Gowtham Potureddi

Posted on Jul 17

Data Engineering for E-Commerce: Clickstream, Recommendations, Inventory Joins

#python #sql #interview #dataengineering

ecommerce data engineering is the single most concentrated stress test a data platform faces in 2026 — high-cardinality catalogs, 20× Black Friday spikes, personalisation SLAs below 100 milliseconds, and an inventory number that a customer can turn into a refund the moment it is wrong. Nowhere else do a clickstream pipeline, a recommendation data pipeline, an inventory join, and an attribution model all have to be right at the same time — and be right for the same order.

This guide is the senior-DE walkthrough of the four pipelines that carry the shopping-cart economy: how a clickstream is collected, enriched, and sessionised on a 30-minute inactivity gap; how a recommendation feature store splits offline batch from online serving and holds point-in-time correctness across both; how an inventory join from OMS and WMS lands in the warehouse via MERGE with a safety-stock buffer and a real-time Flink projection into the product-detail-page cache; and how ecommerce analytics closes the loop with multi-touch attribution model recipes, GA4-to-BigQuery exports, funnel SQL, and cohort retention. Each section pairs a teaching block with a Solution-Tail interview answer — code, a step-by-step trace, an output table, then a concept-by-concept breakdown of why it works.

When you want hands-on reps immediately after reading, drill the SQL practice library →, rehearse on window-function problems →, and sharpen the join axis with the joins practice set →.

On this page

Why e-commerce DE differs in 2026
Clickstream event pipelines
Recommendation feature stores
Inventory joins + real-time stock
Attribution + funnel analytics
Cheat sheet — e-commerce DE recipes
Frequently asked questions
Practice on PipeCode

1. Why e-commerce DE differs in 2026

E-commerce data lives at the intersection of high cardinality, hard SLAs, and unforgiving correctness — the four-pipeline stack is what senior DEs build

The one-sentence invariant: an e-commerce data platform has to be simultaneously high-volume (millions of events per second at peak), high-cardinality (SKUs × users × sessions blow up state), latency-bound (recs and stock served in under 100 ms), and correctness-bound (a wrong inventory number turns into a refund and a bad review). Every other engineering trade-off — collector choice, feature store shape, MERGE cadence, attribution model — is a consequence of that four-way squeeze. Once you internalise it, the entire ecommerce data engineering interview surface collapses to "which of the four constraints are you defending in this pipeline?"

Four axes interviewers actually probe.

Cardinality. A mid-size retailer has 500K SKUs, 5M users, and 50M sessions per month. Any keyed state (per-user recs, per-SKU stock, per-session funnel) has to fit under that fan-out. Naive GROUP BY on session_id materialises 50M rows; you plan for it or the query dies.
Volume. Baseline traffic is a fraction of Black Friday and Cyber Week — peaks are 20× normal, sustained for 4 hours. Every pipeline (collector, enrichment, feature refresh, stock projection) has to survive a 20× burst without falling off the SLA.
Latency. The recommendation carousel on the product detail page (PDP) must return a personalised list in under 100 ms p99 or the placement gets cut. Real-time stock ("3 left in stock") has to be within 5 seconds of ground truth, or you oversell and refund.
Correctness. Attribution feeds bonus payouts. Recs feed revenue. Inventory feeds fulfilment. A silent bug in any one of these hits the P&L within a week — and gets found by finance, not by engineering.

The four pipelines every e-commerce data team runs.

Clickstream. Every page-view, add-to-cart, remove-from-cart, checkout, and purchase event, collected from web + mobile, enriched with a user id, filtered for bots, sessionised on a 30-minute inactivity gap.
Recommendation feature store. Offline batch that computes user embeddings + item embeddings + affinity scores + lookback aggregates; online store that serves the same features under 10 ms; a point-in-time join contract that keeps the two consistent.
Inventory joins. CDC feeds from the order management system (OMS) and the warehouse management system (WMS) landed into the warehouse via MERGE; safety-stock buffers to prevent oversell; a real-time projection stream into the PDP cache.
Attribution + funnel. Multi-touch attribution across channels, GA4 export to BigQuery, funnel SQL with self-joins or LAG, cohort retention grids, LTV modelling with survival analysis.

The 2026 reality — what changed since 2022.

First-party data is now the default. Cookie deprecation + iOS App Tracking Transparency shifted every retailer to server-side collection (Snowplow, RudderStack self-hosted, GA4 server-side) and deterministic ID resolution over probabilistic fingerprinting.
Feature stores went managed. Feast v0.40+ and Tecton became the default; teams stopped hand-rolling online-vs-offline sync jobs.
Iceberg + BigQuery ate the warehouse layer. GA4 events land natively in BigQuery, and the rest of the platform is Iceberg on S3 with Snowflake / Trino / DuckDB reading it — no more one-warehouse-per-team.
Real-time inventory went from optional to mandatory. Buy-online-pick-up-in-store (BOPIS) and same-day delivery pushed the acceptable staleness from 15 minutes to 5 seconds. Flink projections into Redis / DynamoDB are now table stakes.
AI-driven personalisation is the norm. Two-stage retrieval + ranking with embedding-based candidate generation is the reference architecture; the data-engineering job is to feed the ranker with fresh features under a 100 ms budget.

What interviewers listen for.

Do you say "four pipelines: clickstream, recs, inventory, attribution" in the first sentence? — senior signal.
Do you push back on "just put GA4 in BigQuery" with a real sessionisation and consent-handling story? — senior signal.
Do you mention "safety-stock buffer + reservation vs commit" unprompted when asked about stock accuracy? — senior signal.
Do you frame recommendations as a "two-stage retrieve-then-rank" pipeline instead of "a model"? — senior signal.

Worked example — sizing the four pipelines for a mid-size retailer

Detailed explanation. A cross-team sizing exercise is a common interview opener: "Here are the numbers for a mid-size retailer — walk me through how big each of the four pipelines is, where the bottlenecks live, and what SLA you are defending." The exercise forces you to translate business scale into engineering budgets.

Question. Given the profile below, size the peak event rate, the feature-store fan-out, the inventory update rate, and the attribution join volume. Call out which pipeline is the tightest SLA.

Input.

Metric	Value
Monthly active users	5,000,000
SKUs (catalog)	500,000
Baseline pageviews per second	4,000
Peak multiplier (BFCM)	20×
Rec carousel calls per pageview	3
Stock updates per minute (OMS + WMS)	12,000
Marketing channels	8

Code.

baseline_events_per_sec = 4_000 * 3       # 3 events per pageview (view, hover, dismiss)
peak_events_per_sec     = baseline_events_per_sec * 20   # BFCM burst

# feature store fan-out
users        = 5_000_000
skus         = 500_000
user_item    = users * 20    # 20 recent items per user (rolling)
user_features_per_user = 60  # embeddings + lookback aggregates
online_reads_per_sec = 4_000 * 3 * 60   # 3 rec calls × 60 features

# inventory
stock_updates_per_sec = 12_000 / 60   # 200 upserts/sec baseline

# attribution
purchases_per_sec = 4_000 * 0.03      # 3% conversion
touchpoints_per_purchase = 5          # avg across channels
attribution_joins_per_sec = purchases_per_sec * touchpoints_per_purchase

Step-by-step explanation.

Clickstream volume. 4K pageviews/sec × 3 tracked events per pageview = 12K events/sec baseline. Peak = 240K events/sec for the 4-hour BFCM window. That is the collector-tier sizing budget.
Feature-store fan-out. With 3 rec calls per pageview and 60 features per call, the online store handles ~720K reads/sec at baseline. Peak is 14M reads/sec — that is the Redis / DynamoDB sizing number, not the compute cost.
Inventory upserts. 12K updates/minute = 200/sec average; peak bursts to ~4K/sec during flash sales. Modest for a warehouse MERGE but you still need micro-batching (5-second windows) or you spend more on file rewrites than on real work.
Attribution joins. 120 purchases/sec × 5 touchpoints = 600 joins/sec. Small volume but the SQL is the most complex — self-joins across 90 days of session history is the standard cost driver.
Tightest SLA. The 100 ms rec carousel latency is the tightest bound. Everything else has second-scale or minute-scale budgets. Design order is: rec online store first, everything else second.

Output.

Pipeline	Baseline rate	Peak rate	Tightest SLA
Clickstream ingest	12K events/sec	240K events/sec	5 s end-to-end
Rec online store reads	720K reads/sec	14M reads/sec	100 ms p99
Inventory upserts	200 rows/sec	4K rows/sec	5 s freshness
Attribution joins	600 joins/sec	12K joins/sec	daily batch OK

Rule of thumb. Design the tightest-SLA pipeline first (recs at 100 ms), then work outward. The other three pipelines have budgets measured in seconds or minutes; the rec online store is the only sub-second constraint and it dominates infrastructure choice.

Worked example — the four-way trade-off in one query

Detailed explanation. A single SQL question — "how many unique users bought a product they had viewed in the last 30 minutes, per hour, for the last 7 days?" — touches all four pipelines. It stresses cardinality (30M sessions), event ordering (view before buy), freshness (last 7 days), and joins (view stream to purchase stream).

Question. Write the query on a clickstream events table. Comment on which of the four e-commerce DE constraints each choice defends.

Input.

event_id	user_id	event_type	product_id	event_time
1	u1	view	p1	2026-07-14 10:05:00
2	u1	buy	p1	2026-07-14 10:20:00
3	u2	view	p2	2026-07-14 10:10:00
4	u2	buy	p3	2026-07-14 10:35:00
5	u3	buy	p4	2026-07-14 10:50:00

Code.

WITH views AS (
  SELECT user_id, product_id, event_time AS viewed_at
  FROM   clickstream
  WHERE  event_type = 'view'
    AND  event_time >= CURRENT_TIMESTAMP - INTERVAL '7 days'
),
buys AS (
  SELECT user_id, product_id, event_time AS bought_at
  FROM   clickstream
  WHERE  event_type = 'buy'
    AND  event_time >= CURRENT_TIMESTAMP - INTERVAL '7 days'
)
SELECT  DATE_TRUNC('hour', b.bought_at)              AS hour,
        COUNT(DISTINCT b.user_id)                    AS users_buying_after_view
FROM    buys b
JOIN    views v
  ON    v.user_id     = b.user_id
  AND   v.product_id  = b.product_id
  AND   v.viewed_at   BETWEEN b.bought_at - INTERVAL '30 minutes'
                          AND b.bought_at
GROUP BY 1
ORDER BY 1;

Step-by-step explanation.

The two CTEs restrict to the last 7 days first — pushdown at the source table cuts the join input by orders of magnitude. This defends volume (do not scan a year of clickstream to answer a 7-day question).
The join key is (user_id, product_id) — a compound key with high cardinality. Warehouse partitioning on event_time + clustering on user_id makes this join land as sort-merge, not broadcast. This defends cardinality.
The BETWEEN window constrains views to 30 minutes before the buy — a range join. Warehouses like Snowflake and BigQuery execute range joins efficiently only when both sides are already clustered; if not, this becomes an O(N²) hazard. This defends latency for the batch.
COUNT(DISTINCT b.user_id) per hour is a HLL_COUNT_DISTINCT candidate in BigQuery / APPROX_COUNT_DISTINCT in Snowflake for very large data; exact count is fine for 7 days at mid-scale. This defends correctness — pick approximate only when the volume justifies it.
Result is hourly rows for the last 7 days = 168 rows. Small output, big input — the classic e-commerce analytics shape.

Output.

hour	users_buying_after_view
2026-07-14 10:00:00	2
2026-07-14 11:00:00	5
2026-07-14 12:00:00	7

Rule of thumb. Every e-commerce analytics query has a partition-prune step, a cardinality-safe join key, a range or window constraint, and a distinct-count aggregation. Get those four right first; performance follows.

Worked example — SKU × session state blow-up

Detailed explanation. A common trap: an analyst asks for "the number of distinct users who added item X to cart in session Y over the last 90 days." Naively grouping by session_id × product_id creates a state explosion — 50M sessions per month × 3 months × 500K SKUs is beyond any single-node system.

Question. Show why a naive GROUP BY session_id, product_id fails and how to reshape the query to defend cardinality.

Input.

Metric	Value
Sessions per month	50,000,000
SKUs	500,000
Naive group cardinality	50M × 500K × 3 = 75 trillion

Code.

-- NAIVE — state explosion
SELECT session_id, product_id, COUNT(*) AS adds
FROM   clickstream
WHERE  event_type = 'add_to_cart'
  AND  event_time >= CURRENT_TIMESTAMP - INTERVAL '90 days'
GROUP BY session_id, product_id;   -- can materialise trillions of rows

-- SAFE — filter at source, aggregate at the RIGHT grain
SELECT product_id,
       COUNT(DISTINCT session_id) AS sessions_adding,
       COUNT(*)                   AS total_adds
FROM   clickstream
WHERE  event_type = 'add_to_cart'
  AND  event_time >= CURRENT_TIMESTAMP - INTERVAL '90 days'
GROUP BY product_id
ORDER BY sessions_adding DESC
LIMIT 100;                          -- 100 rows out

Step-by-step explanation.

The naive query groups at the finest possible grain (session × product), producing an output row for every unique combination. In practice, the shuffle key is (session_id, product_id); the state store balloons to trillions of keys and the query OOMs or falls back to spilling.
The reshape aggregates at the coarsest useful grain — per product across all sessions. The output is 500K rows at most, one per SKU. COUNT(DISTINCT session_id) is the answer the analyst actually wanted.
The LIMIT 100 after ORDER BY sessions_adding DESC is a pushdown-friendly ranking; Snowflake / BigQuery apply the top-K optimisation and skip full sort.
The trap generalises: whenever you GROUP BY <high-cardinality>, <high-cardinality>, the state cardinality is the product of the two. E-commerce data has multiple high-cardinality axes (user, session, SKU, product-variant); grouping two of them together is almost never what you want.
The safe rewrite is to pick the coarsest grain that answers the question, then use COUNT(DISTINCT) (or HLL) to preserve the "unique count" semantics without materialising the fine-grained combinations.

Output.

product_id	sessions_adding	total_adds
p9231	41,208	47,102
p1129	39,441	43,900
p8877	32,101	34,502

Rule of thumb. In e-commerce, if your GROUP BY includes both session_id and product_id, stop and ask what grain you really need. The right answer is almost always to drop one of the two and use COUNT(DISTINCT) on it.

Senior interview question on the four-pipeline stack

A senior interviewer often opens with: "You are hired as the first data engineer at a mid-size online retailer. Walk me through the four pipelines you would build, in order, and what SLA and technology choice you would defend for each."

Solution Using a four-pipeline build order + SLA table

Build order for a mid-size retailer's data platform
===================================================

Month 1 — Inventory joins (correctness > everything)
    OMS + WMS CDC → S3 staging → MERGE into a warehouse
    stock table. Safety-stock buffer + reservation column.
    SLA: 5-second stock freshness on PDP.

Month 2 — Clickstream pipeline (data quality foundation)
    Snowplow / RudderStack collector → Kafka → schema registry
    → sessioniser → S3 (raw) + warehouse (curated).
    SLA: 5-minute end-to-end latency, 30-minute session gap.

Month 3 — Attribution + funnel analytics (revenue clarity)
    GA4 + ad-platform APIs → BigQuery / Snowflake → dbt models
    for last-touch, linear, and time-decay attribution.
    SLA: daily batch, next-day dashboards.

Month 4 — Recommendation feature store (personalisation lift)
    Feast + offline Spark + online Redis. Point-in-time joins
    for training; two-stage retrieve-then-rank for serving.
    SLA: 100 ms p99 online, 24 h offline refresh.

Step-by-step trace.

Month	Pipeline	Why this order	Tech pick	SLA
1	Inventory	Wrong stock = immediate refunds	CDC + MERGE	5 s freshness
2	Clickstream	Foundation for everything downstream	Snowplow + Kafka	5 min E2E
3	Attribution	Answers "which channel worked?"	dbt + GA4 export	daily batch
4	Recommendations	Requires clickstream to exist first	Feast + Redis	100 ms p99

Inventory ships first because a wrong stock number is a customer-facing bug within the same day. Clickstream is the foundation of the last two pipelines, so it ships before either. Attribution ships before recommendations because attribution needs only 3 months of history while recommendation quality plateaus at 6+ months of data.

Output:

Pipeline	Tightest constraint	Cost driver
Inventory	correctness	MERGE compute + Redis projection
Clickstream	volume	Kafka + object storage
Attribution	join complexity	warehouse compute
Recommendations	latency	Redis / DynamoDB reads

Why this works — concept by concept:

Correctness before completeness — an inventory bug is a public-facing bug within hours. Ship the pipeline whose bugs are visible to customers first, even if it is not the most exciting technically.
Clickstream is foundation — attribution and recommendation both consume clickstream. Building either before clickstream forces a rewrite in month 3 anyway.
Attribution needs less history than recs — 90 days of clean attribution data is enough to inform marketing spend; recs need 6+ months for embeddings to converge. Ship the shorter payback loop first.
Tightest SLA drives infra choice — the 100 ms rec SLA dictates Redis / DynamoDB. The 5 s inventory SLA dictates a streaming projection. The daily attribution SLA lets you stay in the warehouse. Match infra to SLA, not to fashion.
Cost — an e-commerce data platform is O(pageviews) in ingest cost, O(users × features) in online-store cost, and O(SKUs × updates) in inventory cost. Watch each cost line separately; they scale differently.

SQL
Topic — SQL
E-commerce SQL practice problems

Practice →

SQL Topic — aggregation Aggregation problems for e-commerce metrics

Practice →

2. Clickstream event pipelines

A `clickstream pipeline` is a collector, a schema registry, a bot filter, a consent gate, and a sessioniser — in that order, without shortcuts

The mental model in one line: every clickstream event is caught by a first-party collector, typed against a schema registry, deduped against a bot list, gated by consent state, then grouped into sessions on a 30-minute inactivity gap — the raw stream is only useful once all five have run. Once you say "collect · type · filter · gate · sessionise," the entire sessionization interview surface collapses into "which of the five stages is your pipeline missing?"

The five stages of a clickstream pipeline.

Collector. A first-party SDK (Snowplow tracker, RudderStack JS, Segment analytics.js) fires structured events over HTTPS to a self-hosted or vendor collector endpoint. Server-side collection (from Node/edge functions) is now the default over client-only tracking.
Schema registry. Every event has a schema (self-describing JSON, Avro, or Protobuf). The collector rejects (or quarantines) events that fail schema validation. Iglu (Snowplow) and Confluent Schema Registry are the standard picks.
Bot filter. UA-based and behaviour-based bot detection removes automated traffic before it hits the warehouse. Simple UA blocklist gets 60%; adding a rate check per IP + a session-length check gets 90%.
Consent gate. GDPR / CCPA / iOS ATT require that events from non-consenting users are either dropped, minimised, or held server-side without downstream propagation. The consent state is itself an event stream.
Sessioniser. A 30-minute inactivity gap groups events into sessions; a fresh session starts after 30 minutes of no activity from the same user. The output is (user_id, session_id, session_start, session_end) per session, joined back onto every event.

Vendor vs self-hosted — the 2026 decision.

Vendor-hosted (Segment, RudderStack Cloud, Snowplow Insights). Fast to set up, cost scales linearly with events. Sweet spot: <10M events/month.
Self-hosted (Snowplow OS, RudderStack OSS on your K8s). Higher engineering cost upfront, then flat infra cost. Sweet spot: >100M events/month.
Server-side only (edge functions + custom API). Full control over consent, PII, and enrichment; requires an in-house data team. Sweet spot: >1B events/month or heavy PII/consent requirements.

Unified user ID — deterministic before probabilistic.

Deterministic stitching. When a user logs in, join the anonymous device_id to the logged-in user_id via a user_id_map table. This is the ground truth for cross-device tracking.
Probabilistic stitching. For anonymous traffic, use IP + user-agent + first-seen fingerprint to cluster likely-same-user visits. Accuracy is 60-80%; use only when deterministic data is unavailable.
Identity graph. The union of deterministic + probabilistic mappings, kept as a slowly-changing dimension so historic events can be re-attributed as more identity signals arrive.

Sessionisation — the 30-minute gap.

Standard definition. A session ends after 30 minutes of user inactivity. Any event after that gap starts a new session with a fresh session_id.
Overrides. Sessions also end at midnight in the user's timezone (industry convention for daily reporting) and on a campaign_id change (attribution convention — new campaign starts a new session).
SQL primitive. LAG(event_time) OVER (PARTITION BY user_id ORDER BY event_time) + a case-when-gap-exceeds-30-min → cumulative-sum session number. That is the whole recipe.

PII handling and consent.

Pseudonymisation at collection. Hash email/phone at the collector; only the hash lands in the warehouse. The mapping is kept in a separate, tightly-scoped store.
Consent-aware routing. Events tagged with consent state; downstream jobs filter by consent state. A user who withdraws consent triggers a downstream deletion job (right to be forgotten).
Retention policy. Raw clickstream at 90-365 days; sessionised + aggregated data indefinitely. Explicit retention TTLs are a legal requirement in the EU.

Common interview probes on clickstream.

"What is a session and how do you compute it?" — 30-minute inactivity gap, LAG + cumulative sum.
"How do you handle late-arriving events?" — replay-safe pipelines with watermarks, or a nightly re-sessionisation batch.
"Deterministic vs probabilistic ID stitching?" — deterministic first (login), probabilistic as fallback.
"How do you defend GDPR consent?" — consent stream + downstream filter + deletion jobs.

Worked example — sessionisation SQL with a 30-minute gap

Detailed explanation. The canonical e-commerce SQL question. Given a table of events per user, assign a session_id to each event such that consecutive events within 30 minutes belong to the same session.

Question. Write the sessionisation SQL. Show the LAG + cumulative-sum recipe.

Input.

event_id	user_id	event_time
1	u1	2026-07-14 10:00:00
2	u1	2026-07-14 10:15:00
3	u1	2026-07-14 11:00:00
4	u1	2026-07-14 11:20:00
5	u2	2026-07-14 09:45:00

Code.

WITH gaps AS (
  SELECT event_id,
         user_id,
         event_time,
         CASE
           WHEN LAG(event_time) OVER (PARTITION BY user_id
                                      ORDER BY event_time)
                IS NULL
             OR EXTRACT(EPOCH FROM event_time
                                 - LAG(event_time) OVER
                                     (PARTITION BY user_id ORDER BY event_time))
                > 30 * 60
           THEN 1
           ELSE 0
         END AS is_new_session
  FROM   clickstream
),
sessions AS (
  SELECT event_id,
         user_id,
         event_time,
         SUM(is_new_session) OVER (PARTITION BY user_id
                                   ORDER BY event_time
                                   ROWS BETWEEN UNBOUNDED PRECEDING
                                            AND CURRENT ROW) AS session_num
  FROM gaps
)
SELECT event_id,
       user_id,
       event_time,
       CONCAT(user_id, '-', CAST(session_num AS VARCHAR)) AS session_id
FROM   sessions
ORDER BY user_id, event_time;

Step-by-step explanation.

LAG(event_time) gives the previous event's timestamp within the same user partition. For the first event per user, LAG returns NULL — a new session by definition.
The CASE returns 1 when the gap exceeds 30 minutes (or when there is no previous event), else 0. This flags every event that starts a new session.
The cumulative SUM of the flag becomes the session number within the user — every "1" bumps the number, every "0" carries the same number forward.
The final session_id concatenates user_id with the session number for a globally unique id — u1-1, u1-2, etc.
The pattern generalises to any gap-based grouping (30 minutes, 4 hours, 1 day) by changing the threshold in the CASE. It is the single most common sessionization interview question and worth having in muscle memory.

Output.

event_id	user_id	event_time	session_id
1	u1	2026-07-14 10:00:00	u1-1
2	u1	2026-07-14 10:15:00	u1-1
3	u1	2026-07-14 11:00:00	u1-2
4	u1	2026-07-14 11:20:00	u1-2
5	u2	2026-07-14 09:45:00	u2-1

Rule of thumb. For any per-user gap-based grouping in SQL, reach for LAG + CASE + cumulative SUM. It runs in a single window scan — O(N log N) once for the sort, O(N) for the window — and generalises to any gap threshold.

Worked example — deterministic ID stitching across devices

Detailed explanation. A user views a product on their phone (anonymous device_id d1), logs in on their laptop (device_id d2, user_id u42), and buys. To attribute the purchase to the mobile view, you need to stitch d1 and d2 to u42. The pattern is a self-join through the user_id_map.

Question. Given a table of (device_id, user_id, first_seen) mappings, rewrite a clickstream events table so every anonymous event carries the eventual logged-in user_id.

Input — user_id_map.

device_id	user_id	first_seen
d1	u42	2026-07-14 10:30:00
d2	u42	2026-07-14 10:00:00

Input — clickstream.

event_id	device_id	event_time
1	d1	2026-07-14 09:50:00
2	d2	2026-07-14 10:05:00
3	d1	2026-07-14 10:35:00

Code.

SELECT c.event_id,
       c.device_id,
       COALESCE(m.user_id, CONCAT('anon-', c.device_id)) AS resolved_user_id,
       c.event_time
FROM   clickstream c
LEFT   JOIN user_id_map m
       ON   m.device_id = c.device_id
ORDER  BY c.event_time;

Step-by-step explanation.

The LEFT JOIN on device_id attaches the eventual user_id for any device that ever logs in. Devices that never log in stay NULL.
COALESCE(m.user_id, CONCAT('anon-', c.device_id)) gives a stable identifier — the real user_id if known, else an anon-<device> bucket. This preserves anonymous funnels.
Event 1 (d1 at 09:50, before login at 10:30) is retroactively stitched to u42 because d1 eventually mapped to u42 — that is the value of stitching after the fact.
Event 2 (d2) was logged-in at collection time and stays u42.
The trap: if a device_id maps to multiple user_ids over time (family shared device), the join fan-outs. The fix is to RANK() mappings and pick the most-recent-before-event or the earliest-after-event, depending on the attribution policy.

Output.

event_id	device_id	resolved_user_id	event_time
1	d1	u42	2026-07-14 09:50:00
2	d2	u42	2026-07-14 10:05:00
3	d1	u42	2026-07-14 10:35:00

Rule of thumb. Always keep a user_id_map as a slowly-changing dimension so historic events can be re-attributed as new identity signals arrive. Never treat "no user_id at collection time" as "unattributable forever."

Worked example — consent-aware event filtering

Detailed explanation. A user withdraws marketing consent. All events collected after the withdrawal must be excluded from ad-attribution pipelines but retained for essential operational analytics (order confirmations, fraud). The consent state itself is an event stream; downstream jobs join events to the consent-state-at-event-time.

Question. Given a consent_events stream and a clickstream stream, filter clickstream events to only those where the user had marketing consent at the moment the event fired.

Input — consent_events.

user_id	consent_at	marketing_consent
u1	2026-07-14 09:00:00	true
u1	2026-07-14 12:00:00	false
u2	2026-07-14 09:00:00	true

Input — clickstream.

event_id	user_id	event_time
1	u1	2026-07-14 10:00:00
2	u1	2026-07-14 13:00:00
3	u2	2026-07-14 11:00:00

Code.

WITH consent_at_event AS (
  SELECT c.event_id,
         c.user_id,
         c.event_time,
         (SELECT ce.marketing_consent
          FROM   consent_events ce
          WHERE  ce.user_id  = c.user_id
            AND  ce.consent_at <= c.event_time
          ORDER  BY ce.consent_at DESC
          LIMIT  1)                        AS marketing_consent_at_event
  FROM   clickstream c
)
SELECT event_id, user_id, event_time
FROM   consent_at_event
WHERE  marketing_consent_at_event = true;

Step-by-step explanation.

The correlated subquery finds the latest consent state for the user at or before the event time. That is the ground-truth consent state for that event.
Event 1 (u1 at 10:00) — latest consent as of 10:00 is true (from 09:00). Included.
Event 2 (u1 at 13:00) — latest consent as of 13:00 is false (from 12:00). Excluded.
Event 3 (u2 at 11:00) — latest consent as of 11:00 is true (from 09:00). Included.
In production, replace the correlated subquery with a RANGE JOIN or a windowed LAST_VALUE for performance on large data. The correlated form is clear for teaching but scans N times.

Output.

event_id	user_id	event_time
1	u1	2026-07-14 10:00:00
3	u2	2026-07-14 11:00:00

Rule of thumb. Consent is a slowly-changing dimension, not a boolean at ingest time. Attach the consent-state-at-event-time to every event; never trust the current consent state to describe a historic event.

Senior interview question on clickstream sessionisation at scale

A senior interviewer might ask: "You have 5 billion clickstream events per day, 5 million daily active users, and a 30-minute session gap. Design the sessionisation pipeline end-to-end. Where does it run, how does it handle late-arriving events, and what does the output look like?"

Solution Using a streaming sessioniser + nightly re-sessionisation batch

Streaming + batch sessionisation
================================

Streaming layer (Flink)
-----------------------
1. Kafka source: clickstream topic, keyed by user_id.
2. KeyedProcessFunction holds per-user session state:
     - last_event_time
     - current_session_id
     - current_session_start
3. On each event:
     - If (event_time - last_event_time) > 30 min OR no state:
         open a new session (fresh session_id, session_start = event_time).
     - Else:
         extend current session (session_end = event_time).
4. Emit event enriched with (session_id, session_start).
5. Register a timer at last_event_time + 30 min; on fire, close the session
   and emit a session_summary record.

Batch layer (Spark, nightly)
----------------------------
6. Re-sessionise the previous day using SQL LAG + cumsum on the full
   day's events (handles late-arriving events that were >30 min late).
7. Replace the streaming session_ids for that day with the batch's.

Output tables
-------------
- clickstream_enriched (streaming, live)
- clickstream_sessionised (batch, source of truth for downstream)
- session_summary (one row per session)

Step-by-step trace.

Layer	Runs	Job	Output
Streaming	continuous	Flink KeyedProcessFunction	live enriched events
Nightly batch	02:00 daily	Spark SQL re-sessionise	authoritative sessions
Downstream	daily	dbt / warehouse	consumes batch table

The pattern is Kappa-with-a-nightly-correction: streaming handles the live "session is happening now" case for real-time dashboards; the batch handles the "was this event actually part of a session yesterday" case for correctness. Downstream tables consume the batch output.

Output:

Component	Latency	Correctness
Streaming sessioniser	seconds	95% (some late events misassigned)
Batch re-sessionisation	24 h	100% (all late events integrated)
Downstream consumers	daily	always the batch table

Why this works — concept by concept:

Streaming for freshness, batch for correctness — no single system does both well. Streaming is right most of the time for real-time UIs; batch is right always for analytics.
KeyedProcessFunction owns per-user state — one Flink subtask per user hash bucket; state is local to that subtask and snapshotted with checkpoints. Recovery replays from the last checkpoint.
Timer-based session close — fires 30 minutes after the last event and emits the summary row. Without the timer, sessions never close in the streaming layer and state grows unbounded.
Nightly re-sessionisation is idempotent — replaces the previous day's session_ids with authoritative values. Downstream tables always use the batch output; the streaming output is for live UIs only.
Cost — streaming is O(events × per-user state size); batch is O(events × log events) for the daily sort. The streaming layer dominates infra cost; the batch layer dominates warehouse cost.

SQL
Topic — window-functions
Window-function sessionisation problems

Practice →

SQL Topic — SQL Clickstream SQL practice

Practice →

3. Recommendation feature stores

A `recommendation data pipeline` is two feature stores (offline + online) held consistent by a point-in-time join, feeding a two-stage retrieve-then-rank serving path

The mental model in one line: an offline store computes features on batch data for model training; an online store serves the same features at request time under 10 ms; a point-in-time join keeps the two consistent so training and serving see the same feature values — and the serving path is always retrieve (candidate generation) followed by rank (personalised scoring). Once you say "two stores, one contract, two-stage serving," the entire feature-store interview surface collapses to consequences of that architecture.

The two-store split.

Offline store. A columnar store (Parquet on S3, Iceberg, BigQuery) that holds every historical feature value with an event_time. Used for model training, backfill, and analytics.
Online store. A low-latency key-value store (Redis, DynamoDB, Cassandra, ScyllaDB) that holds the latest feature value per key for sub-10ms lookup. Used for model serving.
Feature store framework. Feast, Tecton, Chalk, or Hopsworks abstract the sync between offline and online. Materialisation jobs push feature slices from offline to online on a cadence (typically 15 min to 1 h).

Point-in-time correctness — the training-serving skew fix.

Wrong. Join the training label table (purchase at time T) to the "current" feature value. This leaks future information — the feature at query time is not the feature that the model would have seen at time T.
Right. For each training row (user, item, label_time), join the feature value that was current at label_time. This is a WHERE feature.event_time <= label_time join with a MAX(event_time) per key.
Framework support. Feast's get_historical_features(entity_df) does this automatically. Rolling your own is easy to get subtly wrong — use the framework.

Feature freshness SLAs — how fresh is fresh enough?

Realtime features (< 1 s). Session-level: "items viewed in this session." Requires a streaming path to the online store (Kafka → Flink → Redis).
Near-realtime (5-60 min). Recent behaviour: "items added to cart in the last hour." Micro-batch materialisation.
Batch (24 h). Lookback aggregates: "avg session length over 30 days." Nightly Spark job.
Static (weekly/monthly). Item embeddings, cluster labels. Refresh on model retrain cadence.

Two-stage serving — retrieve then rank.

Candidate generation. Retrieve 1000-10000 candidate items per request from an approximate-nearest-neighbour index (FAISS, ScaNN, Vespa) using user embedding × item embedding cosine similarity. Fast, coarse.
Ranking. Score each candidate with a heavy per-request model (GBDT, LR, or a deep net) using all 60+ features per candidate. Slow, precise. Return top 20.
Why two stages? A ranker cannot process 500K SKUs in 100 ms; candidate generation prunes to a tractable 1000 first. The two-stage split is the industry-standard latency-vs-quality trade-off.

Cold start — new user, new item.

New user. No embedding yet → fall back to popularity + demographic bucketing until 5+ interactions. First-session recs are almost never personalised.
New item. No interaction data yet → fall back to content-based similarity (title, category, image embedding) until 100+ views. First-week item recs use only content features.
Recovery cadence. Update embeddings within a few interactions for new items (streaming); daily for new users (batch).

Lookback windows — 7d / 30d / 365d.

7-day window. "Recent behaviour" — captures current campaigns and shifts in taste.
30-day window. "Consistent preference" — smooths out one-off browses.
365-day window. "Long-term affinity" — for seasonal categories and repeat-purchase items.
Composition. Most rankers use all three as separate features; the model learns which window is predictive for each user/item pair.

Feast + Tecton architecture — the reference stack.

Feast (open source). Declarative feature definitions in Python; materialisation from any data warehouse to any online store; SDK for offline get_historical_features and online get_online_features.
Tecton (managed). Adds monitoring, streaming features, backfill orchestration, and observability on top of the Feast model.
Both. Enforce the point-in-time contract; abstract the offline-online sync; version features so retraining is reproducible.

Common interview probes on recs.

"What is point-in-time correctness?" — feature values as of label time, not query time.
"Why two-stage?" — candidate generation prunes to 1K; ranking scores the 1K under 100 ms.
"How do you cold-start?" — popularity + content features until enough interactions.
"What is the offline-online skew?" — the drift between training features and serving features when materialisation lags.

Worked example — point-in-time feature join for training data

Detailed explanation. Building a training set for a purchase-prediction model. For each (user, item, purchase_time) label, join the user's average session length as of the purchase time, not as of today. Naive joins leak future information.

Question. Write the point-in-time join SQL. Show why a naive join leaks.

Input — labels.

user_id	item_id	purchase_time
u1	p1	2026-07-01 10:00:00
u1	p2	2026-07-15 14:00:00

Input — user_features (historical, one row per (user, day)).

user_id	feature_date	avg_session_len_min
u1	2026-06-30	8
u1	2026-07-10	12
u1	2026-07-14	15

Code.

-- WRONG — joins the current-latest feature, leaking future info
SELECT l.user_id,
       l.item_id,
       l.purchase_time,
       (SELECT avg_session_len_min
        FROM   user_features f
        WHERE  f.user_id = l.user_id
        ORDER  BY f.feature_date DESC
        LIMIT 1) AS avg_session_len_min
FROM labels l;

-- RIGHT — point-in-time: feature as of the label time
SELECT l.user_id,
       l.item_id,
       l.purchase_time,
       (SELECT f.avg_session_len_min
        FROM   user_features f
        WHERE  f.user_id      = l.user_id
          AND  f.feature_date <= l.purchase_time
        ORDER  BY f.feature_date DESC
        LIMIT 1) AS avg_session_len_min
FROM labels l;

Step-by-step explanation.

The wrong version picks the newest feature row per user, regardless of the label time. Row (u1, p1, July 1) gets the July 14 feature value — a value that includes the outcome the model is trying to predict.
The right version adds feature_date <= purchase_time. Row (u1, p1, July 1) now gets the June 30 feature value — the value that would have been visible to the model at prediction time.
Row (u1, p2, July 15) gets the July 14 feature value in both versions — because July 14 is before July 15, no leakage in this case.
In Feast, get_historical_features(entity_df) runs this join automatically for every feature in the view, using the framework's event_timestamp metadata.
The leak has a distinctive symptom: training AUC is 0.95 but serving performance is 0.60. If offline and online numbers diverge by that much, the first place to look is a point-in-time bug.

Output (right version).

user_id	item_id	purchase_time	avg_session_len_min
u1	p1	2026-07-01 10:00:00	8
u1	p2	2026-07-15 14:00:00	15

Rule of thumb. Any training feature that changes over time needs a point-in-time join. If you can't clearly explain "as of what time" for every feature in the training set, you have a leak waiting to be found.

Worked example — two-stage retrieval + ranking flow

Detailed explanation. A user requests the recommendation carousel on the home page. Under a 100 ms SLA, generate a personalised top-20 from a catalog of 500K SKUs. The only way to hit the budget is two-stage: retrieve 1000 candidates from an ANN index, rank them with a heavy model.

Question. Sketch the two-stage flow in Python pseudocode. Call out where the latency budget is spent.

Input.

Stage	Input size	Output size	Latency budget
Retrieval	500K SKUs	1000 candidates	20 ms
Feature fetch	1000 items × 60 features	60K feature values	15 ms
Ranking	1000 candidates	top 20	50 ms
Post-processing	20 items	20 items with metadata	10 ms
Total			95 ms p99

Code.

def recommend(user_id: str) -> list[dict]:
    # Stage 1 — candidate generation via ANN
    user_emb  = online_store.get(f"user_emb:{user_id}")            # 2 ms
    candidates = ann_index.search(user_emb, top_k=1000)             # 18 ms

    # Stage 2 — batched feature fetch
    item_features = online_store.mget([f"item_feat:{c}" for c in candidates])
    user_features = online_store.get(f"user_feat:{user_id}")        # 15 ms total

    # Stage 3 — ranking (GBDT scoring)
    rows = [make_row(user_features, item_features[i], candidates[i])
            for i in range(len(candidates))]
    scores = ranker.predict(rows)                                   # 50 ms

    # Stage 4 — top-K + post-processing
    top20 = sorted(zip(candidates, scores), key=lambda x: -x[1])[:20]
    return [enrich_with_metadata(c) for c, _ in top20]              # 10 ms

Step-by-step explanation.

Stage 1 fetches the user's embedding from the online store (2 ms) and queries an ANN index like FAISS or ScaNN (18 ms). ANN trades a small recall drop for O(log N) query time; brute-force cosine over 500K SKUs would take 5+ seconds.
Stage 2 batch-fetches 60 features for each of the 1000 candidates using MGET. A single round-trip fetches everything; individual GET calls would blow the budget.
Stage 3 runs the ranker over 1000 rows. A GBDT with 500 trees scores 1000 rows in ~50 ms on a modern CPU; a small neural net in the same budget on a GPU.
Stage 4 takes the top 20 by score, then enriches with metadata (title, price, image URL) from a lookup cache. The metadata lookup is fast because it is exactly 20 items.
The whole flow lives inside a /recommend endpoint. Every millisecond spent outside these four stages is overhead — logging, tracing, network. Budget accordingly.

Output (top-3 example).

rank	item_id	score
1	p9231	0.912
2	p1129	0.887
3	p8877	0.861

Rule of thumb. Under a 100 ms budget, always go two-stage. Retrieval cuts the input space by 500×; ranking spends the compute where it matters. Trying to rank all 500K SKUs is the number-one design bug in first-time rec systems.

Worked example — cold-start fallback for a new user

Detailed explanation. A user just signed up. There is no embedding, no history, no interactions. Cold-start means the ranker has nothing to work with. The fallback is popularity within their demographic bucket + explicit signals from onboarding.

Question. Show the cold-start fallback logic and the transition to personalised recs after the user has enough interactions.

Input.

user_id	interactions	age_bucket	country
u_new	0	25-34	US

Code.

def recommend_with_fallback(user_id: str) -> list[dict]:
    interactions = online_store.get(f"user_interactions:{user_id}", 0)

    if interactions < 5:
        # cold-start — popularity in demographic bucket
        profile = user_profile_store.get(user_id)
        bucket_key = f"popular:{profile.age_bucket}:{profile.country}"
        return popularity_lists.get(bucket_key)[:20]

    if interactions < 50:
        # warming — blend popularity + weak personalisation
        pop = popularity_lists.get(bucket_key)[:10]
        per = personalised(user_id)[:10]
        return interleave(pop, per)

    # fully warmed
    return personalised(user_id)[:20]

Step-by-step explanation.

Below 5 interactions, personalised recs would be pure noise. Fall back to demographic-bucketed popularity — the ranking is not personalised, but it is a defensible baseline.
Between 5 and 50 interactions, the user's embedding is stabilising. Blend popularity (10 items) with weak personalisation (10 items) via interleave — this smoothes the transition and gives the model more signal.
Above 50 interactions, personalised recs are stable and dominate. Popularity fallback is retired.
The thresholds (5, 50) are chosen empirically per business. E-commerce with 5% conversion needs more interactions per user than a content site with 60% engagement.
The trap is a permanent cold-start bug: if the interaction counter is stored in an ephemeral cache and evicted, users bounce back to cold-start forever. Store interactions durably in the online store with a long TTL.

Output.

stage	u_new receives
0 interactions	popularity for 25-34 US
20 interactions	blended popularity + personalised
100 interactions	fully personalised

Rule of thumb. Every rec system needs a cold-start policy — you will always have new users. Design the fallback and the warming transition explicitly, and store interaction counts durably in the online store.

Senior interview question on feature store architecture

A senior interviewer might ask: "You have to build a feature store from scratch for a recommendation model. Walk me through the offline store, the online store, how they stay in sync, and what you monitor to detect training-serving skew."

Solution Using Feast + point-in-time contract + skew monitoring

Feature store architecture
==========================

Offline store — Iceberg on S3
    - One table per feature view (user_features, item_features, session_features)
    - Columns: entity_key + feature columns + event_timestamp
    - Partitioned by DATE(event_timestamp)
    - Every write is append-only; no updates

Online store — Redis (or DynamoDB)
    - Key: feature_view:entity_id
    - Value: JSON blob of latest feature values
    - TTL: 7 days (safety net; refresh cadence is much faster)

Materialisation job (Feast)
    - Runs every 15 min (or on stream events for realtime features)
    - Reads new rows from offline
    - Upserts latest per entity into online store
    - Emits materialisation lag metric

Point-in-time join for training
    - get_historical_features(entity_df=labels)
    - Feast joins each label to feature.event_timestamp <= label.event_timestamp
    - Output: training dataframe with correct as-of features

Skew monitoring
    - For 0.1% of live requests, log both online-feature-value
      and warehouse-feature-value-at-request-time.
    - Alert if divergence > 5%.

Step-by-step trace.

Layer	Frequency	Latency	Correctness
Offline write	continuous	minutes	strong (immutable)
Materialisation	15 min	15 min	eventually consistent
Online read	request-time	< 10 ms	latest materialised
Point-in-time train	daily	hours	correct by construction
Skew monitor	0.1% sample	request-time	divergence alarm

The Feast contract guarantees that any (entity, event_time) pair maps to a deterministic feature value in both offline and online paths. Materialisation is the only place skew can enter, and the skew monitor catches it directly.

Output:

Component	Owns	Reads from
Iceberg	historical features + event_time	ingest jobs
Redis	latest features per entity	materialisation
Materialisation	offline → online sync	Iceberg
Skew monitor	drift detection	live requests + Iceberg
Trainer	point-in-time joined dataframe	Iceberg

Why this works — concept by concept:

Two stores by construction — offline is columnar for training scans; online is key-value for point lookups. No single store does both well.
Materialisation as a first-class job — the offline-to-online sync is a scheduled Spark job with observability and SLA, not a script. Failed materialisations are alerted.
Point-in-time in the framework — Feast's get_historical_features is the only place point-in-time logic lives. No hand-rolled SQL to get wrong.
Skew monitoring by sampling live requests — you cannot detect skew from offline alone; you have to sample real serving requests and compare to the warehouse-as-of value.
Cost — offline store cost is O(rows × features); online store cost is O(entities × features × replicas). Online cost usually dominates because it is memory-priced; keep the online payload lean and the offline detailed.

SQL
Topic — joins
Point-in-time join practice

Practice →

SQL Topic — window-functions Feature-window SQL problems

Practice →

4. Inventory joins + real-time stock

An `inventory join` is a dual-write CDC from OMS and WMS into a warehouse MERGE, with a safety-stock buffer and a Flink projection into the PDP cache — anything less oversells

The mental model in one line: the authoritative stock number is the difference between committed inventory (from WMS) and reserved inventory (from OMS), minus a safety-stock buffer, projected in real time onto the product-detail-page cache — every one of those pieces has to be right or you oversell. Once you say "commit minus reserved minus buffer, projected live," the entire real-time inventory interview surface collapses to the four sub-problems.

The four stock states every inventory system tracks.

On-hand. Physical inventory at a warehouse. Ground truth from WMS cycle counts.
Committed. Allocated to a specific order and being fulfilled. Cannot be sold again.
Reserved. Held for a cart or a checkout in progress. May be released after a TTL if the customer abandons.
Available. on_hand - committed - reserved - safety_stock_buffer. This is what the PDP shows.

Why dual-write CDC (OMS + WMS).

OMS. Owns orders, reservations, cancellations. Emits events for every reservation / commit / release.
WMS. Owns physical stock, put-away, pick, pack, ship, cycle counts. Emits events for every physical movement.
Warehouse. Joins both streams to compute available_qty per (sku, warehouse). Neither system alone has the full picture.

The MERGE primitive for upsert.

Every source event (from either CDC stream) is a partial update — an OMS reservation only changes the reserved column. A WMS receipt only changes on_hand.
MERGE INTO stock USING source ON stock.sku = source.sku WHEN MATCHED THEN UPDATE SET ... WHEN NOT MATCHED THEN INSERT ... is the safe primitive across Snowflake, BigQuery, Databricks, Iceberg.
Never use INSERT ON CONFLICT for cross-column partial updates — you will overwrite untouched columns with NULL.

Safety-stock buffers — the oversell guard.

Fixed buffer. available_qty = on_hand - committed - reserved - 5 (subtract 5 units always). Simple; wastes inventory on high-volume SKUs.
Dynamic buffer. available_qty = on_hand - committed - reserved - MAX(3, on_hand * 0.05) (larger buffer for higher-stock SKUs). Better inventory utilisation.
Per-warehouse. Buffers differ by warehouse — a 3PL warehouse with lower accuracy needs a larger buffer than a company-owned one.

Reservation vs commit — the state transition.

Reservation. Customer adds to cart or starts checkout → OMS emits reserve(sku, qty, cart_id) → reserved column increments → PDP shows one less unit.
Commit. Customer completes payment → OMS emits commit(sku, qty, order_id) → reserved decrements, committed increments (net zero for available) → WMS starts fulfilment.
Release. Cart abandoned after TTL → OMS emits release(sku, qty, cart_id) → reserved decrements → PDP shows one more unit.
Ship. WMS emits ship(sku, qty, order_id) → on_hand decrements, committed decrements → net zero for available (already excluded).

Oversell detection.

Ex-ante. Every reservation must check available_qty >= requested_qty inside a transactional MERGE. If two customers try to reserve the same last unit, one wins, the other sees "out of stock."
Ex-post. A nightly batch checks that on_hand >= 0 for every (sku, warehouse) — negative on-hand is the oversell fingerprint.
Alerting. Any negative on_hand is a P0 alert to inventory ops — usually indicates a bug in the reservation or commit path.

Cross-warehouse allocation.

A single customer order may pull from multiple warehouses (split shipment). The available_qty per SKU per warehouse dictates which warehouse serves which line.
Allocation policy. Prefer the warehouse with the shortest delivery time to the customer; fall back to any warehouse with stock; last resort is a drop-ship from the vendor.
Real-time impact. The PDP shows total available across all warehouses; the checkout page shows shipping estimates per available split.

Real-time projection with Flink.

Flink keys the joined stream by (sku, warehouse), maintains the running state as (on_hand, committed, reserved), and emits an updated available_qty to Redis or DynamoDB on every event.
Latency. Sub-5-second updates from CDC event to PDP cache is achievable; sub-second is a hard target that requires careful sink batching.
Idempotence. The projection is a fold over the CDC stream — replaying is safe as long as events are ordered per key.

Common interview probes on inventory.

"How do you prevent overselling?" — safety-stock buffer + transactional reservation MERGE.
"What is the difference between reserved and committed?" — reserved = in cart/checkout (releasable); committed = paid (irreversible).
"How do you handle a cart that never completes?" — TTL-based reservation release.
"How do you compute available across warehouses?" — sum per SKU, but display per warehouse for split-shipment logic.

Worked example — MERGE for OMS + WMS upsert

Detailed explanation. A daily batch lands CDC deltas from OMS (reservations) and WMS (physical stock) into a staging table. The stock fact table needs a MERGE that upserts by (sku, warehouse) — preserving untouched columns.

Question. Write the MERGE SQL for a Snowflake / BigQuery / Databricks warehouse. Show why an UPDATE-SET-column pattern is required (not full-row overwrite).

Input — stock (current).

sku	warehouse	on_hand	committed	reserved
p1	w1	100	10	5
p2	w1	50	0	0

Input — stg_stock_delta.

sku	warehouse	delta_type	delta_qty
p1	w1	reserve	3
p2	w1	receive	20
p3	w1	receive	40

Code.

MERGE INTO stock t
USING (
  SELECT sku, warehouse,
         SUM(CASE WHEN delta_type = 'receive' THEN delta_qty ELSE 0 END) AS on_hand_delta,
         SUM(CASE WHEN delta_type = 'commit'  THEN delta_qty ELSE 0 END) AS committed_delta,
         SUM(CASE WHEN delta_type = 'reserve' THEN delta_qty ELSE 0 END) AS reserved_delta
  FROM   stg_stock_delta
  GROUP  BY sku, warehouse
) s
ON t.sku = s.sku AND t.warehouse = s.warehouse
WHEN MATCHED THEN UPDATE SET
     t.on_hand   = t.on_hand   + s.on_hand_delta,
     t.committed = t.committed + s.committed_delta,
     t.reserved  = t.reserved  + s.reserved_delta
WHEN NOT MATCHED THEN INSERT (sku, warehouse, on_hand, committed, reserved)
     VALUES (s.sku, s.warehouse,
             s.on_hand_delta, s.committed_delta, s.reserved_delta);

Step-by-step explanation.

The USING subquery aggregates the delta table so each (sku, warehouse) has one row with the net delta per column. This prevents multiple matches during the merge (a warehouse's cardinality error).
The MATCHED branch increments the current column values by the delta. Untouched columns stay at their current value — the reason we use SET column = column + delta not SET column = source.column.
The NOT MATCHED branch inserts a new stock row for SKUs that were never on hand before. New SKUs get exactly the delta as their starting values.
Row (p1, w1) has reserved incremented by 3 → 5 + 3 = 8. on_hand and committed untouched.
Row (p2, w1) has on_hand incremented by 20 → 70. Row (p3, w1) is inserted with on_hand = 40.
Never write the merge as SET t.reserved = s.reserved because s.reserved (the delta) would overwrite the previous total. The + delta pattern is what makes partial updates safe.

Output — stock (after merge).

sku	warehouse	on_hand	committed	reserved
p1	w1	100	10	8
p2	w1	70	0	0
p3	w1	40	0	0

Rule of thumb. For partial-column updates via CDC, always MERGE ... UPDATE SET column = column + delta. Never UPDATE SET column = source.column — you will silently wipe out the untouched columns.

Worked example — oversell detection SQL

Detailed explanation. A nightly correctness check catches overselling bugs. Any SKU with negative on_hand or with reserved > on_hand - committed violates the invariant and is a P0.

Question. Write the SQL that finds oversold SKUs and quantifies the negative gap.

Input — stock.

sku	warehouse	on_hand	committed	reserved
p1	w1	100	10	5
p2	w1	5	4	3
p3	w1	-2	0	0

Code.

SELECT sku,
       warehouse,
       on_hand,
       committed,
       reserved,
       on_hand - committed - reserved AS free_qty,
       CASE
         WHEN on_hand < 0                     THEN 'negative_on_hand'
         WHEN on_hand - committed - reserved < 0 THEN 'over_reserved'
         ELSE NULL
       END AS oversell_type
FROM   stock
WHERE  on_hand < 0
   OR  on_hand - committed - reserved < 0
ORDER  BY oversell_type, sku;

Step-by-step explanation.

free_qty = on_hand - committed - reserved is the "no buffer" available quantity. Negative means the system promised more than the warehouse physically has.
Row (p1, w1) — free_qty = 100 - 10 - 5 = 85. Not oversold. Filtered out.
Row (p2, w1) — free_qty = 5 - 4 - 3 = -2. Over-reserved by 2. This is a customer-facing bug: two carts will hit "sold out" at checkout, but the reservations were already accepted.
Row (p3, w1) — on_hand = -2. Negative on-hand — physical stock cannot be negative, so this is a WMS bug or a bad receipt. Highest priority.
Run this query nightly; page on any result. In practice, most e-commerce teams find 3-5 oversells per week and each one is a bug worth fixing.

Output.

sku	warehouse	on_hand	committed	reserved	free_qty	oversell_type
p3	w1	-2	0	0	-2	negative_on_hand
p2	w1	5	4	3	-2	over_reserved

Rule of thumb. Oversell detection is a two-invariant check: on_hand >= 0 and on_hand - committed - reserved >= 0. Run both nightly and alert on any violation. The invariants are cheap; refunds and support tickets are not.

Worked example — real-time Flink projection into Redis

Detailed explanation. The PDP shows "3 left in stock" for a hot SKU. That number must lag reality by no more than a few seconds. Flink reads the CDC stream, keys by (sku, warehouse), aggregates the state, and writes the projected available_qty to Redis on every event.

Question. Sketch the Flink Java DataStream code and the Redis key layout.

Input.

Stream	Source	Payload
stock_deltas	Kafka (from OMS + WMS CDC)	`(sku, warehouse, delta_type, delta_qty)`

Code.

DataStream<StockDelta> deltas = env.fromSource(
    KafkaSource.<StockDelta>builder().setTopics("stock_deltas").build(),
    WatermarkStrategy.forBoundedOutOfOrderness(Duration.ofSeconds(5)),
    "stock-src");

deltas.keyBy(d -> d.sku + "|" + d.warehouse)
      .process(new KeyedProcessFunction<String, StockDelta, StockProjection>() {

          ValueState<StockState> state;

          public void open(Configuration cfg) {
              state = getRuntimeContext().getState(
                          new ValueStateDescriptor<>("stock", StockState.class));
          }

          public void processElement(StockDelta d, Context ctx,
                                     Collector<StockProjection> out) throws Exception {
              StockState s = state.value();
              if (s == null) s = new StockState();
              s.apply(d);                       // increment on_hand / committed / reserved
              state.update(s);
              int available = Math.max(0, s.onHand - s.committed - s.reserved - SAFETY_BUFFER);
              out.collect(new StockProjection(d.sku, d.warehouse, available));
          }
      })
      .addSink(new RedisSink<>(config, new StockRedisMapper()));

// Redis key layout: "stock:{sku}:{warehouse}" -> integer available_qty

Step-by-step explanation.

Kafka source reads the merged CDC stream. Watermark of 5 seconds tolerates modest out-of-order events without falling into slow-path handling.
keyBy(sku + warehouse) partitions the state so each subtask owns a slice of SKUs. State access is local; no cross-subtask coordination needed.
KeyedProcessFunction maintains a per-key ValueState<StockState> — the running (on_hand, committed, reserved) tuple. Applied delta is idempotent because CDC events carry monotone offsets.
On every event, compute available = max(0, on_hand - committed - reserved - buffer) and emit. MAX(0, ...) guards against negative displays even if source data is briefly wrong.
The Redis sink writes stock:{sku}:{warehouse} -> available_qty. The PDP reads from this key; no other cache layer between Flink and the customer's browser.
Recovery is by Flink checkpoint — RocksDB state snapshotted every 60s to S3. On restart, replay from the last checkpoint offset; Redis is idempotent so re-writes are safe.

Output (per-request cache lookup).

Redis key	value	staleness
stock:p1:w1	87	< 5 s
stock:p2:w1	0	< 5 s
stock:p3:w1	33	< 5 s

Rule of thumb. For sub-second stock freshness, project via Flink onto a low-latency cache (Redis, DynamoDB) — never let the PDP read the warehouse directly. The warehouse is the ledger; the cache is the display.

Senior interview question on preventing overselling under load

A senior interviewer might ask: "A flash sale drops a 10-unit SKU at noon. Ten thousand customers hit reserve at once. Only 10 can win. Walk me through the end-to-end path — collector, OMS, warehouse, projection — that guarantees no oversell and gives a clean 'sold out' to the 9,990 losers."

Solution Using transactional reservation MERGE + streaming projection + PDP cache

Flash-sale oversell prevention
==============================

1. PDP reads stock:{sku}:{w} from Redis; shows "10 left".
2. Customer clicks Buy; JS calls /reserve with (sku, qty=1).
3. OMS API opens a serialisable transaction:
       BEGIN;
         SELECT on_hand - committed - reserved AS free
         FROM stock
         WHERE sku = ? AND warehouse = ?
         FOR UPDATE;                              -- row lock
         IF free < requested THEN
             ROLLBACK; RETURN 'sold_out';
         END IF;
         UPDATE stock
         SET reserved = reserved + requested
         WHERE sku = ? AND warehouse = ?;
       COMMIT;
   The row lock serialises the 10K concurrent reservations.
4. OMS emits reserve(sku, 1, cart_id) to Kafka.
5. Flink projection consumes the event, updates state, writes
   available = 9 to Redis. PDP now shows "9 left".
6. Repeat until 10 reservations are accepted; the 11th sees
   free = 0 in step 3, gets 'sold_out'.
7. Committed reservations after payment: reserved decrements,
   committed increments. Available stays at 0 (sold out).

Step-by-step trace.

Time	Event	on_hand	committed	reserved	available
t=0	drop	10	0	0	10
t=0.1	reserve × 10	10	0	10	0
t=0.1	reserve × 9990	10	0	10	0 (sold out)
t=60	commit × 10	10	10	0	0
t=3600	ship × 10	0	0	0	0

The row-lock in step 3 is the safety mechanism. Without it, two concurrent reservations both read free = 1 and both succeed → oversell. The FOR UPDATE serialises the check-and-set.

Output:

Actor	Sees	Latency
First 10 customers	reservation accepted	< 100 ms
Next 9,990 customers	'sold out'	< 100 ms
PDP visitors	10 → 9 → 8 → 0 stock	< 5 s from CDC
OMS	10 reservations, 10 commits	serialisable
Warehouse	10 units to pick, 0 oversold	ground truth

Why this works — concept by concept:

Row-level lock in OMS transaction — SELECT ... FOR UPDATE serialises all reservations against the same row. Without it, two concurrent reservations race and both succeed. With it, one always wins and the other rolls back.
CDC to Kafka, not direct write to Redis — the OMS transaction is the source of truth. Redis is a projection; if it drifts, replay the CDC stream. Never let a customer-facing cache be authoritative.
Flink projection with local state — sub-5-second freshness. Recovery replays from the last checkpoint; Redis writes are idempotent so retries are safe.
Safety-stock buffer as an extra guard — even if OMS bug lets an oversell through, the buffer catches it. Defence in depth.
Cost — the row-lock costs ~1 ms of OMS latency per reservation; the Kafka+Flink projection costs ~2 seconds of stale display worst case. Both costs are dwarfed by the cost of a single oversell refund + support ticket + review.

SQL
Topic — joins
Inventory join SQL problems

Practice →

SQL Topic — aggregation Aggregation for inventory analytics

Practice →

5. Attribution + funnel analytics

`ecommerce analytics` closes the loop with a chosen `attribution model`, a session-safe funnel, and a cohort retention grid — never one-touch models on multi-touch journeys

The mental model in one line: attribution assigns credit to marketing touchpoints; funnels measure conversion between stages; cohort retention measures repeat purchase — the three together tell you which channels are earning revenue, where users drop off, and how much of that revenue repeats. Once you say "attribution + funnel + cohort," the entire e-commerce reporting surface collapses to those three artefacts.

The five attribution models.

First-touch. 100% credit to the first channel in the journey. Overvalues top-of-funnel awareness (SEO, brand ads); undervalues closers.
Last-touch. 100% credit to the last channel before purchase. Default in most ad platforms; overvalues closers (direct, retargeting); undervalues awareness.
Linear. Equal credit to every touchpoint. Simple; ignores that some touches matter more than others.
Time-decay. Credit weighted by recency — half-life typically 7 days. Better than linear when journeys are long.
Data-driven. Shapley-value-based credit computed from A/B tests or observational models. Accurate; requires enough data to compute stable values.

Why multi-touch matters.

A user might see a Facebook ad (touch 1), search for the brand on Google (touch 2), click a retargeting email (touch 3), then convert. Last-touch gives 100% to email; first-touch to Facebook. Neither is true.
Multi-touch models split the credit; the marketing team allocates spend based on that split. Wrong attribution → wrong spend → wrong revenue.
The truest answer is a randomised controlled experiment (holdout tests per channel); attribution models are the analytical fallback when experiments are infeasible.

GA4 export to BigQuery — the modern GA baseline.

GA4 → BigQuery. Every property can export raw events to BigQuery via a native connector. One row per event; tables partitioned daily.
Table shape. events_YYYYMMDD with columns event_name, event_timestamp, user_pseudo_id, user_id, event_params (repeated), traffic_source (record), plus device / geo / user properties.
Cost. BigQuery storage is cheap; queries can be expensive without partition pruning. Always filter _TABLE_SUFFIX BETWEEN on the shard suffix.

Session-based vs user-based funnels.

Session-based funnel. Conversion within a single session — "of sessions that saw a product page, how many added to cart in the same session?"
User-based funnel. Conversion across sessions — "of users who saw a product this month, how many bought it this month?"
Which to use. Session-based for immediate-conversion products (SaaS trial signup); user-based for considered-purchase products (electronics, furniture).

Cohort retention — weekly and monthly.

Cohort definition. Users grouped by their first-purchase week or month.
Retention. Percentage of the cohort that purchased again in each subsequent period.
Reading the grid. The diagonal (all cohorts, first period) is 100% by definition. Columns to the right show retention decay. Rows compare cohorts to each other (is the July cohort retaining better than June?).

LTV — from a survival model, not just avg-order × avg-orders.

Naive LTV. avg_order_value × avg_orders_per_user. Wrong for users who churn early — they have low avg_orders but were never coming back anyway.
Survival LTV. Model each user as a survival curve — probability of being active at time T given past behaviour. Integrate revenue under the curve.
Framework. lifetimes (Python) implements BG/NBD + Gamma-Gamma; scikit-survival for more general models.

Marketing-mix modelling (MMM).

Purpose. Attribute revenue to channels at the aggregate level (Facebook total spend vs revenue impact) rather than to individual users. Complements user-level attribution.
Inputs. Weekly time-series of spend by channel, revenue, promotions, seasonality, macro (weather, holidays).
Method. Bayesian regression (Meta's Robyn or LightweightMMM) that decomposes revenue into channel-attributable and baseline components.

Common interview probes on attribution / funnel.

"Difference between last-touch and data-driven attribution?" — last-touch gives all credit to the closer; data-driven splits via Shapley values.
"Write a funnel SQL." — self-join or LAG-based; can be either.
"How do you compute cohort retention?" — group by cohort week; count returning users per subsequent week.
"How do you compute LTV?" — survival curve times revenue, or BG/NBD if you want a framework.

Worked example — last-touch attribution SQL

Detailed explanation. For each purchase, find the last marketing touchpoint before the purchase and assign 100% of the revenue to that channel. This is the baseline attribution report every e-commerce team runs.

Question. Write the last-touch SQL. Show the ROW_NUMBER per purchase.

Input — touchpoints.

user_id	channel	touch_time
u1	facebook	2026-07-10 10:00:00
u1	google_search	2026-07-14 14:00:00
u1	email	2026-07-15 09:00:00

Input — purchases.

user_id	purchase_time	revenue
u1	2026-07-15 10:30:00	120

Code.

WITH ranked AS (
  SELECT p.user_id,
         p.purchase_time,
         p.revenue,
         t.channel,
         t.touch_time,
         ROW_NUMBER() OVER (PARTITION BY p.user_id, p.purchase_time
                            ORDER BY t.touch_time DESC) AS rn
  FROM   purchases p
  JOIN   touchpoints t
         ON t.user_id    = p.user_id
        AND t.touch_time <= p.purchase_time
)
SELECT user_id,
       purchase_time,
       revenue,
       channel                AS last_touch_channel,
       revenue                AS attributed_revenue
FROM   ranked
WHERE  rn = 1;

Step-by-step explanation.

Join touchpoints to purchases on the same user_id where the touch happened at or before the purchase.
ROW_NUMBER() OVER (PARTITION BY user_id, purchase_time ORDER BY touch_time DESC) numbers the touchpoints from newest to oldest per purchase. The last touch gets rn = 1.
Filter to rn = 1 — that is the last touchpoint per purchase.
Attribute 100% of the revenue to that channel — the last-touch rule.
For user u1's July 15 purchase, touchpoints in the last 5 days are Facebook (July 10), Google (July 14), Email (July 15). Last-touch = Email → revenue 120 attributed to email.

Output.

user_id	purchase_time	revenue	last_touch_channel	attributed_revenue
u1	2026-07-15 10:30:00	120	email	120

Rule of thumb. For last-touch, ROW_NUMBER() OVER (PARTITION BY purchase ORDER BY touch_time DESC) is the whole recipe. For first-touch, flip the order to ASC. For linear, replace with 1.0 / COUNT(*) per purchase.

Worked example — funnel SQL with self-joins

Detailed explanation. Compute a 4-stage funnel — visit → product view → add-to-cart → purchase — per day. Show the row count and the conversion rate at each stage.

Question. Write the funnel SQL. Show both a self-join approach and a COUNT(DISTINCT ... FILTER) alternative.

Input — clickstream.

user_id	event_type	event_time
u1	visit	2026-07-14 10:00:00
u1	product_view	2026-07-14 10:02:00
u1	add_to_cart	2026-07-14 10:05:00
u1	purchase	2026-07-14 10:10:00
u2	visit	2026-07-14 11:00:00
u2	product_view	2026-07-14 11:03:00

Code.

-- Approach 1 — COUNT DISTINCT FILTER (cleaner in modern SQL)
SELECT DATE_TRUNC('day', event_time) AS day,
       COUNT(DISTINCT user_id) FILTER (WHERE event_type = 'visit')        AS visits,
       COUNT(DISTINCT user_id) FILTER (WHERE event_type = 'product_view') AS product_views,
       COUNT(DISTINCT user_id) FILTER (WHERE event_type = 'add_to_cart')  AS carts,
       COUNT(DISTINCT user_id) FILTER (WHERE event_type = 'purchase')     AS purchases
FROM   clickstream
GROUP  BY 1
ORDER  BY 1;

-- Approach 2 — self-join for stage-by-stage conversion
SELECT DATE_TRUNC('day', v.event_time) AS day,
       COUNT(DISTINCT v.user_id)                                 AS visits,
       COUNT(DISTINCT pv.user_id)                                AS product_views,
       COUNT(DISTINCT c.user_id)                                 AS carts,
       COUNT(DISTINCT p.user_id)                                 AS purchases
FROM   clickstream v
LEFT   JOIN clickstream pv
       ON pv.user_id = v.user_id
      AND pv.event_type = 'product_view'
      AND pv.event_time >= v.event_time
LEFT   JOIN clickstream c
       ON c.user_id  = v.user_id
      AND c.event_type = 'add_to_cart'
      AND c.event_time >= v.event_time
LEFT   JOIN clickstream p
       ON p.user_id  = v.user_id
      AND p.event_type = 'purchase'
      AND p.event_time >= v.event_time
WHERE  v.event_type = 'visit'
GROUP  BY 1
ORDER  BY 1;

Step-by-step explanation.

Approach 1 uses FILTER (WHERE ...) (supported in Snowflake, BigQuery, Postgres, DuckDB) to count distinct users per event type. Fast: single scan of the table.
Approach 2 self-joins the same table four times, once per stage, on user_id with an event-time constraint. Correct but expensive on large data.
Both approaches give the same per-day counts. Approach 1 wins on performance; approach 2 wins on transparency (each stage is a clear join).
For "conversion within the same session" (stricter), add AND ... session_id = v.session_id to each join. That flips the funnel from user-based to session-based.
The conversion rates are computed by division: product_views / visits, carts / product_views, purchases / carts. Chart those rates over time to spot funnel regressions.

Output.

day	visits	product_views	carts	purchases
2026-07-14	2	2	1	1

Rule of thumb. For quick funnel counts, use COUNT(DISTINCT ... FILTER). For strict "within same session" funnels, self-join with a session_id predicate. Never use COUNT(*) without DISTINCT — you will double-count users who fire an event twice.

Worked example — cohort retention SQL

Detailed explanation. Compute weekly cohort retention. Cohort a user by their first-purchase week; count how many purchase again in each subsequent week.

Question. Write the cohort-retention SQL. Show the cohort × week grid.

Input — purchases.

user_id	purchase_date
u1	2026-07-01
u1	2026-07-08
u1	2026-07-15
u2	2026-07-01
u2	2026-07-15
u3	2026-07-08

Code.

WITH first_purchase AS (
  SELECT user_id,
         DATE_TRUNC('week', MIN(purchase_date))::date AS cohort_week
  FROM   purchases
  GROUP  BY user_id
),
activity AS (
  SELECT p.user_id,
         fp.cohort_week,
         DATE_TRUNC('week', p.purchase_date)::date AS activity_week
  FROM   purchases p
  JOIN   first_purchase fp ON fp.user_id = p.user_id
),
cohort_size AS (
  SELECT cohort_week, COUNT(DISTINCT user_id) AS cohort_users
  FROM   first_purchase
  GROUP  BY cohort_week
)
SELECT a.cohort_week,
       a.activity_week,
       (a.activity_week - a.cohort_week) / 7 AS weeks_since_first,
       COUNT(DISTINCT a.user_id)             AS active_users,
       cs.cohort_users,
       ROUND(100.0 * COUNT(DISTINCT a.user_id) / cs.cohort_users, 1) AS retention_pct
FROM   activity a
JOIN   cohort_size cs USING (cohort_week)
GROUP  BY a.cohort_week, a.activity_week, cs.cohort_users
ORDER  BY a.cohort_week, a.activity_week;

Step-by-step explanation.

first_purchase finds each user's first-purchase week — their cohort assignment.
activity joins every purchase to the user's cohort so each purchase carries both the cohort and the activity week.
cohort_size counts users per cohort — the denominator for retention percentages.
The final query groups by (cohort × activity_week), counts distinct active users, divides by the cohort size for a percentage.
Week 0 (activity_week = cohort_week) is 100% by definition. Later weeks show the decay curve.

Output.

cohort_week	activity_week	weeks_since_first	active_users	cohort_users	retention_pct
2026-06-29	2026-06-29	0	2	2	100.0
2026-06-29	2026-07-06	1	1	2	50.0
2026-06-29	2026-07-13	2	2	2	100.0
2026-07-06	2026-07-06	0	1	1	100.0

Rule of thumb. Cohort retention SQL is always three CTEs: cohort assignment, activity per period, cohort size. Present the result as a matrix (cohort rows × week columns) and colour the cells by retention percentage; that heatmap is the standard e-commerce retention chart.

Senior interview question on picking the right attribution model

A senior interviewer might ask: "The marketing team says last-touch attribution is underweighting brand advertising. Walk me through the attribution-model choices, the data you need for each, and how you would design a test to pick between them."

Solution Using a decision framework + holdout experiment

Attribution model — the decision path
=====================================

If touchpoints per purchase ≤ 1        → any model gives the same answer
If touchpoints per purchase 2–3        → linear or time-decay
If touchpoints per purchase ≥ 4        → time-decay or data-driven
If you can run channel holdout tests   → holdout is the ground truth

Data requirements
    - first-touch / last-touch: touchpoint stream with timestamps
    - linear                  : same
    - time-decay              : same + decay half-life config
    - data-driven             : 90+ days of purchases + variety in paths
    - holdout                 : ability to withhold a channel for 30 days

Model comparison test
    1. Compute attribution under each model on the same 90-day window.
    2. Aggregate revenue-per-channel under each model.
    3. Rank channels; note which channels move most across models.
    4. Run a 30-day channel holdout on the top mover.
    5. Compare actual revenue lift/loss vs the model's prediction.
    6. The model whose prediction matches the holdout is your winner.

Step-by-step trace.

Step	Action	Owner
1	Baseline attribution per model	data engineer
2	Revenue-per-channel comparison	analyst
3	Rank channels by disagreement	analyst
4	Pick a channel for holdout	marketing + analyst
5	30-day paused-channel test	marketing
6	Compare model prediction to observed lift	analyst

The 90-day comparison is cheap; the holdout is where you spend the marketing dollars. Data-driven attribution almost always beats last-touch on multi-touch products, but you need the holdout to prove it.

Output:

Model	Best for	Data need
Last-touch	short, single-channel journeys	none extra
First-touch	brand-driven categories	none extra
Linear	short-to-medium journeys	none extra
Time-decay	medium-to-long journeys	half-life config
Data-driven	long, multi-channel journeys	90+ days variety
Holdout	ground truth for any of the above	30-day channel pause

Why this works — concept by concept:

Journey length dictates model — no model is universally best; the number of touchpoints per purchase is the strongest predictor of which model is closest to truth.
Data-driven needs variety — Shapley values are noisy if every user takes the same path. 90 days + varied paths is the minimum viable dataset.
Holdout is the ground truth — the only way to know a channel's true contribution is to pause it. Attribution models approximate what holdouts prove; use holdouts to calibrate the models.
Model comparison is cheap — recomputing attribution under 5 models on the same data is a few SQL queries. The expensive step is the holdout; only run one, on the channel where models disagree most.
Cost — attribution model choice is a $1M–$10M decision at a mid-size retailer. Spend the two engineering weeks on comparison + holdout rather than defaulting to last-touch forever.

SQL
Topic — window-functions
Attribution + funnel window-function drills

Practice →

SQL
Topic — aggregation
Cohort + LTV aggregation problems

Practice →

Cheat sheet — e-commerce DE recipes

Clickstream stack. First-party collector (Snowplow / RudderStack) → schema registry (Iglu / Confluent) → bot filter → consent gate → 30-min sessioniser. Land raw to S3, curated to warehouse.
Sessionisation SQL. LAG(event_time) OVER (PARTITION BY user_id ORDER BY event_time) + CASE gap > 30 min → 1 else 0 + cumulative SUM = session number. Concatenate with user_id for a globally unique session_id.
ID stitching. Deterministic first (login joins device_id → user_id), probabilistic as fallback (IP + UA + fingerprint clustering, 60-80% accuracy). Keep a slowly-changing user_id_map so historic events can be re-attributed.
Consent-aware pipelines. Consent is a stream, not a boolean. Every event carries its consent-state-at-event-time via a LEFT JOIN LATERAL to the consent history. Downstream filters by that column.
Feature store. Offline = Iceberg on S3 (append-only, one row per feature per event_time); online = Redis / DynamoDB (latest per key, TTL 7d). Feast handles materialisation + point-in-time joins; do not roll your own.
Point-in-time join. For each label at label_time, pick the feature row with MAX(event_time) where event_time <= label_time. Skipping this is the #1 cause of training-serving skew.
Two-stage recs. Candidate generation via ANN (FAISS / ScaNN) prunes 500K SKUs to 1K in ~20ms; ranking scores the 1K with a GBDT in ~50ms. Never rank the full catalog.
Cold-start policy. < 5 interactions: popularity in demographic bucket. 5–50 interactions: interleave popularity + weak personalisation. 50+: fully personalised.
Inventory MERGE. MERGE INTO stock USING deltas ON sku+warehouse WHEN MATCHED SET column = column + delta WHEN NOT MATCHED INSERT. Never SET column = source.column — you will wipe untouched columns.
Oversell invariant. on_hand >= 0 AND on_hand - committed - reserved >= 0. Check nightly; page on any violation. Also enforce inside the reservation transaction via SELECT ... FOR UPDATE.
Real-time stock projection. Flink KeyedProcessFunction on (sku, warehouse) maintains (on_hand, committed, reserved) state; emits available = max(0, on_hand - committed - reserved - buffer) to Redis on every event. Sub-5-second freshness.
Reservation TTL. Every cart reservation has a TTL (typically 15–60 min). On expiry, OMS emits a release event; the projection returns the units to available.
Attribution SQL. Last-touch = ROW_NUMBER() OVER (PARTITION BY purchase ORDER BY touch_time DESC) = 1. First-touch = ASC. Linear = 1.0 / COUNT(*) OVER (PARTITION BY purchase).
Funnel SQL. COUNT(DISTINCT user_id) FILTER (WHERE event_type = ...) per stage for user-based; add a session_id predicate for session-based. Never COUNT(*) without DISTINCT.
Cohort retention. Three CTEs: cohort assignment (MIN(purchase_date) per user, truncated to week), activity per user × week, cohort size for the percentage. Display as a matrix / heatmap.
LTV. Use lifetimes (BG/NBD + Gamma-Gamma) for e-commerce; scikit-survival for more general survival models. Never avg_order_value × avg_orders — that biases toward one-time buyers.
GA4 → BigQuery. Native connector, one row per event, partitioned daily. Always filter _TABLE_SUFFIX BETWEEN on the shard suffix, or you scan the whole property.
MMM inputs. Weekly channel spend + revenue + promotions + seasonality + macro (weather, holidays). Meta's Robyn is the standard; Google's LightweightMMM is the newer alternative.

Frequently asked questions

What is ecommerce data engineering?

ecommerce data engineering is the discipline of building and running the data pipelines that keep an online store's clickstream, recommendations, inventory, and attribution honest and fast. In a modern retailer, that means a first-party clickstream pipeline (collector, schema registry, sessioniser), a recommendation data pipeline with an offline + online feature store held consistent by point-in-time joins, an inventory join layer that MERGEs CDC deltas from OMS and WMS into the warehouse with a real-time projection to the PDP cache, and an attribution + funnel + cohort analytics stack for marketing and finance. The tightest SLA is usually recommendation serving (<100 ms p99); the highest-correctness bar is usually inventory. Both stress-test data infrastructure in ways that most other verticals don't have to handle at the same time.

How do you sessionise clickstream events?

Sessionisation groups a user's events into sessions using a 30-minute inactivity gap — any event more than 30 minutes after the previous event for the same user starts a fresh session. The canonical SQL recipe is LAG(event_time) OVER (PARTITION BY user_id ORDER BY event_time) followed by a CASE that flags gaps over 30 minutes, then a cumulative SUM of that flag to number the sessions. Concatenate the user_id with the session number for a globally unique session_id. At scale, run a Flink KeyedProcessFunction for streaming freshness and a nightly Spark re-sessionisation batch for correctness on late-arriving events — the batch table is the source of truth downstream, while the streaming output feeds live UIs. Common overrides: end sessions at midnight in the user's timezone, and end them on a campaign_id change so attribution can distinguish visits from different campaigns.

What is a recommendation feature store?

A recommendation feature store is a two-store system that keeps the same features consistent between model training and model serving. The offline store (Iceberg on S3, BigQuery, or Snowflake) holds every historical feature value with an event_time — used for training and backfill. The online store (Redis, DynamoDB, Cassandra) holds only the latest feature value per entity — used for serving under 10 ms. A materialisation job (Feast, Tecton) syncs offline → online on a cadence (15 min to 1 h for near-real-time features; sub-second for streaming ones). The critical contract is point-in-time correctness: for every training label at time T, the join picks the feature row with MAX(event_time) WHERE event_time <= T. Skipping this step is the number-one cause of training-serving skew — offline AUC of 0.95 with serving performance of 0.60 is the tell-tale symptom.

How do you prevent inventory oversell?

Oversell prevention is a two-layer defence. Ex-ante, every reservation runs inside a serialisable OMS transaction with a SELECT ... FOR UPDATE row lock on the (sku, warehouse) row — this serialises concurrent reservations so only one of two racing carts can grab the last unit. Inside the transaction, check on_hand - committed - reserved - safety_stock_buffer >= requested_qty; roll back if not. Ex-post, a nightly correctness batch enforces two invariants: on_hand >= 0 and on_hand - committed - reserved >= 0. Any violation is a P0 alert to inventory ops. Between these two, a Flink KeyedProcessFunction projects the running available_qty onto the PDP cache within 5 seconds so customers see a fresh number. The safety-stock buffer (fixed or dynamic per warehouse) is the extra guard that catches CDC lag or bug-driven inconsistencies before they turn into refunds.

What is the difference between last-touch and data-driven attribution?

Last-touch attribution assigns 100% of a purchase's revenue to the final marketing touchpoint before the purchase — the simplest model, and the default in most ad platforms. Data-driven attribution uses Shapley values (or similar cooperative-game solutions) to split credit across all touchpoints in the journey, weighted by each channel's marginal contribution to the observed conversion rate. Last-touch overvalues closers (retargeting, direct, email) and undervalues awareness channels (brand, top-of-funnel display); data-driven avoids that bias but needs 90+ days of purchase data with real variety in touchpoint paths. The canonical decision test: recompute channel-level revenue under both models on the same window, note where they disagree most, then run a 30-day holdout on the top-disagreement channel. The model whose prediction matches the observed revenue lift/loss is your winner.

What does an e-commerce data engineer do day-to-day?

An e-commerce data engineer's day cycles across the four pipelines: clickstream (adding events for a new page, debugging a bot-filter false positive, re-sessionising a late-arriving batch), inventory (troubleshooting a MERGE that silently wiped a column, tuning the Flink projection lag, chasing a 3-unit oversell across two warehouses), recommendations (adding a new feature to the offline + online stores, fixing a point-in-time bug, tuning ANN recall vs latency), and attribution + analytics (writing a funnel query for a product manager, fixing a cohort retention chart that dropped 5% overnight, comparing attribution models for the marketing team). Interleaved with that: incident response during BFCM peaks, capacity planning for the next flash sale, and the constant negotiation between "correctness now" and "throughput now." The ecommerce data engineering role sits at the intersection of streaming, batch, warehouse SQL, and ML feature engineering — and the interview loops reflect that breadth.

Practice on PipeCode

Drill the SQL practice library → for the sessionisation / funnel / cohort family of e-commerce questions.
Rehearse on window-function problems → for LAG-based sessionisation and ROW_NUMBER-based attribution.
Sharpen the joins practice set → for point-in-time joins, inventory joins, and self-join funnels.
Stack the aggregation library → for cohort retention, LTV, and marketing-mix aggregates.
Layer the streaming practice library → for the Flink projection + KeyedProcessFunction family of problems.
Stack the real-time analytics library → for the end-to-end inventory + PDP-cache probes.
For the broader surface, read top data engineering interview questions →.
Stack the prerequisites with the only 5 skills you need to become a data engineer →.
Sharpen the batch axis with the Apache Spark internals course →.
For pipeline design, work through the ETL system design course →.

Pipecode.ai is Leetcode for Data Engineering — every clickstream, feature-store, inventory-MERGE, and attribution recipe above ships with hands-on practice rooms where you wire the sessionisation window, the point-in-time join, the safety-stock guard, and the last-touch attribution against real graded inputs. PipeCode pairs every reading with 450+ DE-focused problems and a real-time scoring engine, so you never have to wonder whether your `ecommerce data engineering` answer holds up under a senior interviewer's depth probes.

Practice SQL now →
Window-function drills →

1. Why e-commerce DE differs in 2026

E-commerce data lives at the intersection of high cardinality, hard SLAs, and unforgiving correctness — the four-pipeline stack is what senior DEs build

Worked example — sizing the four pipelines for a mid-size retailer

Worked example — the four-way trade-off in one query

Worked example — SKU × session state blow-up

Senior interview question on the four-pipeline stack

Solution Using a four-pipeline build order + SLA table

2. Clickstream event pipelines

A clickstream pipeline is a collector, a schema registry, a bot filter, a consent gate, and a sessioniser — in that order, without shortcuts

Worked example — sessionisation SQL with a 30-minute gap

Worked example — deterministic ID stitching across devices

Worked example — consent-aware event filtering

Senior interview question on clickstream sessionisation at scale

Solution Using a streaming sessioniser + nightly re-sessionisation batch

3. Recommendation feature stores

A recommendation data pipeline is two feature stores (offline + online) held consistent by a point-in-time join, feeding a two-stage retrieve-then-rank serving path

Worked example — point-in-time feature join for training data

Worked example — two-stage retrieval + ranking flow

Worked example — cold-start fallback for a new user

Senior interview question on feature store architecture

Solution Using Feast + point-in-time contract + skew monitoring

4. Inventory joins + real-time stock

An inventory join is a dual-write CDC from OMS and WMS into a warehouse MERGE, with a safety-stock buffer and a Flink projection into the PDP cache — anything less oversells

Worked example — MERGE for OMS + WMS upsert

Worked example — oversell detection SQL

Worked example — real-time Flink projection into Redis

Senior interview question on preventing overselling under load

Solution Using transactional reservation MERGE + streaming projection + PDP cache

5. Attribution + funnel analytics

ecommerce analytics closes the loop with a chosen attribution model, a session-safe funnel, and a cohort retention grid — never one-touch models on multi-touch journeys

Worked example — last-touch attribution SQL

Worked example — funnel SQL with self-joins

Worked example — cohort retention SQL

Senior interview question on picking the right attribution model

Solution Using a decision framework + holdout experiment

Cheat sheet — e-commerce DE recipes

Frequently asked questions

What is ecommerce data engineering?

How do you sessionise clickstream events?

What is a recommendation feature store?

How do you prevent inventory oversell?

What is the difference between last-touch and data-driven attribution?

What does an e-commerce data engineer do day-to-day?

Practice on PipeCode

A `clickstream pipeline` is a collector, a schema registry, a bot filter, a consent gate, and a sessioniser — in that order, without shortcuts

A `recommendation data pipeline` is two feature stores (offline + online) held consistent by a point-in-time join, feeding a two-stage retrieve-then-rank serving path

An `inventory join` is a dual-write CDC from OMS and WMS into a warehouse MERGE, with a safety-stock buffer and a Flink projection into the PDP cache — anything less oversells

`ecommerce analytics` closes the loop with a chosen `attribution model`, a session-safe funnel, and a cohort retention grid — never one-touch models on multi-touch journeys