etl-compiler

Spec-compiled ETL: profile the input, record every decision in an auditable spec, compile to deterministic Python. Bad rows are quarantined and error-coded, never silently coerced.

etl-compiler generates correct, conformant data-transformation code by walking a failure-mode taxonomy instead of improvising: weird characters (unicode, escapes, BOMs, mojibake), nulls and their many disguises (empty strings, whitespace, N/A, 9999), ambiguous dates, silent truncation, and error handling that actually reports — per-record, per-error-type, and per-run.

The problem with how ETL gets built today

Bad data is the norm, not the exception: ~26% of open-government CSVs fail a naive parse, ~20% of CSVs on GitHub use a non-standard dialect, and a single tool silently autocorrupting gene names left errors in ~30% of scanned genomics papers. Yet the tools that ingest this data each fail differently, and mostly silently — verified by direct testing and a primary-sourced census of 15 shipping tools (maintained in the project's evidence corpus; frequency numbers above from Mitlöhner et al. 2016, van den Burg et al. 2019, and Abeysooriya et al. 2021):

• Dataframe parsers silently coerce. Put one bad value in a numeric column and pandas turns the whole column to strings; DuckDB and pyarrow turn a 20-digit ID into 1e+32 — precision gone, no error, no warning. The corruption ships downstream wearing a clean face.
• Warehouse loaders abort the whole batch. Snowflake (ABORT_STATEMENT) and BigQuery (maxBadRecords=0) default to failing the entire load on the first bad row — one dirty cell in ten million rows stops the pipeline.
• Modern ELT hides the damage. Airbyte and Fivetran keep the row and null the bad field, so it "loads fine" — the failure is invisible until someone downstream trusts a NULL that was actually a parse error. (Airbyte's protocol cannot even represent quarantining a bad row.)
• One-shot LLM codegen improvises. Ask any chat model for "a script to convert this file" and you get plausible handling — different every time, with no coherent error-reporting architecture and no record of which edge cases it decided to ignore.

None of these preserves the bad row, tells you what happened, and keeps going.

What etl-compiler does instead

The founding observation: ETL fails in recurring, enumerable ways — so the whole class can be handled systematically instead of improvised per pipeline.

• Surfaces every decision, corrupts nothing. Bad rows are quarantined with their raw form preserved (reprocessable), never silently coerced, dropped, or padded. Every auto-fix is counted, never silent.
• Never guesses meaning-changing ambiguity. MDY-vs-DMY dates, decimal locale, N/A-vs-9999 sentinels — the taxonomy's ask class always asks; in unattended mode it quarantines rather than reinterprets. (A wrong BOM strip is annoying; a wrong date format silently corrupts every row.)
• Reports at three granularities, with stable codes. Per-row error records, per-error-type aggregates, and a run summary + manifest — every error code is a taxonomy ID (TYP-03, STR-02), so a quarantined row traces straight to the documented decision that governs it. Aggregate them across pipelines; they don't drift.
• Same spec → same bytes. Decisions live in an auditable .etlspec.yaml, edge-case semantics live in one shared, tested runtime, and a deterministic compiler turns the spec into the pipeline with no model in the loop — identical spec bytes produce a byte-identical pipeline, so a regenerated pipeline is reviewable as a spec diff. One-shot generation cannot offer this; consistency is the whole point.

How it works

sample input ──▶ PROFILER ──▶ findings (taxonomy IDs + evidence)
                                 │
                                 ▼
                        INTERVIEW (only about what was detected;
                         meaning-changing ambiguity is never guessed)
                                 │
                                 ▼
                        SPEC (.etlspec.yaml — every decision, with provenance)
                                 │
                                 ▼
                        COMPILER (deterministic, no model in the loop)
                                 ──▶ thin pipeline.py  +  etl_runtime.py (shared, tested)
                                 │
                                 ▼
                        VERIFIED RUN ──▶ output.csv + quarantine.csv
                                          + errors.jsonl + summary.json + manifest.json

Three properties make the output trustworthy: the spec is a complete, auditable record of every edge-case decision (including applied defaults); the runtime implements each taxonomy entry exactly once, so every pipeline handles nulls/encodings/dates identically; and error codes are taxonomy IDs, so a quarantined row marked TYP-03 traces straight to the documented decision that governs it.

Built to a high bar

• Strict types, enforced — not aspirational. Every product module (runtime, compiler, profiler) passes mypy --strict, and the type check runs as part of the test suite, so it cannot drift. The spec, pipeline config, profiler findings, and every report record are typed contracts (TypedDicts), not loose dicts — the compiler's emitted config is type-checked against the runtime's contract end-to-end.
• An (almost-)pure core. The runtime is split on the effect boundary: etl_coercers is deterministic with exactly one sanctioned effect — counting auto-fixes into an explicitly-passed report accumulator (that counting is the product's accounting guarantee). All file I/O and the single wall-clock read live in a thin driver, etl_runtime.
• Everything is gated. 130+ unit tests, byte-determinism checks on the compiler, a 244-file real-world corpus audit for the profiler, and the type gate — a regression in behavior, determinism, or type-safety fails the suite.

Try it in 30 seconds

# 1. Profile a genuinely filthy vendor export (BOM, mojibake, NFD unicode, control
#    chars, ambiguous MDY dates, $-and-parens numerics, ragged/duplicate/footer rows):
python3 skill/etl-generator/scripts/profile.py evals/inputs/orders_export.csv

# 2. Compile a recorded spec into a pipeline — deterministically, no model involved —
#    and run it against its sample:
cp skill/etl-generator/assets/etl_runtime.py .
python3 skill/etl-generator/scripts/compile_spec.py evals/inputs/vendor_orders.etlspec.yaml -o vendor_orders_pipeline.py
python3 vendor_orders_pipeline.py evals/inputs/vendor_orders_sample.csv --out-dir etl_out
# → exit 0; inspect etl_out/ (output.csv, summary.json, manifest.json)

Everything is stdlib-only Python. No dependencies.

Using the skill

skill/etl-generator/ is an Agent Skill conforming to the Agent Skills open standard — it runs in any agent harness that supports the standard (Claude Code, Claude, Cursor, Gemini CLI, Goose, Codex, OpenCode, and many more), not just one product. It's plain Python (standard library only) and hardcodes no client-specific tools; the SKILL.md frontmatter declares its requirements via the standard's compatibility field.

Drop the skill/etl-generator/ folder into your harness's skills directory (or zip it), then ask the agent to transform any tabular file — it will profile, interview you only about real detected issues, write the spec, generate the pipeline, and run it to prove it works. It also regenerates pipelines from an existing .etlspec.yaml without re-interviewing.

Repo guide

Path	What
skill/etl-generator/	The product: the skill (Agent Skills standard) — workflow, references, profiler, deterministic compiler, runtime
docs/taxonomy.md	The founding artifact: ~40 failure modes, decision spaces, defaults (v0.2)
tests/	Runtime + profiler + compiler unit suite (python3 -m unittest discover -s tests)
evals/	Eval inputs (also test fixtures) + current benchmark results (evals/iteration-2/benchmark.md)
AGENTS.md	Contributor/agent handoff: decisions, conventions, state, next steps

Development artifacts (validation corpus, historical eval iterations, design logs, detailed reports, runnable examples) live on the maintainer's machine and are not distributed — see the ignore rules in .gitignore.

Status

v0.2 — taxonomy, profiler, and runtime hardened and validated against reality. Everything ships with a real unit-test suite; the taxonomy was validated against a 244-file corpus of genuinely messy real-world data plus a graded, primary-sourced census of 15 shipping ETL tools (the competitive claims above come from it). Six profiler detection bugs found on real portal files were fixed; a new failure mode (magnitude-suffixed numerics) was added end-to-end. The one load-bearing default question — quarantine-vs-fail-loud — was settled by a dedicated adversarial evidence pass (verdict: keep quarantine).

Eval iteration 2 (contract-level assertions, isolated baselines) found that a strong model satisfies the contract unaided on single runs — so the skill's real value is determinism, stable taxonomy codes, and a tested shared runtime, which single-run scoring can't capture. That conclusion is now built and measured: the deterministic spec → pipeline.py compiler ships in the skill, and eval iteration 3 — the determinism benchmark (evals/iteration-3/benchmark.md) — is the first where the arms separate: skill 4/4 determinism properties held, baseline 0/4 (byte-identical regeneration ×3; decision-identical authoring ×3 vs disposition roulette; 4/4 exact codes on unseen problems vs misassigned ones; 1-line edit blast radius with the error budget catching a wrong edit loudly).