A Framework For Running A Migration Agent In Production
Scope, context, approval tiers, failure modes, rollout and metrics for agent-driven legacy-to-dbt migrations
A migration agent is safe to run in production when it has a bounded scope, read-only access by default, a written approval tier for every action it can take, and a numeric definition of "correct" that it must meet before anything is promoted. The agent should emit dbt models as pull requests, never write to production objects directly, and prove equivalence against the legacy output before a human merges. This post gives the six-step framework we use for our own Migration Agent, and the table we use to decide what the agent may do alone.
Key Takeaways
- •Scope a migration agent to one source system, one target (dbt, SQLMesh or Iceberg) and one domain at a time. Breadth is the main cause of unreviewable output.
- •The agent's inputs matter more than its model: legacy SQL, lineage, query history, row-count baselines and the target project's conventions.
- •Reading, analyzing and opening pull requests are safe to automate. Merging, cutover and dropping legacy objects always need a human approval.
- •Equivalence testing (same rows, same aggregates, same freshness) is the acceptance gate. Without it you are shipping plausible-looking SQL.
- •Roll out in shadow mode first, then assisted, then supervised autonomy, and measure review time per model, not just models generated.
A framework for running a migration agent in production
Legacy migrations fail for boring reasons: hidden dependencies, undocumented business logic in stored procedures, and cutovers nobody can roll back. An agent does not remove those problems. It speeds up the mechanical part (translating SQL, mapping dialect differences, scaffolding models and tests) so that engineers spend their time on the judgment calls. The framework below treats the agent as a junior engineer with fast hands and no institutional memory: useful, supervised, and given only the context it needs.
Agents that work inside Claude Code or Cursor fit this model naturally, because every action is a tool call that can be logged and gated. The Model Context Protocol defines how those tools are exposed, which is why we ship each of our agents as an MCP server. The same structure applies if you build your own migration agent: the safety properties come from the tool surface you expose, not from prompting the model to be careful.
Step 1: Bound the scope
Write the scope down before the agent touches anything. A workable scope names one source (for example a legacy Informatica or stored-procedure estate), one target (dbt on your warehouse, SQLMesh, or Iceberg tables), and one business domain such as billing or marketing attribution. We recommend a ceiling on the number of models per batch so a human can review the whole batch in a sitting. This is our opinion rather than a published standard, but every team we have talked to that skipped it ended up with a pile of unreviewed pull requests.
Also write down what is out of scope. Orchestration changes, access-control changes and any object consumed by an external customer should be excluded until the first batches have landed cleanly.
Step 2: Give it the right context
A migration agent needs four kinds of input. First, the legacy code itself: SQL, ETL job definitions and stored procedures. Second, lineage, so it knows what each object feeds. Third, usage: query history tells the agent which legacy tables nobody has read in 90 days and can be retired instead of migrated. Fourth, the target project's conventions: naming, folder layout, materializations and test standards.
The fourth input is the one teams forget. If the agent does not see your existing dbt style guide, it will invent one. Our Schema Agent supplies column-level lineage and drift information to the migration work, so a question like "what's the blast radius if I drop this column?" is answered from data rather than guessed from SQL text.
Step 3: Split actions by approval tier
Every capability the agent has should sit in exactly one tier. The table below is the default we start from. Teams tighten or loosen it based on their risk tolerance, but nothing in the bottom two rows should ever be autonomous in the first rollout.
| Action | Tier | Why |
|---|---|---|
| Read legacy SQL, lineage, query history | Autonomous | Read-only, no side effects |
| Classify objects as migrate, merge or retire | Autonomous, reviewed in PR | A wrong call is visible and reversible |
| Generate dbt/SQLMesh models and tests in a branch | Autonomous | Output is a diff, not a change to production |
| Run models in a scratch schema and compare to legacy | Autonomous within a sandbox | Needs write access, but only to a disposable schema |
| Open a pull request with the equivalence report | Autonomous | Starts the human review |
| Merge the pull request | Human approval | Changes what runs in production |
| Cut consumers over to the new model | Human approval | Affects dashboards and downstream jobs |
| Drop or archive legacy objects | Human approval, after a waiting period | Hardest action to undo |
The Data Workers open-source edition is read-only by default, which maps to the first rows. Write access, approval workflows and audit trails are part of the Pro tier, which covers the sandbox runs and the approval gates in this table.
Step 4: Plan for the failure modes
We see five recurring failure modes, listed here as our own observations rather than benchmarked rates:
- •Dialect drift: functions that look the same across engines but differ on null handling, timezone behavior or integer division.
- •Hidden business logic: filters or case statements encoded in a stored procedure that no documentation mentions.
- •Silent grain changes: the new model returns the same columns but a different row grain after a join is rewritten.
- •Overconfident retirement: an object looks unused in query history but feeds a quarterly job.
- •Convention drift: models that pass tests but ignore project style, which makes every review slower.
Each one maps to a control. Dialect drift and grain changes are caught by equivalence testing. Hidden logic is caught by asking the agent to list every assumption it made in the pull request description. Overconfident retirement is why the agent only proposes retirements and a human approves them after a waiting period. Convention drift is a context problem from step 2.
Step 5: Roll out in three stages
Start in shadow mode. The agent migrates a domain, runs the output in a scratch schema, and publishes an equivalence report, but nobody merges anything. The goal is to learn where it is wrong on your estate. Move to assisted mode, where engineers merge the agent's pull requests after review and the agent handles the revisions they request. Only after several clean batches should you move to supervised autonomy, where low-risk models with passing equivalence tests go to a lighter review.
Stage gates should be explicit: a number of consecutive batches with no post-merge rollbacks, and a reviewer sign-off. We covered the wider pattern of moving platforms in stages in our posts on the agentic data platform, and the same logic applies here: autonomy is earned per domain, not granted globally.
Step 6: Measure it
Models generated is a vanity metric. These are the measures that tell you whether the agent is helping:
| Metric | What it tells you | How to read it |
|---|---|---|
| Equivalence pass rate on first attempt | Quality of the translation | Falling rate means the context or the scope is wrong |
| Review minutes per model | Whether the agent saves human time | Should drop as conventions are learned; if it does not, check the style context |
| Post-merge rollbacks | Escaped defects | Any non-zero number blocks the next autonomy stage |
| Legacy objects retired vs migrated | Whether the agent is simplifying the estate | A high retire share is usually good news |
| Cutover-to-incident time | Production stability after cutover | Compare against your pre-agent migrations |
Compare these against your own previous migrations rather than an industry number. We are not aware of a published benchmark for agent-driven migration quality that we would trust to cite, so we recommend your own baseline.
Where this framework fits with the rest of the stack
The migration agent is rarely alone. During a migration the Schema Agent tracks drift in sources that are still changing, the pipelines tooling reschedules jobs, and governance checks make sure PII columns keep their policies when they move. We wrote about dbt Labs' agent skills for Claude Code and how they relate to this in a separate post, and our guide to Claude Code for data engineering covers the day-to-day workflow of running these agents from the terminal.
Frequently Asked Questions
What is a migration agent? It is an AI agent that analyzes legacy SQL and ETL code and emits modern targets such as dbt models, SQLMesh models or Iceberg tables, along with tests and an equivalence report, for a human to review.
Can a migration agent run without human approval? It can run the analysis, generation and sandbox testing steps without approval. We recommend a human approval for merging, cutover and dropping legacy objects, particularly in the first rollout.
How do you prove a migrated model is correct? Run the new model and the legacy query over the same inputs in a scratch schema and compare row counts, key-level diffs and aggregates. Treat a failing comparison as a blocker, not a warning.
Which target should a migration agent emit: dbt, SQLMesh or Iceberg? It depends on what you already run. dbt fits teams standardized on it for transformation, SQLMesh suits teams that want its virtual environments and plan-based changes, and Iceberg is a storage-format decision that can sit underneath either. Check each project's documentation, such as SQLMesh and Apache Iceberg, against your constraints.
How long does an agent-driven migration take? It varies too much by estate for us to give an honest single number. The time is dominated by review, testing and cutover coordination rather than code generation, which is why the framework measures review minutes per model.