Fresh and Forever
A medium 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
- medium
- Seniority
- mid
- Asked in
Problem
Our live operations dashboard trails reality by minutes, and its counts disagree with the numbers analysts later pull from history, on a platform ingesting about 5 billion user-interaction events a day. Design the pipeline that serves both the dashboard and the analysts' ad-hoc queries over years of events.
Business requirements
- Operations needs the live dashboard to reflect what is happening within a few seconds; anything slower and we are reacting to the past.
- Analysts run ad-hoc queries going back years and cannot be limited to a short retention window.
- Our counts have to be right even when events show up late or get sent twice; finance and ops both trust these numbers.
Worked solution and explanation
Why this problem exists in real interviews
This looks like 'build a real-time pipeline,' but the real probe is whether you notice there are two consumers with opposite budgets. Operations wants the last few seconds and will trade exactness for speed; analysts want the last few years and will wait until tomorrow for a correct number. The trap is one store and one freshness tier that tries to serve both: stream everything into the analytics warehouse and the dashboard lags; size one store to hold years at sub-second latency and the bill is absurd. The seconds-vs-years split, not the word 'Kafka,' is the answer.
The naive design pipes the stream straight into the warehouse and points both the dashboard and the analysts at it. The dashboard is slow because warehouse loads batch up, and the historical queries are expensive because they scan a store that is also trying to ingest 5 billion rows a day. On top of that, producer retries double-count and a late event lands outside its window, so the numbers the ops team reacts to are quietly wrong.
Break down the requirements
Step 1: Pin the two freshness tiers
Operations dashboards are useless past a few seconds; analyst history queries are fine at T+1. That single fact rules out one shared store. The live path runs sub-minute; the analytics path runs batch. Forcing analysts onto streaming wastes money; forcing ops onto batch misses the requirement.
Step 2: Land every raw event durably before processing
The durable event lake is the system of record and the replay source. The serving store only needs the last few hours, but the lake holds 3+ years and lets you backfill the warehouse when a load fails. Without it, a downstream bug means lost history you can never reconstruct.
Step 3: Make counts correct under retries and late data
Producers retry, so the same event_id can arrive twice; dedup on it at the stream processor and on load to the warehouse. Late events are expected, so windowed aggregates carry a grace period instead of dropping anything past the watermark. Skip this and the live number the ops team trusts is inflated and jittery.
The reference architecture
- event_ingest (source, Kafka)
- dedup_late_window_counts (transform, Flink); freshness: real-time
- serving_store (storage, Redis)
- ops_dashboard (consumer, Grafana)
- raw_event_lake (storage, object storage)
- batch_dedup_load (transform, Spark)
- analytics_warehouse (storage, warehouse)
- analyst_bi (consumer, Metabase)
- event_ingest -> dedup_late_window_counts
- dedup_late_window_counts -> serving_store
- serving_store -> ops_dashboard
- event_ingest -> raw_event_lake
- raw_event_lake -> batch_dedup_load
- batch_dedup_load -> analytics_warehouse
- analytics_warehouse -> analyst_bi
One ingest log fans out. The stream processor dedups, maintains event-time windowed aggregates with a grace period, and upserts them into a small low-latency serving store the dashboard polls; only the last few hours live there. In parallel, raw events land in a partitioned lake that is the replayable system of record, and a batch job dedups and loads them into the warehouse where analysts run multi-year queries. The serving store stays small and fast; the warehouse stays cheap per query because it is not also ingesting the firehose.
Common follow-up questions
- The event volume 10x's to 50 billion a day for a launch. What in this design absorbs it and what has to change first? (Tests whether the candidate sizes ingest partitions and stream-processor state for peak, and isolates the live path so a spike does not stall warehouse loads.)
- An analyst reports that a count in the warehouse disagrees with what the live dashboard showed yesterday. How do you reconcile them? (Tests understanding that the live view is approximate within its window while the warehouse is the corrected, deduped, late-data-included source of truth, reconciled from the lake.)