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

This is one event stream with 2 opposite correctness budgets, dressed up as a dashboard problem. Sellers want a number within seconds and can live with it being briefly off. Finance wants an exactly-once daily profit that stays right when a refund lands 3 weeks after the sale. Anyone can draw a stream and a batch. The trap is a single streaming job that both audiences read: a producer retry double-counts revenue, a day-20 refund gets booked to its arrival date, and the profit for the original sale day is permanently wrong.

Split by correctness budget

Step 1: Land raw events before anything aggregates them

order_refund_events is a durable Kafka topic, so a slow consumer never back-pressures a fast one. It fans out to 2 readers. One of them is raw_event_lake, the replayable record of every order and refund. That copy is what lets you recompute history when a refund arrives late, and it is the full raw history data science asked for.

Step 2: Give sellers a fast, approximate path

live_stream runs Flink and keeps running counts in seller_serving_store, a Redis key-value store the dashboard reads directly. This path is the only one marked real-time. Do not try to make it the finance number: exactly-once and fully reconciled is exactly what would make it slow enough for sellers to notice.

Step 3: Make the finance number restatable

daily_profit_batch reads the lake, dedups on a stable event id so retries never double-count, and attributes profit to the original order's event date, not its ingest time. Its delete_write idempotency means a re-run replaces a product-day partition instead of adding to it, and its partition_overwrite backfill is how a late refund restates only the days it touches.

Step 4: Gate the number before it is published

profit_quality_gate checks row counts, non-negative units and profit bounds between the batch and profit_warehouse. Finance reads only what already passed, through finance_reporting, on a < 24h cadence.

One durable ingest fans out: a streaming path for sellers, and a lake feeding an idempotent daily batch that restates product-day partitions when refunds arrive late.
  • order_refund_events (source, Kafka)
  • live_stream (transform, Flink)
  • seller_serving_store (storage, Redis); freshness: real-time
  • seller_dashboard (consumer)
  • raw_event_lake (storage, data lake)
  • daily_profit_batch (transform, Spark); backfill: partition_overwrite; idempotency: delete_write
  • profit_quality_gate (quality gate, Great Expectations)
  • profit_warehouse (storage, warehouse); freshness: < 24h
  • finance_reporting (consumer)
  • ml_training (consumer)
  • 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

Common follow-up questions