DVT FLIP-TARGET-TO

START WITH --DRY-RUN

Always run it with --dry-run first. Loudly, deliberately, every time: it shows you the complete ledger — every file that would move, every role that would change, every limitation — and writes nothing. It is the cheapest look you will ever get at a warehouse migration.

$ dvt flip-target-to snow_prod --dry-run
✅ models/staging/stg_orders.sql
✅ models/staging/stg_customers.sql
✅ models/marts/orders_enriched.sql
⏭  models/marts/vendor_audit.sql  — pinned to a target override

3 flipped, 1 skipped, 0 not converted, 0 converted with a reported limitation (postgres → snowflake)
dry run — nothing written

Don't remember what your outputs are called? Run it bare and it lists them, marking the current default:

$ dvt flip-target-to
Pick a target:
  pg_local                     postgres  ← current default
  snow_prod                    snowflake

Usage: dvt flip-target-to <target> [--dry-run]

THE REAL RUN

Happy with the ledger? Drop the flag. The same report prints, and this time every converted file is written (each with a timestamped .bak beside it), the target:line in profiles.yml moves, and the new engine's adapter is provisioned if it isn't already there:

$ dvt flip-target-to snow_prod
✅ models/staging/stg_orders.sql
✅ models/staging/stg_customers.sql
✅ models/marts/orders_enriched.sql
⏭  models/marts/vendor_audit.sql  — pinned to a target override

3 flipped, 1 skipped, 0 not converted, 0 converted with a reported limitation (postgres → snowflake)
default target: pg_local → snow_prod in /Users/you/acme/profiles.yml
environment: provisioned 'snowflake' for 'snow_prod'

The rewrite domain is every path class whose SQL executes on the default target, read from your own dbt_project.yml paths: models, snapshots, tests (singular and generic), analyses and macros. Block-wrapped files keep their semantics untouched — a snapshot's strategy and unique_key, a test's signature, a macro's parameters are Jinja, and Jinja comes back byte-for-byte. Only the SQL between the tags is translated.

And some things are never touched, by design: federated models that stay federated (their body is DuckDB SQL by doctrine, whatever the default is), models pinned with config(target='...')(a pin is a declaration that the model does not live on the default), and DVT's own compatibility macro file (machinery, not your SQL — the skip is stated in the ledger).

THE FLIP ALWAYS COMPLETES

This is the command's charter, and it is a founder ruling: a refused flip strands you. Your model works today on the old target; if the flip refused, you could neither move nor fix a limitation that lives in a translator rather than in your code. So the flip never gives up halfway — every file is attempted, the target always moves, and the run always finishes. Where the lane cannot carry a file across, it names it — kept exactly as written — and moves on:

$ dvt flip-target-to pg_prod
✅ models/staging/stg_products.sql
✅ models/staging/stg_orders.sql
✅ models/staging/stg_orders.sql  — AI lane (attempt 1)
✅ models/marts/orders_enriched.sql  — already valid — unchanged
❌ models/marts/search_products.sql  — needs translation mysql -> postgres; AI lane: no convergence
   after 3 attempt(s) — the output calls MATCH(), which the postgres engine does not have
     ✗  its SQL is mysql and must become postgres — queued for the AI lane

⚠️  WHAT STILL NEEDS TRANSLATING — ruling 243. The flip COMPLETED and the target has moved;
these files are still in the old dialect, named so you can fix them rather than discover them:

    /Users/you/shop/models/marts/search_products.sql
      ✗ its SQL is mysql and must become postgres — queued for the AI lane

    1 file(s) were NOT converted and are still mysql. They are unchanged on disk — nothing was
    half-written — and they will fail on 'pg_prod' until you translate them.
    This is not a partial flip: the project is on the new target and every one of these is a
    known, named, local fix. A flip that refused instead would have left you unable to move OR
    to fix it.

3 flipped (2 translated by the AI lane), 0 skipped, 1 not converted, 0 converted with a reported limitation (mysql → postgres)
default target: mysql_local → pg_prod in /Users/you/shop/profiles.yml
environment: provisioned 'postgres' for 'pg_prod'

Three verdicts, and the ledger keeps them apart. Already valid — unchanged: the file means the same thing read as the old and the new dialect (a neutrality judge reads it both ways and compares), so it is not touched and no AI is spent on it. Translated by the AI lane: the file needed the new dialect and a validated answer landed, with a .bak beside it. Not converted: the lane gave no answer the validators accepted within its bounded attempts — the file is left exactlyas it was (nothing is ever half-written) and the reason is the validator's own words. When the AI-Proxy is not connected, every file that needs translating is named this way and the flip still moves the target:

❌ models/legacy/order_stats.sql  — needs translation postgres -> snowflake; AI-Proxy is not
   configured — the flip moved the target and judged; this file still needs translating
   (enable AI, or ask Martin)

Translate these by hand (full paths, straight to your editor):
  /Users/you/shop/models/legacy/order_stats.sql
or connect AI-Proxy (hub → Settings → AI CREDENTIALS) and rerun — the lane translates what is
not yet the new dialect, validated before anything is written.

Jinja is never a blocker either. Every {{ ref() }} and {{ config() }}rides through the lane as an opaque sentinel the proposer cannot legally edit, and comes back byte-for-byte — flip evaluates no branch and counts no loop; rendering is dbt's job. A reported limitation is not a failed command: the exit code is 0, because the flip did what it said it would — it moved the project and told the truth about the boundary.

WHAT ONE FLIP ACTUALLY RUNS — FIVE PHASES

A flip is not a single rewrite pass. It is five phases, in a fixed order, so the project is provably healthy on both sides of the move:

config-checkup  →  config-fix  →  flip-target-to  →  config-checkup  →  config-fix
   (before)         (before)      the move itself       (after)           (after)

Before: checkup diagnoses and fix repairs anything that would stop the project parsing — an illegal materialization string, for instance — because a project has to parse before anything can flip:

config-checkup then config-fix ran first — the project has to PARSE before anything flips:
  ✅ models/marts/promo_lift.sql — materialized='tabel' is not a DVT materialization

The move:every model's role is recomputed under both targets, the files are translated, and the profile's target: line is written. After: the same checkup/fix pair runs again — this time against the target that is default now, reconciling whatever the move changed:

config-checkup then config-fix ran again AFTER the flip — leftovers are reconciled against
the target that is default NOW ('snow_prod'), which is the pass that could not exist while
the target moved last:
  ✅ models/marts/blended_sessions.sql — materialized='table' but its dependencies are
     federated — the engine already coerces this at runtime

Then the landing. A flip ends with a real dvt parse on the new default — not a copied manifest, an actual parse of what is now on disk — because a flip lands finished or it has not landed. Silent when it succeeds; loud, with a non-zero exit code, when it does not:

❗ THIS FLIP DID NOT LAND: the project does not parse on 'snow_prod'. The target moved and
the files were rewritten, but `dvt parse` failed, so target/manifest.json is not the new
engine's and every verb that reads it will refuse (ruling 282):
      • Compilation Error in model orders_enriched (models/marts/orders_enriched.sql)
    Fix what it names, then run `dvt parse` — the flip itself does not need repeating.

ENGINE SEMANTICS GRAMMAR CAN'T SEE — COMPENSATED AT THE DOOR

Transpiling handles grammar; some engine rules are semantics, invisible to any syntax-level translator. ClickHouse happily parses the ANSI FETCH FIRST n ROWS ONLY clause — then refuses to run it without an ORDER BY. SQL Server genuinely requires a dummy ORDER BY (SELECT NULL) under OFFSET/FETCH — an artifact that is load-bearing there and a syntax error on Oracle. A model that travels between engines picks these up on one engine and trips over them on another.

DVT compensates for exactly these rules at each engine's door: a plain row-count FETCH becomes LIMIT for ClickHouse; the T-SQL dummy — and only the dummy, never a real ordering — is stripped for Oracle. Every such rule lives in onehome shared by the flip and the federation engine's own render path, so both lanes are cured by the same fix and can never disagree about what an engine accepts. And where a clause has no honest translation (FETCH ... PERCENT, WITH TIES), DVT passes it through so the engine refuses it in its own words instead of silently changing what your query means.

ROLES MOVE WITH THE TARGET

Federated-vs-native is a property of a model relative to the default target— so moving the target moves the boundary, and models trade places. A model that joined a Snowflake source into your Postgres warehouse was federated (DVT's engine ran it); flip the default to Snowflake and every input is suddenly local, so it becomes a plain native table. Its mirror image — a model that read only Postgres tables — now reads a non-default engine, and becomes federated. The flip handles both directions automatically, and the dialect follows the role: a model going native is rewritten into the new target's dialect, a model going federated is rewritten into DuckDB, because that is who runs it now.

✅ models/marts/daily_kpis.sql  — ROLE CHANGED: f_table -> table (now native)
     ↳ every engine it reads is now the default target, so it builds in place — no round trip
       out through Sling and back in. It still stores its rows, exactly as it did before.
✅ models/marts/blended_sessions.sql  — ROLE CHANGED: table -> f_table (now federated)
     ↳ it now reads an engine that is NOT the default target, so it LANDS its data on the
       default target — that costs storage and a load; that is the physics, not a preference.

2 model(s) changed ROLE because the default target moved — federated-vs-native is relative to
the default, so moving it moves the boundary:
      • models/marts/daily_kpis.sql: f_table -> table
      • models/marts/blended_sessions.sql: table -> f_table

The pairs are closed — table ↔ f_table, incremental ↔ f_incremental — so a flip there and back is exact. Every role change states its physics in the ledger, never silently: a model that was ephemeral and becomes federated now persists rows it never stored before, and the run says so in capitals. One conversion is one-way: no view is flippable— a view the flip rewrites becomes a table, permanently, and the ledger calls that what it is, the cost of flipping.

THE LANE — AI TRANSLATES, MACHINES JUDGE

Since 0.2.59 there is exactly one translator, and it is the AI lane; the deterministic transpiler that preceded it is gone. What stays deterministic is everything that never lied: which models change role, the target's provisioning, the backups, the parse after the flip, the compile on the new engine, and the neutrality judge that decides whether a file needs translating at all. That judge asks the SQL parser to read the file twice — as the old dialect and as the new — and compares the two readings; when they agree, and no function has to be spelled differently, the file is already valid and is left alone.

Two more readings guard that verdict since 0.2.59. A keyword the new engine no longer uses (LIMIT on SQL Server, ILIKE, ::) and a clause the new engine requires that the file never wrote (T-SQL's FETCH without its OFFSET and ORDER BY) both send the file to the AI lane rather than calling it already valid. And case is never the translator's to change: a translation is re-spelled to the author's own case for every bare identifier before it is written, and on ClickHouse — the one engine that stores an unquoted name exactly as written — a model's bare columns are re-spelled to the way they landed upstream, with the flip ledger saying so.

$ dvt flip-target-to snow_prod
✅ models/legacy/order_stats.sql  — AI lane (attempt 2)
✅ models/marts/orders_enriched.sql  — already valid — unchanged
engine judge: 2 file(s) failed compile on 'snow_prod' — one agentic repair round
  ↩ 1 repair(s) did not clear the judge — reverted, your SQL is unchanged

Every file that needs the new dialect gets its own bounded conversation, and they run in parallel: propose, validate, feed the validator's exact words back, retry, up to three attempts per file. No answer touches a file until it passes validation: every Jinja block intact, the output parses in the new dialect, and no call to a function the new engine provably lacks. A file the lane cannot carry is kept as written and named; the lane is then asked once more for DuckDB SQL, so the model can run federated on DVT's own engine instead of being stranded (ruling 345).

Then the final judge, and it is not an AI: after the target moves, the real new engine compiles and explains the project — the translated files andthe untouched ones, because a lenient parser can wave through what an engine will not. Any model it rejects buys one more parallel repair round, driven by the engine's own error text, and a repair that does not clear the judge is reverted. The flip still always completes.

AI-only means the flip translates only while the AI-Proxy answers. With --no-ai, or no proxy configured, the flip still moves the target and the roles, judges, and names every file that still needs translating. --agentic is accepted for scripts written earlier and does nothing — the lane is the flip.

FLIPPING PART OF THE PROJECT

-s / --select and -x / --exclude scope the file rewrite with shell-style patterns, matched against names and paths across every path class. The classic use is finishing the stragglers after a full flip:

$ dvt flip-target-to snow_prod -s 'staging/*'        # one folder
$ dvt flip-target-to snow_prod -s stg_orders          # one model
$ dvt flip-target-to snow_prod -x 'legacy/*'          # everything but

BACKUPS, ROLLBACK, EXIT CODE

Every file the flip rewrites keeps a timestamped .bak beside it — profiles.yml included — so any single decision is reversible by hand. And if a run is interrupted — a crash, a Ctrl+C, a write that dies halfway — the flip restores every file it had already rewritten, verified byte-for-byte, because an unfinished run must leave the project exactly where it started:

❗ This flip did not finish, so it rewrote NOTHING: 12 already-translated file(s) restored
to exactly what they were (ruling 149 — an unfinished run leaves the project in an UNKNOWN
state, which is the one thing all-or-nothing still guards).

A reported limitation is the opposite case — a known state, not an unknown one — so it never triggers a rollback and never fails the command. The exit code is 0unless the final parse fails, in which case the parse's own exit code is carried through. On a terminal, unconverted files also get an interactive aftercare pass — the flip offers to open each one in your editor at the work that is left; --silent (or any non-TTY run, like CI) skips it.

REFERENCE — EVERY FLAG