ETL / Ingest Pipeline
Build an extract-transform-load pipeline that ingests source data, reshapes it to a target schema, and loads it with validation at every stage. Locks the schemas FIRST — source shape, target shape, the field-by-field transform/mapping, type coercions, and the data-quality rules — then implements extract→transform→load with row-level error capture, then a reviewer validates output conformance, row counts, and reject handling. Covers ETL stages, schema mapping, type coercion, data validation, idempotent loads, dead-letter/reject rows, and reconciliation counts.
Steps
Entry step: schema-scope. Each step names the specialist role it wants; the full working prompt is expandable.
- Schema scopeplannerentry
lock source/target schemas, mapping, quality rules
Show working prompt
Lock the schemas and mapping before writing pipeline code. Step 1: Document the SOURCE schema — fields, types, and any messiness (nulls, formats, encodings). Step 2: Document the TARGET schema the data must conform to. Step 3: Write the field-by-field transform map: which source field(s) feed each target field, the coercion/cleaning applied (trim, parse date, cast number), and the default for missing values. Step 4: Define data-quality rules and reject criteria — which rows are invalid and what happens to them (drop, dead-letter, fail run). Step 5: Write a numbered acceptance-criteria checklist of 5-9 items (e.g. 'every loaded row conforms to the target schema', 'invalid rows go to the reject sink and are counted', 'input count = loaded + rejected', 're-running does not duplicate rows'). `write_task_note` the schemas + mapping + checklist and write the same to the produces path.
- Build the pipelinedeveloper
implement extract → transform → load
Show working prompt
Implement the pipeline as three clear stages in a runnable script. Step 1 (extract): read the source data into records. Step 2 (transform): apply the locked mapping field-by-field with the specified coercions and defaults; validate each row against the quality rules. Step 3 (load): write conforming rows to the target and route failing rows to the reject sink, keeping running counts (read / loaded / rejected). Step 4: make the load idempotent (upsert or skip-seen) so re-runs do not duplicate. Emit a reconciliation summary at the end. Use sample/in-memory sources and sinks if none specified. On a loop-back, fix only the named gaps. `write_task_note` the file path and which criteria now pass.
- Validate the outputreviewer
check conformance, counts, and reject handling
Show working prompt
Validate the pipeline's output against the schema and the quality rules. Step 1: Run the pipeline on representative input including deliberately bad rows. Step 2: Confirm every loaded row conforms to the target schema exactly (fields present, types correct). Step 3: Confirm the reconciliation holds: input count equals loaded plus rejected, and rejected rows are the ones that violate the rules. Step 4: Re-run and confirm no duplicate rows appear. Write a short validation note with the counts and any conformance gaps to the produces path.
- Evaluatereviewer
Grade the deliverable against every acceptance criterion. All pass → finish; any fail → loop back and fix the gap.
Show working prompt
Run the pipeline on input that includes valid and invalid rows. For EACH acceptance criterion: confirm every loaded row conforms to the target schema, confirm invalid rows are rejected and counted, confirm input = loaded + rejected, and confirm a re-run produces no duplicates. Write PASS/FAIL per criterion with the reconciliation counts. Then route — this is the whole point of the loop: - **Every criterion PASSES →** call `advance_task_step({ ref, stepId: "evaluate", next: "finish" })`. - **Any criterion FAILS →** write the specific gaps to notes, then call `advance_task_step({ ref, stepId: "evaluate", next: "build" })` to loop back. The builder fixes exactly those gaps. Never route to `finish` while any criterion is unmet. The build phase's completion gate already blocked a grossly-incomplete deliverable; your job is the judgment an automated check cannot make (does it actually work, read well, look right). After ~3 unproductive loops, stop and report DONE_WITH_CONCERNS so the user can step in. - Finishdeveloper
All acceptance criteria met. Stamp a short summary and report DONE.
Show working prompt
Every acceptance criterion passed. Write a one-paragraph DONE summary to task notes via `write_task_note`: what was built, the deliverable path(s), and a one-line confirmation that each criterion is met. Then report DONE.
Triggers
Phrases that suggest this craftbook to a crew.
- build an etl pipeline
- ingest and transform data
- data pipeline
- load data into a target schema
- etl job
Source
View this craftbook on GitHub · MIT license