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
Why this problem exists in real interviews
Two-year clickstream at 600M events per day with four properties tugging apart: fast recent queries with rare-but-possible older queries, marketing's near-real-time during flash sales, an unsustainable bill that has to come down, and 10x flash-sale spikes the pipeline can't drop. The trap is one warehouse holding two years; cost climbs and queries on older data still scan everything.
The default reach is one warehouse with two-year retention. The bill grows month over month. Queries on older data scan the full table because the layout doesn't match the access pattern. Marketing reads from the same warehouse and the dashboard stutters during a flash sale because the spike saturates compute. A flash sale's 10x burst drops events because nothing buffers.
Walk the requirements
Step 1: Recent fast, older possible , tier the storage to access pattern
Most history lives in cheap object storage with date partitioning. Recent data is also there but mirrored to a warehouse-grade hot tier (or the same lake with cluster keys for the common dimensions) so recent queries scan a small slice. Older partitions remain queryable through a serverless engine; older queries are slower and pay per byte scanned but still answerable. A 'one warehouse holds two years' design pays warehouse compute on data nobody touches; the tier is what makes the bill scale with access pattern.
Step 2: Marketing during flash sales: a streaming path on top of the lake
Marketing watches conversion in near real time during flash sales. A streaming consumer aggregates events for marketing's view within minutes; standard analytics reads a T+1 batch off the lake. One shared aggregator serving both is the version where standard analytics pays streaming compute or marketing waits for batch; two paths sized for the consumer is the contract.
Step 3: Bill comes down via partition layout and routing
Date-partitioned columnar files mean common date-filtered queries scan a slice rather than the full table. Repeated dashboard queries use pre-aggregated rollups so the same scan doesn't run a hundred times a day. Older queries pay for what they scan; recent queries hit the hot tier. The bill comes down because access pattern matches layout, not because the engine got cheaper.
Step 4: 10x flash-sale spike absorbs in the queue, not in the consumer
A queue between producers and the consumers buffers spikes; the consumers catch up at their own rate with backpressure visible in queue depth. A 'consumer at peak' approach drops events when the spike exceeds capacity; the queue is what gives the system slack while keeping the SLA for everyone else.
The shape that fits
- kafka_events (source, Kafka); parallelism: 16 partitions
- event_buffer (queue, Kafka); parallelism: 16 partitions
- streaming_marketing (transform, Flink); freshness: real-time; idempotency: upsert
- marketing_store (storage, PostgreSQL); freshness: < 1min
- lake_writer (transform, Spark); freshness: < 15min; backfill: partition_overwrite
- event_lake (storage, Iceberg); backfill: partition_overwrite
- hot_tier (storage, Snowflake); freshness: < 1h
- serverless_query (transform, SQL); freshness: < 1h
- marketing_team (consumer, Grafana); freshness: < 1min
- analytics_team (consumer, Tableau); freshness: < 1h
- kafka_events -> event_buffer
- event_buffer -> streaming_marketing
- event_buffer -> lake_writer
- streaming_marketing -> marketing_store
- lake_writer -> event_lake
- event_lake -> hot_tier
- event_lake -> serverless_query
- hot_tier -> analytics_team
- serverless_query -> analytics_team
- marketing_store -> marketing_team
- Source + buffer: kafka_events, event_buffer
- Marketing path: streaming_marketing, marketing_store
- Lake + tiers: lake_writer, event_lake, hot_tier, serverless_query
- Consumers: marketing_team, analytics_team
Common follow-up questions
- A historical query takes too long because the partition column doesn't match the filter. What in this design lets you accelerate it? (Tests whether the candidate sees the layout knobs available: better partition keys, additional cluster keys on the lake, materialized aggregates over the cold tier, or moving the recurring query onto a path with pre-aggregation. The decision depends on how often the query runs and what it scans.)
- A flash sale's 10x spike lasts an hour. What does this design do during the hour and immediately after? (Tests whether the candidate sees the queue holding the spike, consumers catching up at their throughput, and queue depth visible to on-call. After the spike, lag shrinks back to baseline; events aren't dropped. The streaming marketing path may show the lag with a freshness flag during the spike's tail.)