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

We ingest payment events from a few hundred partner APIs, some pushing webhooks and some we poll on a schedule, totaling around 2 billion events a day. Operations needs a near-real-time view of transaction volume and error rates within seconds, while finance needs an exactly-once daily ledger that reconciles to the cent. Partners go down and resend the same events when they recover, so the platform has to retry, deduplicate, and never lose or double-count a transaction.

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 two 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 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 before processing reads anything

Webhook pushes and scheduled pulls land in a durable queue first, so one bursty or offline partner is a non-event for everyone else: producers write at their pace, consumers drain at theirs. Read partner APIs straight into the stream and a single partner's recovery backlog stalls 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, reprocess the day from the lake and overwrite the 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. The result is written idempotently into 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

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 unhappy, differently.

Two 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. 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.)