600 Million Events a Day
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
Problem
A Kafka stream carries 600 million e-commerce interaction events a day, retained for two years and read by four consumers on very different clocks: personalization, fraud, analytics dashboards and model training. Design the storage and serving architecture that carries those events from the stream to every reader.
600 million events a day. Two years of retention.
Business requirements
- Personalization needs a user's latest activity within seconds, fraud reads purchases within minutes, analytics dashboards accept 15 minutes of lag, and model training runs on next-day data.
- Date-filtered analytics queries currently scan the entire two-year history and have to stop doing so.
- A right-to-erasure request has to flow from a deletion stage into every store that holds that user's events.
Worked solution and explanation
The trap
This is a storage-tiering-by-access-pattern problem wearing an e-commerce costume. Four consumers want the same 600M daily events on four different clocks: personalization in seconds, fraud in minutes, analytics in a quarter-hour, training overnight, plus two-year retention and GDPR erasure. The trap is one warehouse for everything. The query bill climbs because every date-filtered query scans two years, personalization can't hit its budget reading the warehouse, and when a right-to-erasure request lands the user sits in five undocumented places. Treat 'one store for two years' as a cost decision that has to be justified and it falls apart.
Walk the requirements
Step 1: Match four consumers to four budgets
Personalization reads features from online_store in seconds; fraud reads fraud_store in minutes; analytics queries the warehouse on a 15-minute view; training reads partitioned files from event_archive. All are fed from the same events; the budgets diverge after ingest. One shared store makes at least three of the four consumers miss their clock.
Step 2: Lay the lake out for the bill
Most data lives in cold storage in an open table format, date-partitioned and clustered by the common BI filter (segment, country, product family). A 'last week' query scans a few partitions, not two years. Repeated dashboards read pre_aggregated_views so the same scan doesn't run twice. The bill drops because the layout matches the access pattern, not because the engine got cheaper.
Step 3: Propagate deletion with confirmation
GDPR erasure must reach every store that holds user-level events: event_archive, online_store, fraud_store and analytics_warehouse. The request rides the same fan-out the events did; each store deletes and writes a confirmation; deletion_orchestrator collects them to prove the regulatory window. pre_aggregated_views rebuild from the warehouse, so erased users drop out on the next recompute. Without the orchestrator, deletion is a manual hunt and the audit answer is a promise, not a record.
Step 4: Evolve the schema additively
New event fields arrive each quarter as nullable columns defaulting null on historical rows. Queries that ignore the field are unaffected; queries that use it filter to the rows that have it. The alternative, rewriting history on every field add, takes the warehouse offline for hours each quarter. Additive evolution is what keeps the layer stable as columns accumulate over years.
The shape that fits
- kafka_events (source, Kafka)
- event_archive (storage, Iceberg)
- streaming_personalization (transform, Flink)
- online_store (storage, PostgreSQL)
- fraud_stream (transform, Flink)
- fraud_store (storage, PostgreSQL)
- analytics_warehouse (storage, warehouse)
- pre_aggregated_views (storage, data mart)
- deletion_orchestrator (transform, Airflow)
- personalization (consumer, API)
- fraud_team (consumer, Grafana)
- analytics_team (consumer, dashboard)
- training_job (consumer, Jupyter)
- kafka_events -> event_archive
- kafka_events -> streaming_personalization
- kafka_events -> fraud_stream
- streaming_personalization -> online_store
- fraud_stream -> fraud_store
- event_archive -> analytics_warehouse
- analytics_warehouse -> pre_aggregated_views
- event_archive -> training_job
- online_store -> personalization
- fraud_store -> fraud_team
- pre_aggregated_views -> analytics_team
- deletion_orchestrator -> event_archive
- deletion_orchestrator -> online_store
- deletion_orchestrator -> fraud_store
- deletion_orchestrator -> analytics_warehouse
- Source + archive: kafka_events, event_archive
- Streaming + online: streaming_personalization, online_store, fraud_stream, fraud_store
- Warehouse + aggregates: analytics_warehouse, pre_aggregated_views
- Deletion + consumers: deletion_orchestrator, personalization, fraud_team, analytics_team, training_job
Common follow-up questions
- Analytics wants a new sub-second dashboard. What accommodates it without putting `analytics_warehouse` on the hot path? (Tests whether the candidate reaches for a new `pre_aggregated_views` materialization off the warehouse, indexed for the dashboard's query pattern, rather than a fresh path from the source. The warehouse stays the slow path.)
- A deletion confirmation from `pre_aggregated_views` is overdue because its recompute hasn't run. What does `deletion_orchestrator` surface, and how does the audit answer read? (Tests whether the candidate holds the request open per store with a recompute trigger and reports honest per-store status: confirmed where done, pending where the view hasn't recomputed.)