Die Labs / Data
DataWorking~5h
An ETL pipeline safe to run twice
An ETL pipeline that reads records from a source (CSV/API), transforms them, and loads them into a destination table keyed on a natural key (not an auto-increment id). You make the load step an upsert on that natural key, then prove that running the exact same batch multiple times — including a batch that partially loaded before crashing — leaves the destination table with exactly one row per source record, not one row per run.
Was er beweist
Der Satz, den du im Vorstellungsgespräch verteidigen kannst.
Lebenslauf-Zeile
Built an idempotent ETL pipeline (natural-key upsert, re-run-safe load step) and proved with automated tests that re-running the same batch, or resuming a crashed batch, never duplicates or double-counts rows.
- Understands why append-only loads break under retries and crash-resume scenarios
- Designs the load step around a natural key instead of relying on the pipeline to run exactly once
- Can reason about partial-failure states, not just clean success/failure
Die Aufgabenstellung
Was du Schritt für Schritt baust.
- 01Extract: read a batch of source records from a file or API, each with a natural key (e.g. order_id + line_number).
- 02Transform: normalize/clean fields (types, trimming, derived columns) into the destination row shape.
- 03Load: upsert each transformed row into the destination table by natural key — insert if new, update in place if the key already exists (no plain INSERT-only path).
- 04The whole batch must be safe to re-run from the start at any time, including after a simulated crash partway through the load.
- 05Track a load_run log (run id, row count, started/completed timestamps) separate from the data table, for auditability.
- 06Provide a dedupe check: after N re-runs of the same source batch, row count in the destination equals the number of distinct natural keys in the source.
Der Beweis
Fertig, wenn diese Tests bestehen.
Running the full pipeline 3 times on the same source batch results in the same row count as running it once
automated test
A run that crashes after loading half the batch, when re-run from scratch, produces a destination table identical to a clean single run
automated test
A source record whose values change between runs updates the existing destination row in place rather than inserting a duplicate
automated test
The load_run log records one entry per pipeline invocation, independent of how many rows were upserted
checked output
Stack
Pythonpandas or plain SQLPostgreSQL or SQLite as the destination
Sage Method
frame → map → decide → prove
Du behältst
A re-runnable ETL pipeline (extract/transform/upsert-load) + a passing re-run and crash-resume test suite