The Firehose and the Ledger
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
- senior
- Asked in
- Sigmoid
Problem
When a partner comes back from an outage and replays its backlog, our daily ledger double-counts and the live partner view stalls behind it. Design the pipeline that takes about 2 billion payment events a day from a few hundred partner APIs, some pushing webhooks and some polled on a schedule, and serves both operations and finance.
Business requirements
- Operations: I need to see transaction volume and error rate per partner within a few seconds, so I can catch a partner going bad before it becomes an incident. I do not care if a count is off by a handful in flight.
- Finance: my daily ledger has to reconcile to the cent. Every settled transaction counted once, no duplicates, no drops, even when a partner resends yesterday's events today.
- Platform: partners drop offline and replay their backlog when they recover. The pipeline must absorb retries and resends without losing or double-counting anything, and let us reprocess a day if our own logic was wrong.
- Platform: a few hundred partner APIs, some webhook push, some scheduled pull. One slow or bursty partner cannot be allowed to back up ingestion for everyone else.
Worked solution and explanation
Why this problem exists in real interviews
This is 2 correctness budgets fed by one unreliable firehose, dressed up as a platform. Operations wants an approximate view in seconds; finance wants an exact ledger once a day. The trap is the single pipeline that aggregates everything into one table both teams read: too slow to feel live for ops, too approximate to bill on for finance, and the day a partner replays its backlog it double-counts the ledger. The skill probed is whether you split the budgets and put exactness in batch, not the stream.
The other half is the ingestion edge: a few hundred partner APIs, some webhook push, some polled, bursting and failing independently. Read partners directly, or pour them all into one undivided lane, and one slow partner backs up everyone while a recovering partner's replay floods the system. Those retries and day-late resends are exactly what the design must absorb without dropping or double-counting a cent.
Walk the requirements
Step 1: Buffer the ingestion edge, one lane per partner
Webhook pushes and scheduled pulls land in a durable queue keyed by partner, so each partner writes into its own partitions. A bursty or recovering partner fills only its own lane while every other partner's partitions keep draining at their normal pace. A single undivided queue, or reading partner APIs straight into the stream, lets one partner's recovery backlog stall the platform during business hours.
Step 2: Land raw events immutably as the replay source
Every event is written as-is into the raw lake before any dedup. When your own logic is wrong, rerun the day's dedup from the lake and overwrite that day's ledger partition. Re-pulling hundreds of rate-limited partner APIs, some of which have aged the data out, is not a recovery plan.
Step 3: Give ops an approximate, seconds-fresh stream
A stream processor reads the queue and emits per-partner volume and error rate into a serving store the dashboard reads within seconds. Approximate is the budget: a transaction counted twice for a few seconds during a retry is invisible on a trend line. Put the exact ledger logic here and the view lags until ops stops trusting it.
Step 4: Make exactness a property of the daily batch, on a stable id
A batch job reads the day's raw events and deduplicates on the partner-assigned transaction_id over the full day, because a resend can arrive a day late. It overwrites the day's partition in the warehouse: rerun the day, get the same ledger. This is the only place exactly-once holds, because the window is the whole day, not a short streaming window.
The shape that fits
- partner_apis (source, API)
- per_partner_ingestion_queue (queue, Kafka)
- raw_event_lake (storage, object storage)
- ops_stream (transform, Flink)
- ops_serving_store (storage, Redis)
- ops_dashboard (consumer, Grafana)
- daily_ledger_dedup (transform, Spark); backfill: partition_overwrite
- ledger_warehouse (storage, warehouse)
- pipeline_monitoring (consumer, Prometheus)
- partner_apis -> per_partner_ingestion_queue
- per_partner_ingestion_queue -> raw_event_lake
- per_partner_ingestion_queue -> ops_stream
- ops_stream -> ops_serving_store
- ops_serving_store -> ops_dashboard
- raw_event_lake -> daily_ledger_dedup
- daily_ledger_dedup -> ledger_warehouse
- per_partner_ingestion_queue -> pipeline_monitoring
- One pipeline both teams read
- A single stream aggregates everything into one table. Ops sees lag because the table carries the heavier exactness logic; finance double-counts a day-late replay because the streaming dedup window is too short. Both teams are unhappy, in different ways.
- 2 paths, one source
- Ops reads a light streaming path tuned for seconds and approximate counts; finance reads a daily batch that dedups over the whole day and reconciles to the cent. The same raw events feed both; the budgets diverge after the lake.
Common follow-up questions
- A partner was offline all day and replays 400 million events at 2am, after your daily ledger ran. How does the design produce a correct ledger? (Tests using the immutable lake plus idempotent `partition_overwrite` to reprocess the day, not patching the warehouse in place or losing late events.)
- Operations now wants per-partner error alerts within 5 seconds, not just a dashboard. What changes? (Tests pushing alerting onto the streaming path with an alert destination while keeping exact accounting on batch.)
- The daily dedup job has started missing its morning SLA as volume grew. Where do you look first? (Tests skew diagnosis, lake file layout, and shuffle sizing before reaching for a bigger cluster.)