Prove the Number Is Right

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

L6

Problem

We show customers one unified view of their spending across their connected bank accounts. The data arrives from dozens of partner integrations, each delivering by webhook, scheduled file, or CDC, and compliance must be able to reproduce every number we serve. Design the data pipeline.

Bad data in fintech is not just messy. It is expensive.

Business requirements

• Customers expect their account balance to reflect a new transaction within minutes; transaction history can lag a couple hours.
• Compliance has to be able to prove our numbers match each partner's reported totals; today the gap can't be explained.
• When a partner's feed is late, that partner's customers should see their last known balance rather than nothing; healthy partners must keep flowing.
• SEC audit requires every validation result, pass or fail, to be retained for seven years.

Worked solution and explanation

What this really is

This is a reconciliation pipeline dressed up as a spending dashboard. Anyone can draw partners flowing into a warehouse. What separates candidates is whether proof is a stage in the pipeline or a spreadsheet someone runs afterward. One shared loader on one cadence fails three ways. Balances go stale, one late partner stalls everyone, and when compliance asks why partner X is short this week, nobody can say, because nothing ever compared your totals to theirs.

Walk the requirements

Step 1: Split one feed into two cadences

Balances must land within minutes, and history can lag a couple of hours. Fan out from partner_topics. One branch runs balance_stream in real time into balance_store. The other runs transaction_loader as a batch under a two-hour SLA. A single cadence either pays streaming prices on every historical row or leaves balances stale.

Step 2: Isolate partners, keep the last known balance

per_partner_ingest runs one task per partner, so a late feed stalls only its own task and healthy partners keep flowing. balance_store holds the latest balance per account, so a silent partner's customers still see their last known value instead of an empty page.

Step 3: Gate each batch on partner totals

reconciliation_gate sums each partner's batch and compares it to the total that partner reported. A gap beyond tolerance flags the batch in the run that created it, not a month later.

Step 4: Archive every verdict for seven years

The gate writes one row per check to validation_archive: partner, batch, expected total, actual total, tolerance and result. Keep it queryable for the SEC window, so an auditor's question becomes a query instead of a promise.

Step 5: Serve the customer from both tiers

customer_app reads the balance from balance_store and the history from transaction_warehouse. Drop that second edge and customers see a number with no spending behind it.

The reference design

One shared loader

Every partner shares one job and one cadence. A late partner blocks the run, every balance goes stale, and nobody checks totals against what partners report.

Per-partner ingest

Each partner gets its own task in per_partner_ingest. A late partner stalls only itself, and reconciliation_gate checks each batch against that partner's own reported total.

Common follow-up questions

• A partner reports totals daily but streams transactions continuously. How do you reconcile? (Tests windowing: the gate sums the window up to the partner's daily cutover and checks that sum, while `balance_store` is untouched.)
• A reconciliation flag fires. What do the customer, compliance and on-call each see? (Tests triage: balances keep streaming, history holds at the last reconciled batch, compliance sees the flagged row in `validation_archive`, and on-call investigates rather than auto-correcting.)