# Six Million Rows Before the Market Opens > One massive CSV. Millions of timestamps. Canonical URL: Domain: Pipeline Design · Difficulty: medium · Seniority: L5 ## Problem A third-party vendor drops a dirty CSV of 6 million time series price rows over SFTP each morning, and the quant team's pre-market models read it from the warehouse. Design the ingestion architecture from the vendor drop through to the quant query surface. ## Business requirements - The vendor file lands at 6am and quant pre-market models run at 8am; data has to be queryable by 7:30 with a buffer, and a run that is failing or running late has to raise an alert in time to react. - Duplicate rows are copies of a real tick and only need collapsing to one somewhere before the warehouse, but rows with null prices or malformed timestamps are unusable; they can't enter the warehouse where they would corrupt model output, so they must be split off to a separate location. - If the morning load is rerun on the same file, the warehouse must end up with the same rows, so the warehouse is fed from staged data and the writes on the way are safe to repeat rather than appending copies. ## Worked solution and explanation This is a rerun-safe batch load wearing a deadline costume. Anyone can draw vendor file, validate, warehouse. What separates candidates is the write into the warehouse: it has to come from staged data and be safe to repeat, and the alert has to fire on a late run, not only a failed one. Miss the first and a rerun doubles the quants' rows before the 8am models start. Miss the second and a slow run looks green until the models read stale data. > **Stage first, then replace the day** > > Once the file sits in a `landing zone` and the clean rows sit in a `clean zone`, a rerun re-reads the same bytes and overwrites the `load_date` partition. Loading straight from the raw file leaves nothing to replay from, so the load can never be made idempotent. ### Walking the design #### Step 1: Land the file untouched The vendor's SFTP drop is copied into a `landing zone` on object storage, and the orchestrator schedules and tracks that pull. Raw bytes stay put, so a rerun never depends on the vendor resending, which they will not do. #### Step 2: Collapse duplicates, keep one copy An exact duplicate is the same tick sent twice, not a broken row. `dedup rows` keeps one copy and passes it on. Rejecting duplicates would throw away real prices and leave holes in the day the quants model. #### Step 3: Split unusable rows at the gate The `row validator` checks null prices and malformed timestamps. Valid rows continue to the `clean zone`. Unusable rows leave on an error path to `quarantine`, so the warehouse never sees them and the vendor team can still review them. #### Step 4: Make the warehouse write safe to repeat The `warehouse load` reads only the `clean zone` and overwrites the day's `load_date` partition with a delete-then-write. Run it once or three times and the day looks the same. An upsert keyed on the tick works too; a plain append never does. #### Step 5: Alert on lateness, not just failure The orchestrator owns the schedule and has an error path to `run alerts`. A task failure and a missed checkpoint both page. If the load has not finished by 7:00, there is still time to rerun before the 7:30 deadline. **Pipeline stages** - vendor file (source) - download scheduler (transform, Airflow); alerting: page if not loaded by 7:00 - landing zone (storage, object storage) - dedup rows (transform, Spark) - row validator (quality gate, Great Expectations) - quarantine (storage, object storage) - clean zone (storage, object storage) - warehouse load (transform, SQL); idempotency: delete_write - price warehouse (storage, warehouse) - quants (consumer, Jupyter) - run alerts (consumer) **Data flow** - vendor file -> landing zone - download scheduler -> landing zone - landing zone -> dedup rows - dedup rows -> row validator - row validator -> clean zone - row validator -> quarantine - clean zone -> warehouse load - download scheduler -> warehouse load - warehouse load -> price warehouse - price warehouse -> quants - download scheduler -> run alerts **Layers** - Ingest and schedule: vendor file, download scheduler, landing zone - Validate and stage: dedup rows, row validator, quarantine, clean zone - Load and serve: warehouse load, price warehouse, quants, run alerts *Duplicates collapse to one copy, unusable rows leave on an error path, and the `clean zone` feeds a load that replaces the day by `load_date`. The scheduler pages `run alerts` on failure or a missed 7:00 checkpoint.* **Append from the raw file** The raw file goes straight into the warehouse. A rerun inserts the same 6 million rows again, and the only fix is a hand-written delete that someone must remember at 7am. **Replace the day from staged data** The `clean zone` feeds a load that overwrites the `load_date` partition. A rerun leaves the result unchanged, so the on-call can restart without thinking. > **A slow run raises no error** > > Candidates wire an alert to task failure only. A stuck SFTP pull or a validator that crawls never fails, it just finishes at 8:10. The deadline needs its own checkpoint alert, not only a failure hook. > **Six million rows is not the hard part** > > That is about a gigabyte of CSV, which a warehouse bulk load handles in minutes. The 90-minute window is eaten by a late vendor and by reruns, not by compute. Spend your design effort on the replay path, not on scaling. ## Common follow-up questions - The vendor restates last Tuesday's file. What does this design do? _(Tests whether the candidate replays that day's `load_date` partition from a corrected landing file without touching other days.)_ - The quarantine count is ten times its usual size. Should the load still publish? _(Tests for a threshold on the validator output that holds the publish and pages, instead of silently loading a partial day.)_ ## Related - [All practice problems](https://datadriven.io/problems) - [Mock interview mode](https://datadriven.io/interview/six_million_rows_before_the_market_opens) - [System Design Interview Questions](https://datadriven.io/data-engineering-system-design) - [Data Engineering Interview Prep Guide](https://datadriven.io/data-engineer-interview-prep) - [Daily Challenge](https://datadriven.io/daily) --- Source: DataDriven (https://datadriven.io). DataDriven is the data engineering interview community. Live code execution in SQL, Python, and Spark sandboxes. Every feature is open to every member.