Robust data pipelines with AI

A pipeline that runs and a pipeline that is correct are two different things.

Mehdi Ouazza · MotherDuck · mehdio.com

Green
≠ correct

Why AI pipelines break

AI is non-deterministic

ask twice, get two implementations
  • each attempt makes different choices
  • “it runs” doesn't tell you which one is correct
  • you can't review your way out at generation speed

AI can't see your data

so it guesses
  • guesses your schema, your units, what counts as a duplicate
  • guesses are silent bugs
  • no error — just plausible, wrong numbers

The one-shot prompt

no MCP · no context · just vibes
# paste to a coding agent Load NOAA GHCN daily max temperatures for US stations from the public S3 bucket (parquet) into MotherDuck, then tell me the average and hottest max temperature. s3://noaa-ghcn-pds/parquet/by_year/
ClaudeMotherDuck

The pipeline is green · here's what it says

179.6°avg US max temp
807.8°hottest reading

Roughly the temperature of hell.

Three silent bugs

×Hardcoded YEAR=2024 + CREATE OR REPLACE — wipes the table on every runno backfill
×GHCN stores temperature in tenths of a degree — 151 means 15.1 °C, nobody divided by 10×10 too hot
×The source flags bad readings with Q_FLAG — the pipeline ingests them anywaygarbage in
✓Pipeline statusgreen

01

Foundations
still matter

The old discipline didn't disappear — it matters more at generation speed

A
Parameters
inject the year(s) — load or reload any partition, not a hardcoded snapshot
B
Idempotent
run it twice, same result — no duplicates, no drift
C
Incremental
load slices, not a full snapshot every time
D
Inspect first
look at the data before writing any transform — spot the gotchas

02

Give your AI eyes
on your data

Any MCP for your database works — here, the MotherDuck MCP

coding agent
“inspect the data”
queries
MCP
read · describe · sample
reads
your data
S3 parquet · warehouse
✓Found it: values are in tenths of a degree — divide by 10
✓Found it: layout is partitioned by year and element
✓Found it: Q_FLAG marks failed quality checks at the source

Eyes on the data + fundamentals in the prompt → the robust version

✓YEARS is a parameter — backfill or reprocess any yearflexible
✓DATA_VALUE / 10.0 — degrees, not tenthscorrect units
✓Drop rows where Q_FLAG is set — the source already told you they're badQA'd
✓Dedupe on station + date, delete the year's rows then insertidempotent

And now it can write tests that mean something

<1sfull test suite · in-process DuckDB · zero network
✓Because it saw real data, the fixtures look like real data: a tenths value, an exact duplicate, a QC-failed row
✓An engine that runs locally and in CI keeps the agent loop fast — no cloud round-trips to find out you broke it

03 · the hardest one

Make the goal
a contract,
not a prompt

Judge the output against a goal you set up front — not the code

the contract · written first
# Target: main.us_tmax_daily — QA-passed daily max temperature # One row = one station, one date. Values in °C (source is tenths). # Drop rows where Q_FLAG is set. Idempotent per year. # KPI rule — the part a prompt would never guess: Same-station mean TMAX per year. To measure a trend, hold the station set fixed. # new stations were added in # colder places — averaging them all biases the trend.

Contract → assertion · write, audit, publish

agent
writes the pipeline
deploy
Flight
remote python compute
writestaging
Audit
non-empty · −90…60 °C
no null keys
passpublish
prod table

Always fail loudly.

Why the contract matters · US mean TMAX · 1974 → 2024

−0.3°all stations · looks like cooling
+1.1°same 2,652 stations · warming

Same data, opposite conclusions.

Bonus · a chart spots weirdness faster than rows

fixed station set · +1.1°C all stations · drifts down mean TMAX °C 1974 2024
the real trend, per contract the naive average

04

Package it

All those rules, in one markdown file — so it happens every time

SKILL.md · robust-flight
name: robust-flight description: Build a data pipeline that's correct, not just green. rules: - get eyes on the data before writing any transform - parameters + idempotency + incremental loads - write the contract first; audit before publish - local unit tests on realistic fixtures

github.com/motherduckdb/agent-skills

In short

01
Foundations still apply
parameters, idempotent, incremental — in the prompt
02
Eyes on real data
MCP first, transform second
03
Contract, not prompt
judge outputs against a goal — fail loudly
04
Package it
a skill file makes it happen every time

The final prompt · one paragraph, four disciplines

the robust prompt
Build a MotherDuck Flight that curates NOAA GHCN daily max temperatures for US stations into md:climate_demo.daily_tmax. Before writing any transform, use the MotherDuck MCP to inspect the source on S3: schema, units, null rates, and duplicate keys. Judge the output against a contract, not the code: one row per station and day, value in Celsius (DATA_VALUE is tenths of a degree), keep only readings that passed QC where Q_FLAG is null, and the KPI is same-station mean TMAX per year, holding the station set fixed so changing coverage does not bias the trend. Take the year(s) as a parameter so I can backfill any year, make it idempotent by deleting the year then inserting so a re-run is a no-op, and read straight from the public S3 parquet with no download. Factor the transform into a pure SQL function and write golden tests that run on local DuckDB (a tenths-of-a-degree value, a QC-failed row, a duplicate), add a Write-Audit-Publish step that refuses to publish if any reading is outside −90 to 60 °C or a key is null, then deploy it, run it, read the logs, and iterate until the audit passes.
eyes on data contract foundations tests & audit

Stay cool. Trust your pipelines.

Thanks.
mehdio.com · the full blog post

I hope it's not 179.6° on your side of the world.