Skip to main content

Your Data Pipelines Should Fix Themselves

AIAgentsFlights

· 12 min read

It's 2 a.m. and you just got paged for a broken data pipeline. What's the first thing you do? I know the first thing I would do: copy the error and paste it into Claude. Why are you awake again?

Or maybe your whole team feels the pain all throughout the day: your bugfix backlog is constantly growing. You wouldn’t be alone. Fivetran’s 2026 study found over 50% of data engineering time is spent on maintenance.

Instead, what would it take to let an agent fix something instead of just sending you 5000 alerts about it? What would it look like to have self-healing data pipelines?

Well, most data platforms are brittle. I know I’ve been scared to COMMIT to production in the past. We all have stories where we slowed prod to a crawl or ran up a surprise bill.

Two-buttons meme: a sweating legacy warehouse user choosing between 'Get paged at 2am' and 'Agent runs wild on prod'

For us to realistically put agents to work as members of the data team we need a few things. We need a place to run an agent harness and an agent with visibility across the full data stack.

However, we also need guardrails. We need isolated environments with the same data as prod, just like we need sandboxes for our agent harness. We can’t let costs scale infinitely.

We’ve built a cookbook recipe for self-healing data pipelines on MotherDuck that can use any LLM to repair your pipelines, complete with a full OpenCode agent harness. Point your agent at the recipe and customize it to your workflow.

Here is how we designed it, and why it’s safer to run on MotherDuck than anywhere else.

Oh, and each fix was under 25 cents.

The architecture of self-recovering data pipelines

Animated overview: failed runs are classified into incidents, each incident gets a zero-copy clone and an OpenCode fixer, then an independent review, ship, and a Guide to learn from

The entire pipeline fixing workflow runs inside of a single MotherDuck Flight, our serverless job runner. We’ll call that the “healer” Flight. The data pipelines themselves each run as their own “worker” Flight.

As a first step, the healer checks for any error logs from the workers it has been tasked with watching.

However, we don’t want to kick off one investigation per error, as sometimes one problem can cause a cluster or cascade of other errors (I bet you’ve seen an error alert storm…). To reduce duplicate work and get a better solution faster, the healer will group errors into incidents.

Classify errors into incidents

Animated flowchart of how a failed run is fingerprinted, checked for exact repeats, matched by Jev or an LLM, and routed to a new or existing incident

Incoming errors are classified using Jev, a system one decision model that is faster and less expensive than LLMs because it specializes in these kinds of classification tasks. Errors get fingerprinted to remove run-related id numbers, then Jev compares that generic error with ongoing incidents. If no ongoing incident is a good fit, a new incident is created.

MotherDuck can call out to Jev directly from a SQL command, so you don’t need a TypeSafe AI account.

If Jev is uncertain, an LLM model is used to make the “Is this a new incident?” decision instead. This type of fallback allows us to have low cost decisions in most cases, but use extra intelligence when it will be helpful.

There is also a fallback case for times when many errors all happen at once that bundles those together. This prevents something like a cloud provider outage that causes a variety of issues from spinning up too many investigations at once.

Now the healer kicks off the incident process.

Creating an isolated environment

The declaration of each incident begins with a zero-copy clone of the MotherDuck data used by the worker Flight into its own MotherDuck user. The zero-copy clone gives complete data isolation so that no edits during troubleshooting affect prod. The separate MotherDuck user provides compute isolation so the pipeline can be adjusted and rerun in its own sandbox without slowing down any other production workloads.

This sandboxing makes sure that fixing one issue doesn’t cause any others. It also defers any edits to prod until later in the workflow once things have been validated, or even until a human gives the green light if you’d prefer.

Data guardrail, check. Compute guardrail, check.

Each MotherDuck user is also assigned a fixed amount of compute by virtue of its duckling size. That means that there is a hard cap on the compute cost of any incident investigation.

This healer Flight cookbook also uses an OpenRouter token for all its LLM calls, which can also be assigned spending limits. If you would rather use Anthropic or OpenAI, those tokens would work equally well! OpenRouter just lets us choose models from any of the major frontier labs (including those two).

In our test cases, bugs were identified and solved for under 25 cents apiece.

Cost guardrail, check.

With the debugging playground in place, the healer begins its investigation.

Investigating an incident

Animated timeline of one incident's fix: lease a separate MotherDuck account, zero-copy clone, reproduce, recall Guides, write the prompt, OpenCode fixer, review

Does it still fail?

The first step to solving a problem is understanding it. So, before we even kick off an agent, we re-run the pipeline to replicate the failure. This is where all that isolation immediately pays dividends.

Have I seen this before?

Next, we pull in context from past incidents using MotherDuck Guides. That context could include explanations of what error codes mean, code snippets for debugging, or the fixes that worked in similar situations. By pulling this up front, we can reduce the time and tokens spent solving problems that are similar to what we’ve seen before.

Here is the problem, agent

The error details and past context are arranged into a prompt and sent to an OpenCode agent harness. It has just a few tools available to it so that it can operate safely, but it can still accomplish significant debugging. The agent is able to:

  1. Edit the worker Flight code
  2. Re-run the worker Flight
  3. Run SQL statements
  4. Reference MotherDuck and DuckDB documentation
  5. Edit files in its directory

Having a full agent harness means that the agent can explore and collect more information in addition to making changes and rerunning. This is far from a one-shot attempt! The agent can iteratively work through even complex problems. I picked OpenCode so that any LLM model could be used and so that I could fully customize it. Even though OpenCode is written in TypeScript and Flights start with a Python script, you have permission to install anything you need in a Flight sandbox.

Before executing either SQL or a Flight, there is a verification gate (similar to “auto mode” in other agent harnesses) and arbitrary web request access by the agent directly is disabled. Many Flights ingest data, so it is important to allow the Flight to reach out across the internet, but each Flight run is gated by a validation check.

Network access guardrail, check

Independent review

Animated review gates: healer checks, fresh run, reviewer agent, risk gate, approver and upstream check, with failures sent back to the fixer

In addition to the per-operation validation checks, the overall fix is reviewed by an independent agent. The best practice is to usually use a model from a different frontier lab so that it has a different perspective. In this architecture, there are several checks. If there is a failure at any point, it goes back to the fixing step and tries again to fix the issue.

First, the Flight is re-run in a freshly cloned environment. It is important that these clone operations are fast and computationally inexpensive so we can have this kind of clean end to end test. Then, another OpenCode agent, this time a “reviewer”, is launched using a different model to review the changes. It has a different system prompt and is read only.

Once the changes are assessed to be appropriate, a direct call to an LLM is used to assess the risk of the change and give a final go/no-go. This step plays the role of “approver”. The prompt sent to the approval step does not include justification from the reviewer. Even though modern AI models are increasingly resistant to prompt injection, we still want isolation between those steps.

If other changes have been made by another agent or human in the meantime, code is either merged or sent back to the fixer for conflict resolution.

Risk guardrail, check.

At this point, the fix was developed by one OpenCode agent, reviewed by another OpenCode agent, and approved by a direct call to an LLM that intentionally includes less context. The code is ready to ship or be presented to a human decision maker.

Ship or alert a human

A human review can be another risk mitigation guardrail as well if your domain calls for it. However, I firmly believe that just as more and more code goes un-read, more pipeline fixes will go without human validation as well.

It would be naive to think that you would just switch on auto-fixing without auditing it first though! So having a semi-auto capability is critical especially during initial deployment.

If you enable automatic shipping, a new version of the Flight is pushed to MotherDuck and the next scheduled run picks up that new version in the worker Flight’s MotherDuck user account. Otherwise, an alert can be surfaced in whichever location works best for your company: GitHub, Slack, a ticketing system, or even in a custom Dive App designed for your ideal approval flow.

Even in that semi-auto case, instead of an alert about a problem, you receive a fully vetted solution. Maybe at 2am you could read through the diff on your phone, approve the fix, and head right back to sleep!

Learn for next time

Once a fix has been approved and optionally shipped, the healer flight documents the learnings from the investigation into Guides, the MotherDuck context layer. The next time a similar error occurs, fixing it will be significantly faster. Maybe an upstream table schema changed, and as each downstream job is fired off, they all fail with a missing column error. We want our healer to have a memory so it can be more effective over time.

Ways to take these ideas even further

There are many ways that this cookbook can be made more resilient or more tightly integrated into your team’s workflow.

Instead of directly changing the Flight, changes could route through GitHub first. This is a dependency that you would take on though, and GitHub has had some availability issues of late. If you depend on it for your platform, your data availability is capped at GitHub’s reliability.

If you want to provide live steering to the investigation, perhaps during a human incident response, you could set up a communication channel to send information to the agent as it investigates. For example, you could have a Slack channel that the agent reads between each turn.

Increasingly, code review is the bottleneck in an organization, so spending extra tokens on review and also customizing your human review process to save time can often have a big ROI. This flow doesn't have a dedicated code review step (although changes are encouraged to be minimal) or a dedicated refactoring step. For augmenting manual review, a custom Dive App could bring in other context from your other systems and could simplify the approval step. This is another way you could provide feedback while the agent iterates as well.

This example has a config setting for auto or semi-auto, but you could set up tiering so that your most mission critical pipelines require human approval and all others run on auto. Or maybe the newest pipelines start out as semi-auto until the agent earns the right to push code itself. Moving mature pipelines to auto can help prevent maintenance from scaling out of control as more and more pipelines are created.

This cookbook relies on defined data tests for validation purposes, but it would be beneficial to give the fixer agent more context about downstream consumers of the worker Flight. Since MotherDuck is an integrated platform, you can trace the lineage all the way from sources, through transformations, into visualizations as well.

If you have had the pleasure of maintaining production software for any length of time you know that failures can happen in all kinds of shapes and sizes. It would be good to hammer on this system with additional test cases and document those learnings in Guides. Ideally you could bootstrap that process without having to learn as a trial-by-fire. You could scrape the internet for bug reports and common failures for the libraries you are using or mine your organization’s log archives.

Finally, you could continue to add more validation steps, both via LLMs or deterministic checks. Spending additional tokens to double or triple check is often a great trade to reduce the time spent reviewing and approving manually.

The role of a data person is changing

Increasingly, the role of a data person is to empower an organization to use AI effectively. We see a new role forming: the Data Intelligence Engineer. Traditional data engineering tasks like pipeline creation are automated with agents, as are transformations that used to be handled by analytics engineers. The key objective of a Data Intelligence Engineer is to enable AI powered self-service for anyone building analyses across the company.

Diagram of the Data Intelligence Engineer role: agents automate ingestion and modeling, the engineer owns context and evals, and builders across the company are enabled

If your pipelines repair themselves, there is more time to invest in loading new data sources, documenting business logic and context, and constructing evals that test how well agents can answer business questions. You and your team can shift your focus from tactical bug hunting to partnering with the business.

In my opinion this is super interesting and fun work to do! I’ve always found platform building and self-service enablement to be some of the most fulfilling work.

If you’re reading blogs like this, I bet you are already a Data Intelligence Engineer! Check out our job description for the role to see if it resonates with you.

Subscribe to motherduck blog