# Everything Lands, Then It Ships Canonical URL: Domain: Pipeline Design · Difficulty: medium · Seniority: mid ## 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. ## Worked solution and explanation ### Why this problem exists in real interviews The ask sounds like plumbing (get the files into Gold) but the real probe is whether you reach for the medallion pattern or collapse it. The trap is treating 'make it available in Gold' as one hop: parse the 900 nightly files and write straight to the daily sales table. It demos fine on day one. Then a distributor renames a column, a corrected file arrives for last Tuesday, and one malformed export takes the whole batch down, and there is no raw copy to replay from. Get this wrong and Gold becomes a table nobody trusts by the end of the first month. The reason Bronze and Silver aren't ceremony here is the input: 900 uncoordinated senders, drifting schemas, and out-of-order corrections. Bronze exists so you can reprocess exactly what was sent; Silver exists so 900 different headers become one schema before anyone aggregates; the quality gate exists so a bad file is caught before BI sees it, not after. Skip a layer and one of those guarantees quietly disappears. --- ### Break down the requirements #### Step 1: Land raw and immutable before touching anything The files hit a landing zone exactly as sent, schema-on-read, partitioned by distributor and arrival date. Ingest must never fail because a column moved. This Bronze copy is what makes 'reprocess that one corrupt file' a real operation instead of an email asking the distributor to resend. #### Step 2: Conform the mess in Silver Per-source column mappings turn semicolon exports, CSVs, and spreadsheets into one canonical schema keyed on distributor_id, product_id, business_date. Rows that can't be mapped route to a quarantine path rather than killing the run. This is the layer that absorbs schema drift so Gold never has to think about it. #### Step 3: Gate before you publish, partition so you can replay Data-quality and completeness checks run on Silver: did all expected distributors land, are keys non-null, are volumes sane. Only passing partitions publish to Gold, written with partition-overwrite on (distributor_id, business_date) so a corrected file replaces exactly its slice and never double-counts. A failing distributor is held and alerted; everyone else still ships by 7am. --- ### The reference architecture An orchestrator (Airflow) wakes after the arrival window, kicks a Spark batch job that lands the day's files to a cold-storage Bronze zone, then a second stage conforms them into a Silver lakehouse table. A quality gate (Great Expectations) validates Silver; on pass, the aggregation writes daily sales-by-distributor-by-product into the Gold lakehouse table with partition-overwrite, and BI reads Gold the next morning. Reprocessing a single late file is just re-running the DAG scoped to one partition. > **Scale + Cost** > > 40M rows and under ~5 GB compressed a night is a small batch job, not a distributed-systems problem. Cost concentrates in storage duplication across Bronze/Silver/Gold, not compute. The bottleneck is the arrival window: files finish ~3am, Gold is due 7am, so the four-hour window is your real budget. Partition pruning on (distributor_id, business_date) keeps both the nightly run and single-file replays cheap. > **Interviewers Watch For** > > The tell of seniority is talking about the corrected file. Anyone can draw three boxes labeled Bronze/Silver/Gold. The candidate who says 'I partition by distributor and date, write with overwrite on that key, so a resent file for last Tuesday replaces exactly that slice and BI never double-counts' has actually operated one of these. Also watch for a completeness check (did all 900 land) versus only row-level validation. > **Common Pitfall** > > Writing files straight into the Gold table with an append. It works until the first correction, when a resent file appends a second copy of Tuesday and every sum is now wrong, with no immutable raw to rebuild from. The second most common miss: letting one malformed file abort the whole batch instead of quarantining the bad rows and shipping the other 899 distributors on time. --- ## Common follow-up questions - A distributor sends a corrected file for a date three weeks ago, after Gold has already been consumed by demand planning. How does your design absorb it? _(Tests whether Bronze immutability plus partition-overwrite genuinely supports out-of-order, historical reprocessing without a full rebuild, and whether downstream consumers are notified of the restatement.)_ - Volume grows 10x as you onboard a new region and files start arriving in the middle of the batch window. What changes? _(Tests scaling the batch (partition parallelism, incremental conform) and whether the SLA design degrades gracefully or needs a shift toward micro-batch triggered on file arrival.)_ ## Related - [All practice problems](https://datadriven.io/problems) - [Mock interview mode](https://datadriven.io/interview/distributor-files-to-gold) - [System Design Interview Questions](https://datadriven.io/data-engineering-system-design) - [Data Engineering Interview Prep Guide](https://datadriven.io/data-engineer-interview-prep) - [Daily Challenge](https://datadriven.io/daily) --- Source: DataDriven (https://datadriven.io). 100% free data engineering interview prep. Live code execution against Postgres 16, Python 3.11, and Spark sandboxes. No paywall, no premium tier, no signup gate.