Everything Lands, Then It Ships
A medium 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
- medium
- Seniority
- mid
- Asked in
- Anheuser-Busch
Problem
Every night roughly 900 of our distributors drop about 3,000 sales files into a shared bucket, around 40 million rows in CSV, semicolon exports and the odd spreadsheet, with columns that drift without warning. Design the nightly batch pipeline that turns them into trustworthy daily sales by distributor and product for BI each morning.
Business requirements
- When a distributor sends a corrupt file, we need to go back to exactly what they sent and reprocess it without guessing.
- Distributors rename and reorder columns constantly, and the numbers still have to line up across all of them.
- BI opens the daily sales dashboard every morning and it has to be right and complete.
- One late file at 3am shouldn't force a full re-run or corrupt the distributors that already loaded cleanly.
Worked solution and explanation
What this really is
This is a replay problem dressed up as a nightly load. Anyone can draw Bronze, Silver and Gold. The real question is which tier is allowed to change. Bronze is append-only and never rewritten. Silver and Gold are overwritten one distributor-day at a time. Merge those two rules and either choice hurts you. Append straight into Gold, and a resent Tuesday file doubles Tuesday. Overwrite the raw tier, and a resend erases what the distributor first sent. If that 'correction' turns out to be the corrupt file, you have nothing left to replay.
The layers do real work because of the input: about 900 uncoordinated senders, around 3,000 files a night because some split their drop, headers that drift without warning, and corrections that arrive out of order. Each tier owns exactly one of those problems.
Four decisions, in order
Step 1: Land every file once and never touch it again
Bronze in object storage is partitioned by distributor and arrival date. A resend lands next to the original with its own arrival timestamp, so both versions survive. Ingest copies bytes and parses nothing, which means a renamed column can never fail it.
Step 2: Conform the drift into one Silver schema
Per-distributor column mappings turn CSVs, semicolon exports and spreadsheets into one table keyed on distributor_id, product_id and business_date. Silver reads the latest Bronze file for each distributor-day. Rows that cannot be mapped go to quarantine instead of aborting the run.
Step 3: Gate Silver before anything reaches Gold
Check completeness against the expected roster (did all 900 land?), confirm keys are non-null, and confirm volumes sit inside their baseline. A failing distributor is held back and alerted on. The other 899 publish on time.
Step 4: Overwrite by partition, downstream of raw
Silver and Gold use Delta Lake atomic overwrite to rewrite exactly one (distributor_id, business_date) slice, so running a slice twice leaves the same numbers. This only works because a resend is always the distributor's complete file for that day. When a straggler arrives at 3am, Airflow reruns that one partition and leaves the rest alone.
The reference design
- distributor files (source)
- orchestrator (transform, Airflow); alerting: Missing or late distributor file vs expected roster
- bronze raw (storage, object storage)
- silver conform (transform, Spark)
- quarantine (storage, object storage); alerting: Unmappable rows above baseline
- silver sales (storage, Delta Lake); backfill: partition_overwrite
- quality gate (quality gate, Great Expectations); on error: alert
- gold sales (storage, Delta Lake); freshness: < 24h; backfill: partition_overwrite
- bi (consumer, dashboard); freshness: < 24h
- distributor files -> orchestrator
- orchestrator -> bronze raw
- bronze raw -> silver conform
- silver conform -> quarantine
- silver conform -> silver sales
- silver sales -> quality gate
- quality gate -> gold sales
- gold sales -> bi
- Ingest: distributor files, orchestrator, bronze raw
- Conform + gate: silver conform, quarantine, silver sales, quality gate
- Publish: gold sales, bi
- Overwrite in Bronze
- A resend replaces the original file. If the resend is the broken one, the good copy is gone and you have to email the distributor and wait.
- Append-only Bronze
- Every received file is kept. Silver chooses the latest file per distributor-day, and rolling back means pointing Silver at the earlier arrival and rerunning one partition.
Common follow-up questions
- A corrected file for a date three weeks ago arrives after demand planning has already consumed Gold. What happens? (Tests historical replay from append-only Bronze, and whether downstream consumers are notified of the restatement.)
- Volume grows 10x and files start landing in the middle of the batch window. What changes? (Tests partition parallelism and whether ingest should be triggered by file arrival instead of the clock.)