Six Million Rows Before the Market Opens

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

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.

One massive CSV. Millions of timestamps.

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.

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.

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.

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.)