The Morning File
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
- Whatnot
Problem
A single JSON file of the previous day's orders from a live-shopping marketplace, about 4 GB and 20 million records, lands in cloud storage each morning. Design the pipeline that makes each day's file queryable in the warehouse before the daily standup.
Business requirements
- Finance keeps flagging days where our order count is inflated. If the same file lands twice, our numbers cannot move.
- The dashboard has to be right by 9am, and when the file is late I want to know before the analysts do.
- Product teams keep adding fields to the export without telling us, and it keeps breaking the load.
- When a load goes wrong I do not want to beg the vendor to resend; I want to re-run it myself.
Worked solution and explanation
Why this problem exists in real interviews
This is an idempotency problem wearing a file-ingestion costume. The skill being probed: can you load the same day's file twice, or a late re-delivery under the same name, without moving a single number? Anyone can COPY a 4 GB JSON file into a warehouse table. The trap is the blind daily append: the morning the vendor re-sends the file after a fix, or the orchestrator retries a half-finished run, your order count silently doubles and finance's revenue number is wrong before standup.
The whiteboard answer is a daily job that reads the JSON and inserts it into an orders table. It works until the day the file is re-delivered, or the file lands two hours late and the job already ran against nothing, or a product team adds a field and the strict loader throws. Those are 3 separate failure modes, and each stays invisible until an analyst notices the dashboard disagrees with reality.
Break down the requirements
Step 1: Decide full vs incremental before anything else
Each file is one day's orders, not a full snapshot, so the target table is built by accumulating daily slices. That single fact rules out a full reload, which would wipe history, and it rules out a blind append, which double-counts on re-delivery. The answer is an incremental merge keyed on order id: today's slice is upserted into the accumulated table, and re-running the same day changes nothing.
Step 2: Make lateness a signal, not a surprise
The file usually lands early in the morning but sometimes late. A daily-scheduled orchestrator with a file-arrival sensor waits for the file rather than running against an empty prefix, and a deadline alert fires if the file has not arrived in time for the 9am standup. Without the sensor, a late file produces a silently stale table that looks fine until someone checks.
Step 3: Land raw, then transform, so you can replay
Write the untouched JSON to a durable landing zone before any parsing. When a load is wrong (a schema break, a bad merge, a partial run) you re-run from the landing zone instead of asking the vendor to resend. The landing zone is also what makes an orchestrator retry safe: the raw bytes are already durable and the merge is idempotent.
Step 4: Absorb additive schema drift at the gate
Fields get added, never renamed or dropped. A validation gate checks the required fields and types and fails loudly on a real break. The merge into a table format that supports schema evolution absorbs new optional fields, so a harmless new key does not fail the whole day.
The shape that fits
- daily_json_drop (source, object storage)
- file_arrival_sensor (transform, Airflow); alerting: alert if file not landed by 7am
- raw_landing (storage, object storage)
- schema_validation (quality gate, Great Expectations)
- idempotent_merge_load (transform, Spark); idempotency: upsert
- orders_warehouse (storage, Iceberg)
- analyst_dashboard (consumer)
- daily_json_drop -> file_arrival_sensor
- file_arrival_sensor -> raw_landing
- raw_landing -> schema_validation
- schema_validation -> idempotent_merge_load
- idempotent_merge_load -> orders_warehouse
- orders_warehouse -> analyst_dashboard
- Sense + land: daily_json_drop, file_arrival_sensor, raw_landing
- Validate + merge: schema_validation, idempotent_merge_load
- Serve: orders_warehouse, analyst_dashboard
Common follow-up questions
- The file volume grows 20x after the company adds international orders, and a single daily merge starts missing the 9am deadline. What changes? (Tests whether the candidate scales the merge through partition pruning, splitting the load by region, or micro-batching within the day, rather than just buying a bigger warehouse.)
- The vendor starts occasionally sending a corrected file that changes the amount on orders you already loaded yesterday. How does the design handle a mutating record? (Tests whether the candidate sees that upsert-on-key already updates in place, and whether they weigh tracking change history against overwriting for finance's audit needs.)