Migrate from Dataform

sqlanvil migrate-dataform converts a Dataform/BigQuery project into a SQLAnvil-on-Postgres project — one command, directory in → directory out:

sqlanvil migrate-dataform ~/work/my-dataform-project ./my-sqlanvil-project
sqlanvil compile ./my-sqlanvil-project     # should already be green

Prefer a guided flow? sqlanvil init --interactive (1.23+) asks “start fresh, or convert an existing Dataform project?” and runs this same converter, then offers a credentials Q&A for the converted project.

What it does

Sources stay in BigQuery. Classification is by action type: every type: "declaration" is a source — its data does not move. The converter groups them into one named connection per source GCP project (mode: runner-extract — no Vault secret, no extensions) and rewrites each declaration’s config to ride it. The declaration’s schema: keeps naming its dataset, and the extracted table keeps that name in Postgres, so schema-qualified ref()s resolve unchanged.
Targets move to Postgres. Materializing actions (tables, views, incrementals, assertions, operations) get a dialect pass that rewrites what can be rewritten without changing what the query means, and flags the rest.

Rewritten (1.28): the call family — SAFE_CAST (as a pg_input_is_valid guard, so invalid input still yields NULL instead of aborting the query), SAFE_DIVIDE, SPLIT, DATE_DIFF, OFFSET/ORDINAL subscripts, BigQuery type names, CURRENT_DATE() → CURRENT_DATE; the lexical differences that silently change meaning — backtick identifiers, double-quoted strings (PostgreSQL reads those as identifiers, so copying them across turns a literal into a column reference), raw strings, # comments, EXTRACT(DAYOFWEEK); COLLATE(x, ''); and the dataform.projectConfig → sqlanvil.projectConfig compile-global rename. NOT ENFORCED keys are rewritten to the PostgreSQL form that would work and left commented — enforcing integrity a project has never had should be your decision.

Flagged with an inline -- SQLANVIL-MIGRATE: marker: QUALIFY, ARRAY(SELECT AS STRUCT …) (with a per-site recommendation and the SQL, in the report), EXPORT DATA, geography, and anything else needing a judgement call. BigQuery-only config (bigquery: {}, partitionBy/clusterBy, project qualifiers) is commented out and flagged for translation to postgres: {}.
Config converts mechanically. dataform.json/workflow_settings.yaml becomes a sqlanvil workflow_settings.yaml (warehouse: supabase, connections block, vars carried).
Credentials never travel. .df-credentials*, service-account keys, .env files, and stale build artifacts in the source are detected and excluded from the output (and listed in the report).

Staying on BigQuery (`--target-warehouse bigquery`, 1.24+)

Don’t want to move warehouses at all — just stop using Dataform? Convert with:

sqlanvil migrate-dataform ./my-dataform-project ./my-sqlanvil-project --target-warehouse bigquery

This is a tooling swap, and it’s deliberately boring: BigQuery is a first-class sqlanvil warehouse, so your SQL bodies, bigquery: {} blocks (partitionBy/clusterBy), and declarations pass through byte-identical. defaultProject and defaultLocation carry into the new workflow_settings.yaml, dataset casing is preserved, and there are no connections — the sources are already native. The only changes: the settings file converts (sqlanvilCoreVersion: instead of dataformCoreVersion:) and the dataform.projectConfig compile global renames to sqlanvil.projectConfig. No Supabase or Postgres anywhere — not even an account.

Auth works like Dataform’s, out of the box (1.26+): the converter generates the secretless ADC-mode .df-credentials.json (project + location only, gitignored — your actual credentials are never copied), so gcloud auth application-default login is the only setup local runs need. Service-account keys and keyless CI are covered in Running in production.

And your first runs can’t touch production: a ready-made test environment is scaffolded — sqlanvil validate first (read-only dry-run of every model; all-PASS means the swap is done), then sqlanvil run . --environment test writes to <dataset>_test datasets. Want tests in a separate GCP project instead? Set environments.test.defaultDatabase. Only a deliberate plain sqlanvil run . touches the real datasets.

The init --interactive convert path asks which target you want, with keep-BigQuery as the default — plus an auth step (detect gcloud ADC and offer to launch the login flow, or point at a service-account key, or defer) and a test-target step (suffix / separate project / none), both before anything runs against your warehouse.

The migration report

migration-report.md + migration-report.json land in the output root: the connections and their per-declaration sqlanvil introspect to-dos, every flagged file with line-level notes, and a warning when a schema is both source and target (its declared tables are probably produced by the pipeline itself — convert those to plain declarations once their producers move).

From 1.28 the report is a to-do list rather than an inventory. Findings are grouped by class (all 141 SELECT * EXCEPT sites as one item, not 141 items), and split by who can finish them: mechanical work an agent or a sed can apply, versus work needing a decision about your business. Within that, items that change meaning rank above ones that fail to compile — a query that still runs and returns different numbers is the more expensive failure. The report is also a living document: all three phases below write to it.

Finishing the migration

sqlanvil compile — green out of the conversion.
Scaffold columnTypes: run the generated scripts/introspect_all.sh (one concrete sqlanvil introspect command per declaration) on a machine with source read access, and commit what it writes. Failed commands are collected and summarized at the end (1.27.0) — typically declarations for tables that no longer exist, or never existed. That’s fine: unreferenced declarations are inert (nothing reads them, nothing runs them), and a referenced declaration’s extract fails at run time with the exact introspect command.
sqlanvil migrate-fix (1.28+) — the conversions that need the introspected schema, and so cannot happen in step 1: SELECT * EXCEPT (…) expands to the explicit column list, and GROUP BY ALL becomes positional ordinals. Sites it can’t resolve (a star over a source that was never introspected) are reported rather than half-rewritten. --dry-run first if you want to see the changes before they land.
sqlanvil validate against your warehouse — PASS/FAILURE/BLOCKED per model is the migration to-do list. Work the SQLANVIL-MIGRATE: markers until it’s green.
The report is written for a coding agent to work through with you: point Claude Code (or any agent) at migration-report.md and the inline markers. Each class says what to do, and the ones that need a decision say what the decision is, so the agent brings you the question rather than guessing at it.

What it deliberately does not do

Full SQL transpilation. The line is deliberate: a construct is rewritten when the PostgreSQL equivalent is determined by the source, and flagged when it depends on something only you know. SAFE_DIVIDE(a, b) has one honest answer; whether an ARRAY<STRUCT> should become a child table, a pivot, or jsonb depends on what reads it and what your BI tools expect — so the report recommends and shows the SQL, and you decide. A guess that compiles is worse than a flag, because nothing downstream tells you it was wrong.

Also out of scope: moving the BigQuery data (connections cover read paths), automatic columnTypes introspection (needs credentials the converter never touches), and non-Dataform sources (dbt is its own beast).