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 their current inventory to S3, and some of those files land late. Design the pipeline that takes those uploads into the warehouse the replenishment team works from.
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
This is a rerun-safety problem wearing a 7am deadline. Anyone can draw files flowing into a warehouse. What separates candidates is whether a 5am rerun replaces last night's rows or doubles them, and whether one corrupt CSV out of 4,500 stops the other 4,499. Get it wrong and replenishment orders against inflated inventory for exactly the stores that loaded before the crash.
Walk the requirements
Step 1: Give nightly_load_orchestrator the deadline
Nothing else in the design owns the clock. Airflow sensors watch the upload prefix, so late files join the same night's run. A hard cutoff before 7am decides what gets published, and retries cover transient failures. Without this stage, a late store is invisible until someone opens an empty dashboard.
Step 2: Validate per file and branch rejects off the main path
file_validator checks schema, size and parse for each file on its own. A failure takes an error path to data_quality_exceptions with the rejection reason, and the good files keep moving. Failing the whole load on the first bad file is what has been taking the load down.
Step 3: Stage first, then overwrite by key
Validated files land in validated_store_inventory, and only then does warehouse_loader write store_inventory_current, replacing rows by (load_date, store_id). A rerun reads the same staged files and overwrites the same partition. Appending raw files straight into the warehouse is where this month's duplicates came from.
The shape that fits
- raw_store_inventory_files (source, S3)
- nightly_load_orchestrator (transform, Airflow)
- file_validator (quality gate, custom)
- data_quality_exceptions (storage)
- validated_store_inventory (storage)
- warehouse_loader (transform, SQL); idempotency: staging_table
- store_inventory_current (storage, warehouse)
- replenishment_dashboard (consumer, dashboard)
- raw_store_inventory_files -> file_validator
- nightly_load_orchestrator -> file_validator
- nightly_load_orchestrator -> warehouse_loader
- file_validator -> data_quality_exceptions
- file_validator -> validated_store_inventory
- validated_store_inventory -> warehouse_loader
- warehouse_loader -> store_inventory_current
- store_inventory_current -> replenishment_dashboard
- One nightly job
- Lists the prefix, parses everything and appends. One malformed file halts it, the rerun re-appends the stores that already loaded, and files after midnight wait a day.
- Staged and keyed
- Validates per file, quarantines rejects, stages clean data and overwrites by (
load_date,store_id). A rerun is a no-op on stores that already succeeded.
Common follow-up questions
- A few stores routinely upload at 6:50am. What lets them land by 7am, and what doesn't? (Tests the sensor plus cutoff trade: late files join the run, but a strict cutoff means some stores miss that morning's publish.)
- A store resends a corrected file for last night. How does it replace the bad rows? (Tests that the (`load_date`, `store_id`) key makes a reload replace only that store's rows.)