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

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

Partners write into a queue keyed by partner, so a burst stays in its own lane. That buffered, immutably archived source feeds 2 paths: approximate and live for ops, deduplicated and daily for finance. The raw lake is the replay source when the dedup logic is wrong.
  • 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