ラボ / 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.
これが証明すること
面接で守れる一言。
履歴書の一言
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
概要
ステップごとに作るもの。
- 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.
証明
これらが合格したら完了。
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
技術スタック
Pythonpandas or plain SQLPostgreSQL or SQLite as the destination
Sage Method
frame → map → decide → prove
あなたが得るもの
A re-runnable ETL pipeline (extract/transform/upsert-load) + a passing re-run and crash-resume test suite