The Ledger and the Live Wire
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
- mid
- Asked in
- Amazon
Problem
We run an online retail marketplace that emits about 2 billion order and refund events a day across 300 million listings. Seller-facing inventory and sales dashboards have to reflect a purchase within seconds, while finance needs an exactly-once daily profit-per-product number that stays correct even when refunds land days after the original order. Data science also wants the full raw event history retained for model training.
Worked solution and explanation
Why this problem exists in real interviews
One event stream feeds two consumers with opposite correctness budgets. Sellers want a live count where approximate-but-fast is fine; finance wants a daily profit number that is exactly-once and stays right even when a refund shows up three weeks after the sale. The trap is a single pipeline that serves neither well: a live-ish aggregate finance can't trust, or an exact nightly batch sellers find useless because it's a day stale.
The default reach is a streaming job that maintains a profit table both the seller dashboard and finance read. It looks elegant until a producer retries and double-counts revenue, or a refund lands on day 20 and the profit for the original order date is silently wrong because the stream attributed nothing back to it. Now finance is restating numbers to leadership and nobody trusts the dashboard either.
Break down the requirements
Step 1: Absorb the firehose, then fan out
Two billion events a day with a 3x peak spike is not something you push straight into a database. A durable queue sits at the front so the streaming and batch consumers each read at their own pace, and a slow consumer never applies backpressure to the sellers. From the queue, raw events also land in the lake before anything aggregates them; that raw copy is what lets you reprocess when a refund arrives late.
Step 2: Give sellers a live, approximate path
Seller inventory and sales dashboards target under ten seconds, and they can tolerate a count that is briefly off as long as it self-corrects. A streaming processor maintains running counts and writes them into a low-latency serving store the dashboard queries directly. This path deliberately does NOT try to be the finance number; forcing exactly-once here is what makes it slow enough that sellers notice the lag.
Step 3: Make finance exactly-once and restatable
Profit per product per day is computed by a daily batch reading the raw lake. Dedup on a stable order/refund event id so producer retries never double-count revenue, and write with partition-overwrite keyed on product and event date so a re-run is idempotent. Crucially, profit is attributed to the ORIGINAL order's date, not ingest time, so when a refund lands on day 20 you re-run and overwrite only the affected product-day partitions and the historical number becomes correct again.
Step 4: Gate the number before finance sees it
A data-quality check runs between the aggregation and the warehouse: row counts, non-negative units, profit within sane bounds. Finance reads a published number that already passed the gate, and the orchestrator owns the schedule with a before-6am deadline and an alert if the run is at risk.
The reference architecture
- order_refund_events (source, Kafka / Kinesis, 2B events/day)
- live_stream (transform, Streaming, sub-10s); freshness: real-time
- seller_serving_store (storage, Low-latency KV); freshness: < 1min
- seller_dashboard (consumer, Seller UI); freshness: real-time
- raw_event_lake (storage, S3, partitioned by event date); backfill: partition_overwrite
- daily_profit_batch (transform, Spark on Airflow); idempotency: partition_overwrite
- profit_quality_gate (quality gate, Great Expectations); on error: alert
- profit_warehouse (storage, Redshift); freshness: < 24h
- finance_reporting (consumer, BI); freshness: < 24h
- ml_training (consumer, Model training)
- order_refund_events -> live_stream
- order_refund_events -> raw_event_lake
- live_stream -> seller_serving_store
- seller_serving_store -> seller_dashboard
- raw_event_lake -> daily_profit_batch
- daily_profit_batch -> profit_quality_gate
- profit_quality_gate -> profit_warehouse
- profit_warehouse -> finance_reporting
- raw_event_lake -> ml_training
- Ingest: order_refund_events
- Live path: live_stream, seller_serving_store, seller_dashboard
- Lake + batch: raw_event_lake, daily_profit_batch, profit_quality_gate
- Serving: profit_warehouse, finance_reporting, ml_training
Common follow-up questions
- A flash sale drives 10x the normal event rate for two hours. What in this design keeps the seller dashboards and the nightly finance run both healthy? (Tests queue-based backpressure and consumer isolation: the durable queue absorbs the spike, the streaming path may lag slightly but self-corrects, and the batch reads the lake later regardless of the spike.)
- The product taxonomy changes and 'cpu' (cost per unit) is now sent as a nested field. How does the pipeline handle the schema change without breaking the daily profit job? (Tests schema-registry / contract awareness and whether the candidate versions the event schema and evolves the batch read rather than letting a silent parse failure zero out profit.)