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

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

A daily sensor gates the run and alerts on a late file, raw JSON is landed for replay, a quality gate catches drift, and an upsert on order id lands the slice in a warehouse that absorbs new fields.
  • 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