New: Guides, the context layer for AI agentsRead the blog

START FREE
BACK TO VIDEOS
WebinarData PipelinesAI, ML and LLMsMotherDuck Features

Writing robust data pipelines with AI

2026/07/22

TL;DR: AI can write a data pipeline in seconds, but "runs green" isn't the same as "correct." Mehdi Ouazza (DevRel at MotherDuck) walks through the habits that turn a lazy one-shot prompt into a pipeline you can trust, using a NOAA climate dataset with Claude and MotherDuck. There's a companion blog post and the slide deck if you want to follow along.

Why AI pipelines break

AI is non-deterministic and can't see your data. It guesses your schema, units, and duplicates, and those guesses become silent bugs that still produce plausible-looking numbers. In the demo, a NOAA temperature pipeline reports 179-degree averages because the source stores temperature in tenths of a degree and nothing divided by ten.

Give your AI eyes on the data

The single biggest fix is letting the agent read real production data through an MCP server or a CLI. That way it inspects the actual schema, partitions, and quality flags instead of guessing. The MCP workflows docs cover setup for your coding agent.

Foundations still matter

Parametrize by time window. Make loads idempotent with delete-and-insert or merge. Prefer incremental loads over full snapshots. Inspect the data before you transform it. Same data engineering fundamentals as always — you're just prompting for them now instead of writing them by hand.

Judge the output against a contract

Write a contract: the business logic the pipeline has to satisfy. Have the agent iterate against that goal rather than against the code. Let it deploy to a serverless Python runtime like MotherDuck Flights, read the logs when a run fails, and fix itself.

Package it as a skill

Once the pipeline works, capture the whole discipline — eyes on data, foundations, tests, contract — in a reusable skill so you can re-run it with one command instead of re-explaining it every time.

FAQS

"Runs without errors" and "returns the right numbers" are different things. AI is non-deterministic and can't see your data by default, so it guesses your schema, units, and deduplication logic. Those guesses become silent bugs that still produce plausible-looking output. In the webinar's NOAA example, average temperatures came back around 179 degrees — the source stores tenths of a degree and the generated code never divided by ten.

Give it read access to your real data through an MCP server or a CLI. Most database providers (MotherDuck included) ship an MCP server you can connect to your coding agent so it inspects the live schema, partitions, and quality flags instead of guessing from mock data. The important part is that it reads production-like data. Staging and fake data hide the gotchas that cause silent bugs.

Four habits. Parametrize the pipeline by time window so you can backfill and rerun single days. Make loads idempotent with delete-and-insert or merge so reruns don't duplicate data. Prefer incremental loads over a full snapshot every run. And inspect the real data before writing transforms. After that, add unit tests on mock data and a written contract the output has to satisfy.

Usually not. Keep prompts short enough that a person can read them and tell what's going on, and move long, reusable logic into a skill instead. For complex one-off tasks, use plan mode so the agent writes and executes a plan rather than trying to cram everything into one giant prompt. You want prompts you can inspect and skills you can reuse.

A contract is the business logic and intent the pipeline's output has to satisfy, written separately from the code that produces it. You write it down first, then have the agent judge its output against that goal rather than against the code. The code matters less as models improve. In practice you let the agent deploy the pipeline, read the failure logs, and iterate until the output matches the contract.

Related Videos

View all