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
Each morning a single JSON export of the previous day's orders from a live-shopping marketplace lands in cloud storage, roughly 4 GB and 20 million records, and it has to be queryable in the warehouse before the daily standup. The file sometimes arrives late, occasionally gets re-delivered under the same name, and gains new fields as product teams extend it, so a plain daily reload either double-counts orders or breaks on the new keys. Design the pipeline that lands this file in the warehouse reliably each day.
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. Three separate failure modes, all 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 (it would wipe history) and rules out a blind append (it 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 at 6am 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 to hit 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. This 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, while the load tolerates new optional fields (store the raw record in a variant column, or enable schema evolution on the merge) so a harmless new field does not fail the whole day.
The shape that fits
- daily_json_drop (source, S3 (4 GB/day, 20M orders))
- orchestrator (transform, Airflow file sensor + daily schedule)
- raw_landing (storage, S3 raw zone)
- validator (quality gate, Great Expectations)
- merge_loader (transform, dbt/Spark merge on order_id)
- orders_warehouse (storage, Snowflake, partitioned by order_date)
- analyst_dashboard (consumer, Tableau)
- daily_json_drop -> orchestrator
- orchestrator -> raw_landing
- raw_landing -> validator
- validator -> merge_loader
- merge_loader -> orders_warehouse
- orchestrator -> orders_warehouse
- orders_warehouse -> analyst_dashboard
- Sense + land: daily_json_drop, orchestrator, raw_landing
- Validate + merge: validator, merge_loader
- 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: partition pruning, splitting the load by region, or micro-batching within the day, rather than just 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 consider tracking change history versus overwriting for finance's audit needs.)