A 6-Step Playbook for dbt Migration With an AI Agent

A dbt migration plan that budgets the schedule for translating SQL finishes the translation early. The rest of the schedule then goes to parity testing, which means proving the new pipeline returns the same numbers as the old one. That proof is slow because the old pipeline holds years of business decisions nobody wrote down. Each decision has to be found in the old code, then reproduced in the new pipeline or explained away.

Start parity testing in week one, against whatever you have translated so far.

Risk concentrates between steps four and six, not at the start.

Step 1. Inventory Before You Translate Anything

Start a dbt migration with an AI agent by building an inventory of every database object you are moving: tables, views, stored procedures and jobs. Build that list from recollection and you miss the ones nobody thinks of, which are the ones that break the migration later.

The inventory has four columns:

• The object name.
• What reads that object.
• The last-read date, meaning when a query last touched it.
• Who owns it.

The last-read date saves the most work, and you do not have to fill it in by hand. Your data platform already logs it. Snowflake records every object a query read in ACCESS_HISTORY, which retains 365 days.

-- Snowflake. Every base object, with the last time a query read it.
-- An object that returns no row here has not been read in a year.
select
    f.value:objectName::string as object_name,
    max(a.query_start_time)    as last_read,
    count(*)                   as reads
from snowflake.account_usage.access_history a,
     lateral flatten(input => a.base_objects_accessed) f
group by 1
order by last_read asc;

An object missing from this result, or carrying a last_read a year old, is a first candidate to cut. Step 2 decides which objects actually go.

Hand this step to an agent. Reading hundreds of stored procedures is slow for a person and fast for a model. A misreading here is cheap, because you correct one row of the inventory and carry on.

Step 2. Decide What Not to Migrate

Deciding what not to migrate is the only step that makes the project smaller. Proving that nothing reads an object is harder than migrating it anyway, so scope stays larger than it needs to be. Evidence shrinks the scope back. An opinion about what looks unused does not survive the first objection.

Column-level lineage supplies that evidence. Traced at table level, lineage reports every downstream model and tells you to migrate. Traced at column level, lineage names only the reports that actually read the field. Column-level lineage use cases shows how to trace a single field to its downstream consumers.

dbt answers the table half of the question on its own:

# Every model downstream of one legacy staging model, as a countable list.
dbt ls --select stg_legacy_orders+ --resource-type model --output name

dbt ls follows the ref() links between models, so it lists whole models and never individual columns. Cut an object only when both checks agree:

• The last-read date from step 1 shows no reads inside the retention window.
• Column-level lineage shows nothing still reading it.

Every object you cut is one fewer to translate in step 3 and one fewer to diff in step 4.

Step 3. Translate the Syntax

An agent translates SQL from one dialect to another quickly, so step 3 is one of the shorter steps in a migration. Migration plans still give translation the most room, because it is visible progress while the risk sits later in step 4.

Dialect differences are mechanical and most of them have deterministic mappings:

Division is the difference most likely to change a number without anyone noticing:

-- Redshift divides two integers and truncates the result.
select 7 / 2 as ratio;   -- 3

-- Snowflake divides the same two integers and scales the result.
select 7 / 2 as ratio;   -- 3.500000

Both statements are valid SQL on both platforms. Each platform returns a different value, and neither raises an error.

Open-source tooling already handles cross-database SQL translation. SQLGlot translates between more than 30 dialects, and its README states what that does not cover.

SQLGlot is a transpiler, not a validator. A query that parses successfully may still fail at execution time.

A transpiler rewrites a query from one dialect into another. A transpiler answers whether the target engine accepts the statement. It does not answer whether the rows the new query returns match the rows the old query returned.

Altimate Code connects to over a dozen data platforms: Snowflake, Databricks, BigQuery, Redshift, Postgres, DuckDB, Trino, ClickHouse, MongoDB, MySQL, SQL Server, Oracle and SQLite. Dialect translation covers fewer of those platforms. The documented cross-dialect translation covers Snowflake, BigQuery, Databricks, Redshift, PostgreSQL, MySQL, SQL Server and DuckDB.

Our migration guide publishes one 47-model project end to end. 38 models translated cleanly, 6 needed manual review for VARIANT columns, and 3 used Snowflake-only features. That is 81% automatic. Snowflake STREAMS map to change data capture and TASKS to scheduled queries, which are architecture calls rather than syntax.

How the object set narrows across the six steps. Step 4 is where the differences surface.

Step 4. Prove Parity With a Data Diff, Not a Review

Parity testing runs both pipelines against the same input and compares the results to prove they agree. Scheduled as final QA, after every model is translated, parity testing is the step that slips the schedule. The number of mismatches decides how long it takes, and nobody knows that number until the first diff runs. Leave parity to the end and you learn it with the deadline already fixed.

Parity has an exact answer, which is why a review is the wrong tool. A reviewer reads two queries and says they look equivalent. A data diff is a query that compares the rows the two pipelines produced and reports where they differ.

Inside one platform the diff is two set operations:

-- Row-level parity inside one platform. A pass returns zero rows.
select 'legacy_only' as side, d.* from (
    select * from legacy.fct_orders
    except
    select * from target.fct_orders
) d
union all
select 'target_only' as side, d.* from (
    select * from target.fct_orders
    except
    select * from legacy.fct_orders
) d;

Run an aggregate diff as well. The row-level diff above uses EXCEPT, which removes duplicate rows before it compares the two sides. A row that appears twice on one side and once on the other passes that diff, and a SUM over the table drifts by one row's value:

-- The check a row-level diff passes straight through.
with legacy_monthly as (
    select date_trunc('month', order_date) as month, sum(net_amount) as total
    from legacy.fct_orders group by 1
),
target_monthly as (
    select date_trunc('month', order_date) as month, sum(net_amount) as total
    from target.fct_orders group by 1
)
select
    coalesce(l.month, t.month) as month,
    l.total                    as legacy_total,
    t.total                    as target_total,
    l.total - t.total          as delta
from legacy_monthly l
full outer join target_monthly t on l.month = t.month
where l.total is distinct from t.total;

EXCEPT only works when both tables sit on one engine. In most migrations they do not, because the old pipeline runs on the platform you are leaving and the new one on the platform you are moving to. Bisection hashing is the diff for that case, and it compares checksums instead of rows. It splits both tables into blocks and hashes each block on its own engine, so no row leaves its platform. It then repeats on only the blocks whose hashes disagree. You compare a 100M-row table by moving checksums, and the rows stay where they are.

A high mismatch count does not always mean the migration is wrong, so read what the differences are before you act on the count. One published SQL Server to Microsoft Fabric run shows why:

• What ran. A row-level diff ran on the fact_sales table.
• What came back. The diff returned 92 differing rows.
• Why it passed. All 92 differed by trailing decimal zeros alone, because the staging layer cast to a fixed scale. The values matched, so the table passed.

When an agent rewrites a query, a second model reviewing that query is no substitute for a diff. We covered that on our DataCamp panel about what AI agents are really changing in data engineering.

Step 5. Cut Over One Consumer at a Time

Cutting over means pointing each consumer at the new pipeline. A consumer is any dashboard, report or job that reads your data. Move the consumers across one at a time. Start with the consumers whose owners sit close to the data team, because those owners notice a wrong number quickly and report it privately. In practice that means internal dashboards before customer-facing reports.

Keep the diff running on a schedule while both pipelines are live, rather than as a one-time gate. A model that matched once stops matching when a source changes shape. While both pipelines run, you can see which side moved. Once the old pipeline is off, nothing is left to compare against.

Step 6. Decommission the Legacy Pipeline on Evidence, Not Silence

Turn the legacy pipeline off on evidence that nothing depends on it. A quiet week proves nothing, because a quarterly job does not run in a quiet week.

Three things have to hold before you decommission:

• Lineage shows nothing reads the legacy objects.
• The diff has come back clean through a full business cycle.
• The last-read date shows no unexpected recent activity.

Then suspend the objects rather than dropping them, for one more cycle:

-- Reversible. A surprise consumer raises an error instead of losing data.
alter task legacy_db.public.load_orders_daily suspend;
alter view legacy_db.public.v_orders rename to v_orders_retired;

A drop destroys the data. A suspend leaves the data in place, so the extra cycle costs only calendar time. A quarterly job nobody documented then fails with a clear error instead of quietly returning nothing. You undo a suspend by resuming the task and renaming the view back. Once a full cycle passes with no surprises, drop the objects and archive the step 1 inventory.

Each dbt Migration Step and How to Know It Worked

Four Semantic Differences Cause Most Parity Failures

Four causes account for most parity failures, so you can work step 4 as a checklist rather than an open investigation.

Null handling drifts on sort order. Engines disagree on whether a null sorts before or after every other value. That disagreement matters in a query that keeps one row per group. A common shape is row_number() over (partition by customer_id order by updated_at) where updated_at holds nulls. Each engine keeps a different row for those groups, and the row-level diff catches it.

Timezone drift starts with a timestamp column that carries no timezone. Each platform decides for itself which clock that value is on. One platform reads it as UTC and another reads it in the session timezone, and a session setting shifts every result with no change to the code.

Take an order placed late on the last day of the month. A date_trunc('month', order_date) moves that order into the next month on one platform and leaves it in place on the other. The two monthly totals differ by exactly the orders in that boundary window. The aggregate diff run across a month end catches it, because those boundary rows move.

Undocumented filters are the hardest of the four causes to spot, because nothing marks them. A legacy pipeline hides three kinds:

• A WHERE clause that excludes test accounts.
• A hardcoded date cutoff nobody remembers setting.
• A customer ID excluded after an incident.

An agent translating faithfully keeps all three filters, which is correct. The numbers break when somebody later tidies the translation and removes them.

Diff a full month rather than a sample, because timezone boundaries and hidden date cutoffs only show up across a month end. Workload migration covers the same ground as a use case.

The four causes of parity failures. Each one changes a number while both engines run the query without an error.

Choose dbt Core V1 or V2

As of August 2026, target dbt Core v1. dbt Labs published the first alpha of dbt Core v2.0 on 1 June 2026. The launch post describes two free distributions of the v2 engine. One is Fusion, a binary that contains some proprietary code, and the other is the Apache 2.0 code in the dbt-core repo. Advanced SQL comprehension, linting and column-level lineage ship in Fusion only. The v2 roadmap says Core v1 "is not going away tomorrow, or any time soon."

dbt Core v1.12 ships the v2 parser behind a flag:

# Run from dbt Core v1.12. If the project parses, v2 becomes a real option.
dbt parse --use-v2-parser

If your project parses under that flag, v2 is a realistic target. If it fails, the errors name the stricter rules you would have to fix first, so you learn the size of the job early. The dbt-autofix package handles much of the mechanical part. Migrating to v1 now and v2 later is two migrations, so pick one and write down why.

Where a dbt Migration With an AI Agent Helps and Where It Does Not

An agent earns its place in the step 1 inventory and the step 3 syntax translation. Steps 4 through 6 are where migrations fail. Those steps need a diff and a human decision, and more generated code does not help there. Run the inventory and the translation unattended, then gate parity on a diff before anything moves. The broader argument for that split is why deterministic tooling beats LLM-only data agents.

Run an aggregate diff against the models you have already translated, before you translate the next one.