Migrating SQL to dbt by hand vs with an AI agent: what changes and what doesn't

Migrating SQL to dbt by hand means a person reads each legacy script, splits it into staging and mart layers, replaces hard-coded table names with sources and refs, and writes tests, which is the process dbt describes in its own guide on refactoring legacy SQL to dbt. With an AI agent, the same steps are generated and validated by software, and a human reviews the result instead of typing it. The decision is not whether an agent can write the SQL. It is which steps you hand over and which you keep behind an approval.

Key Takeaways

• •The manual path is well documented and reliable, but it is serial: one engineer, one script at a time, with the effort growing with the number of legacy scripts and how tangled they are.
• •An agent can take over the mechanical work: splitting SQL into layers, declaring sources, rewriting table references as refs, and drafting tests and CI checks.
• •Humans should still approve the business logic, the equivalence check against production output, and the cutover. Those are judgment calls, not typing.
• •Evaluate an agent on whether it proves the new models match the old output, not on how clean the generated SQL looks.
• •In our opinion, the right split is agent drafts, tests verify, human approves. Automating the middle without the approval gate is where migrations go wrong.

Migrating SQL to dbt by hand vs with an AI agent: the job and who does it today

The job is simple to state. A team has transformation logic living outside dbt: stored procedures, scheduled scripts, views nobody owns, or a BI tool's embedded queries. They want that logic as modular dbt models with declared sources, explicit dependencies, tests, and a CI check, so it can be reviewed in pull requests and rerun reliably.

Today an analytics engineer or data engineer does this, usually alongside their regular work. It is rarely scheduled as a project because each script looks small. The cost shows up in aggregate: dozens or hundreds of scripts, each with undocumented assumptions, and nobody who remembers why a filter on line 212 exists.

The manual workflow, step by step

dbt's legacy SQL refactoring guide lays out the standard approach: migrate the code as-is first, then refactor it into a modular structure, then audit the output against the original. In practice, the handoffs look like this.

• •Inventory: list the legacy scripts, their schedules, and the tables they read and write. This is usually assembled from scheduler configs and tribal knowledge.
• •Copy as-is: paste the legacy query into a single dbt model and get it running, so you have a baseline that produces the same rows.
• •Declare sources: replace hard-coded table names with dbt sources so lineage and freshness checks can attach to them.
• •Split into layers: pull out import CTEs, then staging models, then intermediate and mart models, replacing table references with refs.
• •Add tests: write data tests for keys, nullability and accepted values, based on what the data should look like.
• •Audit: compare row counts and column values between the legacy output and the new model, and investigate every difference.
• •Wire CI and cut over: add a build check to pull requests, retire the legacy job, and update downstream consumers.

Where the manual path breaks

None of these steps is hard in isolation. The failure modes are about scale and memory. Splitting a 600-line query into clean layers is tedious, so teams stop at step two and ship a single giant model. Tests get written for the columns someone remembers, not the ones that matter. The audit is skipped under deadline pressure, and a subtle join change surfaces weeks later as a dashboard number that moved.

There is also a coordination cost. The person migrating a script needs to know who consumes its output, which is a question for lineage and the catalog, and whether the schema will change underneath them. Those answers live in other people's heads and other tools, so the engineer becomes the integration layer.

What an agent does instead

Our Migration Agent runs the skill we call dw-migrating-sql-to-dbt, whose scope is narrow on purpose: convert legacy hand-written SQL into modular, tested dbt models with refs, sources, and CI validation. That maps onto steps three through five and the CI part of step seven above. It reads the legacy SQL, proposes the layered model structure, declares the sources, rewrites table references as refs, drafts tests, and adds a validation check for CI.

Because every Data Workers agent is an MCP server, an engineer can invoke this from the terminal in Claude Code or Cursor rather than learning a new interface. The Model Context Protocol is the open standard that lets those clients call tools like this one. The engineer points the agent at a script, reviews the proposed models in a normal diff, and iterates in the same session they already use for everything else.

Where the migration touches other systems, the agent chains with the rest of the swarm. Our Schema Agent can map which downstream tables depend on a column before it is renamed, and our Pipeline Agent can handle the orchestration side once the models exist. We covered how that chaining works in our posts on the agent swarm; the point here is that the engineer no longer gathers that context by hand.

Manual vs agent, step by step

We are deliberately not putting hours or cost figures in this table. We have no public benchmark we can attribute for this exact job, and effort varies far more with how tangled the legacy code is than with who or what is typing.

What still needs a human approval

An agent can produce plausible SQL that is subtly wrong. Three decisions should stay with a person, and we configure them as approval gates rather than suggestions.

• •Business logic changes: if the agent proposes to simplify a filter or merge two CTEs, a person who knows the intent should confirm that the output is meant to be identical.
• •The equivalence bar: someone has to decide what counts as a match, such as exact row equality, a tolerance on aggregates, or known acceptable differences.
• •Cutover: retiring the legacy job and repointing consumers is a change in production behavior. It should be approved, logged, and reversible.

This is why our open-source edition is read-only by default and writes arrive with approval workflows and audit trails in Pro. For a migration, reading and proposing is safe to automate broadly. Writing to production should be gated per domain, with the autonomy level raised only as the team's trust in the output grows.

How to evaluate an agent for this job

Run any candidate, ours included, on three or four real scripts from your own backlog instead of a demo. Pick one simple, one ugly, and one that feeds an important dashboard. Then check the following.

• •Does it prove equivalence? The strongest signal is a side-by-side comparison of legacy and new output, not a claim that the models are equivalent.
• •Does it follow your conventions? Naming, layer structure and test patterns should match your existing dbt project, which depends on how well the agent reads that project's context.
• •Does it fail loudly? An agent that flags SQL it cannot safely convert is better than one that guesses.
• •Does the work land as reviewable diffs? You should be able to read, comment on and revert everything it produced.
• •Does it stay inside your infrastructure? Customer data should not leave your environment during the migration.

Frequently Asked Questions

Can an AI agent migrate SQL to dbt without human review? It can generate the models, but we do not recommend skipping review. The agent is good at mechanical conversion and weak at knowing which odd filter is a deliberate business rule. Keep a human approval on logic changes and cutover.

How is migrating SQL to dbt with an agent different from asking a general chat assistant to rewrite a query? A chat assistant converts the text you paste in. A purpose-built agent works against your repository and warehouse context, declares sources and refs consistently across files, and adds tests and CI validation instead of returning a single rewritten query.

Does the agent replace the audit step? No. The audit is where you find out whether the migration preserved behavior, and the acceptance bar is a business decision. The agent can run and report the comparison, but a person signs off on it.

Do we need Claude Code or Cursor to use it? No. The agents are MCP servers, so any MCP-compatible client works, including Claude Code, Cursor, Windsurf, VS Code and GitHub Copilot. Those are simply where most engineers already work.

Where should a team start? Pick a low-risk script, migrate it both ways, and compare the diffs and the time your reviewers spend on them. That gives you evidence from your own codebase, which is worth more than any general claim, including ours.