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
We run a global beverage manufacturer, and every night roughly 900 distributors drop depletion and sell-through files into a shared bucket in whatever shape their local systems produce: CSV, semicolon-delimited exports, and the occasional spreadsheet, around 40 million rows a night with columns that drift and rename without warning. Design the batch pipeline that lands these raw files immutably, cleanses and conforms them into tables that can absorb the changing columns and be overwritten one distributor-day at a time, and publishes a trustworthy Gold layer of daily sales by distributor and product for BI the next morning. It runs on a nightly schedule that sequences the stages and lets a bad or late file from one distributor be reprocessed without corrupting the numbers everyone else already trusts.
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 on S3 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. A staging table makes a rerun idempotent. 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, S3)
- orchestrator (transform, Airflow); alerting: Missing or late distributor file vs expected roster
- bronze raw (storage, S3)
- silver conform (transform, Spark); idempotency: staging_table
- quarantine (storage, S3); 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, Power BI); 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.)