Skip to content
SQLAnvil SQLAnvil SQLAnvil

Migrate from Dataform

import { Aside } from ‘@astrojs/starlight/components’;

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

Terminal window
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

Section titled “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 an honest dialect pass: safe lexical rewrites only (SAFE_CAST → CAST, CURRENT_DATE() → CURRENT_DATE, the dataform.projectConfig → sqlanvil.projectConfig compile-global rename), and an inline -- SQLANVIL-MIGRATE: marker on every construct that needs human review (SAFE_DIVIDE, QUALIFY, UNNEST/STRUCT, BigQuery date functions, EXPORT DATA, backticked FQNs, …). 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).

The migration report

Section titled “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).

Finishing the migration

Section titled “Finishing the migration”
  1. sqlanvil compile — green out of the conversion.
  2. Scaffold columnTypes for the declarations you actually read: sqlanvil introspect <connection> <dataset>.<table> --output <declaration.sqlx>. (Un-introspected declarations compile; their extracts fail at run time with this exact command.)
  3. 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.
  4. The report is machine-readable — pointing an AI agent at migration-report.json plus the inline markers is the fastest path through the dialect work.

What it deliberately does not do

Section titled “What it deliberately does not do”

Full SQL transpilation (BigQuery→Postgres can’t be auto-translated honestly — flags beat guesses), 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).