Two Years of Every Click

A hard Pipeline Design interview practice problem on DataDriven. Design the pipeline on the canvas: sources, transforms, storage and checks, then get feedback on it.

Domain

Pipeline Design

Difficulty

hard

Seniority

L7

Asked in

Walmart

Problem

Our e-commerce site generates 600 million clickstream events per day through Kafka. Analytics needs this data for user behavior analysis, funnel conversion, and A/B test evaluation, and retention policy requires two years of history. Design the storage and ingestion pipeline.

Every click, every aisle, every day for two years.

Business requirements

• Most queries hit the last month and need to come back fast; queries on data older than six months are rare but must remain possible.
• During flash sales, marketing watches conversion in near real time; standard analytics tolerates T+1.
• The current bill is unsustainable; the team has been told to bring it down meaningfully without losing query performance for recent data.
• Sales events drive a 10x volume spike; the pipeline can't drop events or fall behind during a sale.

Worked solution and explanation

The trap is one warehouse holding two years

This is an access-pattern problem dressed up as a storage problem. Anyone can draw Kafka into a warehouse. What separates candidates is seeing that four requirements want four different things: fast recent queries, rare older queries, a minutes-fresh view for marketing, and a buffer for 10x spikes. Get it wrong and you pay warehouse compute on two-year data almost nobody touches, while a flash sale saturates the same compute marketing is watching.

Step 1: Land every event once in cold object storage

The Iceberg lake, partitioned by event date, is the system of record for all two years. It is cheap, and it keeps the rare six-month-old query possible. Nothing else has to hold the full history.

Step 2: Mirror only the recent window into a fast warehouse

Most queries hit the last month, so only that slice earns warehouse prices. Older partitions are read in place by a serverless SQL engine billed by bytes scanned: slower, but the bill follows the access pattern.

Step 3: Fork marketing off the buffer, not off the lake

A Flink job reads the buffer and keeps a conversion view fresh in seconds, so the flash-sale dashboard never waits on batch. Analytics reads the batch path and tolerates T+1, so it never pays streaming compute.

Step 4: Absorb the 10x spike in the queue

Both paths read from a Kafka buffer, so a spike becomes consumer lag that drains afterward instead of dropped events. Size the consumers for normal load and let queue depth carry the surge.

One warehouse, two years

Every query pays warehouse compute, older queries still scan wide, and marketing shares compute with the sale spike.

Lake plus recent warehouse

History sits in cheap Iceberg partitions, the warehouse holds only the hot window, and marketing has its own streaming path.

Common follow-up questions

• A historical query is slow because the partition column does not match its filter. What do you change? (Tests knowledge of partition layout, pre-aggregated rollups and sort keys in the lake.)
• A flash sale runs 10x for an hour. What happens during it and right after? (Tests whether you can describe consumer lag growing, dashboard freshness degrading gracefully, and lag draining with no events lost.)