4,500 Stores Before Sunrise
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
- L5
- Asked in
- Walmart
Problem
Every night, 4,500 stores each upload a CSV of current inventory to S3. The replenishment team needs clean, validated data in the warehouse by 7 AM. Some files arrive late, some are malformed, and re-runs have been producing duplicates. Design the pipeline.
The shelves open at 7. The data better be there.
Business requirements
- Replenishment opens dashboards at 7am every day; clean validated inventory across all stores has to be in the warehouse by then.
- The pipeline has been re-run multiple times this month and each rerun has been creating duplicates in the warehouse.
- A few stores send malformed or oversized files; one bad file can't take down the load for the other 4,500.
Worked solution and explanation
Why this problem exists in real interviews
Many small files, a hard morning deadline, reruns that have been producing duplicates, and bad files that have been taking the whole load down. Each property has a clean answer; the trap is fitting them together so the orchestrator owns the deadline, the writes are idempotent on a deterministic key, and the bad files quarantine without halting the rest.
The default reach is one job that lists the S3 prefix at midnight, parses every file, and appends to a warehouse table. The first malformed file crashes the parser and the load halts; the on-call engineer reruns and the rows from the first run get appended again, doubling inventory for the stores that succeeded the first time. Late files arriving after midnight miss the load entirely until the next night.
Walk the requirements
Step 1: Land validated inventory by 7am with per-stage alerting
An orchestrator runs the nightly DAG: file-arrival sensors watch the S3 prefix, validation runs as files land, the warehouse load runs once enough files have arrived (with a deadline-driven cutoff for late files), and the publish step runs ahead of 7am. Each stage has its own SLA and its own alert; on-call sees a delayed stage with hours to act, not minutes after replenishment opens an empty dashboard. Without orchestration there's nothing watching the deadline.
Step 2: Reruns don't duplicate, by construction
The warehouse load writes via partition-overwrite keyed on (load_date, store_id). A rerun for the same night replaces those store-rows in the partition rather than appending. Whatever stage failed in the prior run, restarting the DAG produces the same warehouse state. An append-style load is what's been creating the duplicates this month; the partition-overwrite key is the contract that stops it.
Step 3: One bad file doesn't take down the load
Validation runs per file: schema check, size check, parse check. Files that fail route to a quarantine location with the rejection reason; the rest continue to the warehouse load. The orchestrator alerts when quarantine count exceeds an expected baseline so somebody notices a systemic issue (a region of stores all sending malformed files at once) without alerting on the steady drip of one or two bad files. A 'fail the entire load on the first bad file' design is what's been taking the load down; per-file validation with quarantine isolates the failure.
The shape that fits
- store_uploads (source, S3)
- orchestrator (transform, Airflow); on error: alert; alerting: Stage at risk vs 7am SLA or quarantine count above baseline
- file_validator (transform, Spark); on error: dlq
- quarantine_zone (storage, S3); alerting: Quarantine count above expected baseline
- staged_lake (storage, S3); backfill: partition_overwrite
- warehouse_loader (transform, Spark); backfill: partition_overwrite; idempotency: staging_table
- inventory_warehouse (storage, Snowflake); freshness: < 24h; backfill: partition_overwrite
- replenishment_team (consumer, Tableau); freshness: < 24h
- store_uploads -> orchestrator
- orchestrator -> file_validator
- file_validator -> quarantine_zone
- file_validator -> staged_lake
- staged_lake -> warehouse_loader
- warehouse_loader -> inventory_warehouse
- inventory_warehouse -> replenishment_team
- Source + orchestration: store_uploads, orchestrator
- Validation + stage: file_validator, quarantine_zone, staged_lake
- Warehouse + consumer: warehouse_loader, inventory_warehouse, replenishment_team
Common follow-up questions
- A handful of stores routinely upload at 6:50am, just before the deadline. What in this design lets their data still land by 7am, and what doesn't? (Tests whether the candidate sees the file-arrival sensor as continuous: late files validate and load as they arrive, with the orchestrator's deadline cutoff defining the published view. If the deadline cutoff is strict, those stores don't make 7am; if the cutoff is flexible, they may. The candidate should name the trade.)
- A store sends a corrected file the next day for the prior night's data. How does this design replace the prior night's row for that store? (Tests whether the candidate sees that the partition-overwrite key is (`load_date`, `store_id`), so loading the corrected file under `last_night`'s `load_date` for that store replaces the affected row. The other stores' rows for that night don't change.)