A Clean Number for Every Merchant

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

L7

Problem

Our payments platform generates a daily log containing every transaction across all merchants. The finance and BI teams need a clean merchant-level summary - total volume, transaction count, average amount - but the raw logs have duplicates, schema inconsistencies, and no restart safety. Design a batch pipeline that reliably produces this summary.

Raw payment logs in. Clean merchant summaries out.

Business requirements

• Finance reconciles at 7am every morning and the merchant summary has to be ready and validated by then.
• Gateway retries deliver some events twice; merchant totals can't be inflated by those duplicates.
• Records with missing merchant or amount can't be silently dropped; finance reviews them daily.
• When something fails partway through, an operator reruns the day, and the rerun has to produce the same numbers as a clean run.

Worked solution and explanation

What this really is

This is an idempotency problem dressed up as a daily rollup. Anyone can draw source, Spark, warehouse, dashboard. What separates candidates is whether four requirements hold at the same time: the 7am deadline, dedup of gateway retries, a home for bad rows, and reruns that land on identical numbers. Solve them one at a time and they fight each other. Pair an append-style write with a dedup that keeps whichever copy arrived first, and the operator's rerun moves a merchant's total by a few thousand dollars. After that, finance stops trusting the warehouse.

Walk the requirements

Step 1: Put an orchestrator on the deadline

Airflow owns the daily DAG and its retries, and it alerts while there is still time to act if the run threatens 7am. Without it, finance discovers the missed window when they open the dashboard. The summary lands in the warehouse finance already reads.

Step 2: Dedup on payment_id before aggregating

A retried event has to collapse to one row whatever order the copies arrive in. Once copies are summed into a merchant total, you cannot take them back out.

Step 3: Quarantine bad rows, keep the rest moving

Rows missing merchant or amount go to a quarantine table along with the rejection reason. Valid rows flow on to aggregation. Failing the whole run over one bad row blocks finance. Dropping bad rows silently makes the totals drift away from the gateway's.

Step 4: Replace the date, never append to it

Build the day in staging, then delete that date from the summary and write the staged rows in its place. Whichever stage failed, rerunning the date produces the same end state.

The reference design

Append the day

A failed run leaves half a day in the summary. The rerun appends a second copy on top, and a merchant's total now depends on where the first run died.

Replace the day

Staging builds the full day, then the date is deleted and rewritten in one step. Whether this is the first run or the third, the date ends up holding the same rows.

Common follow-up questions

• A merchant's total jumped today, but the merchant says volume was flat. Where do you look first? (Tests reading the pipeline's evidence in order: quarantine replays, then dedup collapse counts, then the gateway log.)
• Finance wants the summary for a date six months ago, rebuilt from the raw log. Will it match the original? (Tests whether deterministic dedup plus a per-date replace makes any day reproducible.)