Migration guide

This is the end-to-end playbook for moving an existing BigQuery SQL codebase into a Dataform project with sql2sqlx. The quick start shows the shortest path; this guide covers the full workflow, the decisions you will make along the way, and how to verify the result.

The philosophy to keep in mind: sql2sqlx is conservative by default. Out of the box it changes no behavior - unconvertible statements become verbatim operations actions. You then opt into more idiomatic Dataform structure (typed incrementals, dropped guards, external declarations) statement class by statement class, guided by the report.

1. Inventory your SQL

Gather the .sql files you want to migrate into one tree. sql2sqlx scans a directory recursively (default glob *.sql), so mirror your existing layout - the mirror output layout will reproduce it under definitions/.

Each file may contain one statement or many; statement boundaries are found by a real lexer, so semicolons inside strings, comments and scripting blocks are handled correctly. Files that use transactions, temporary tables, variables or procedural control flow are kept whole (see limitations).

2. Do a dry run and read the report

Before writing anything, convert with --dry-run and capture the report:

sql2sqlx ./legacy_sql --dry-run --report report.json \
    --default-project my-gcp-project \
    --default-dataset analytics

--default-project/--default-dataset describe how unqualified table names in your SQL should be resolved so that references line up with producers across files. They do not get baked into target identities - only explicit qualification in your SQL does that.

Open report.json (or add -v for a console listing) and look at:

  • failures - files that could not be parsed at all. Fix these first; a located LexError points at the exact character.

  • actions_by_type - a sanity check on how many tables/views/operations you expect.

  • refs_unresolved - your external sources (tables read but never produced).

  • warnings - triage by code using the report triage workflow.

3. Choose your conversion strategies

The defaults preserve semantics exactly. Opt into typed conversions where you understand and accept the trade-off. Each flag is covered in depth in the CLI reference and conversion rules.

If your SQL has…

Consider

INSERT ... SELECT appends you want as native incrementals

--insert-strategy incremental (emits protected: true; review INSERT_INCREMENTAL / TARGET_SCHEMA_REQUIRED)

MERGE upserts with a simple same-named key/update shape

--merge-strategy incremental-when-safe (converts only provable shapes; others stay operations with MERGE_FALLBACK)

CREATE TABLE DDL for tables managed outside Dataform

--plain-create declaration

CREATE TABLE/VIEW IF NOT EXISTS ... AS you want typed

--if-not-exists table (records the lost create-if-absent guard)

Reads of upstream tables you want as declared sources

--declare-external

Two more that affect every action:

  • --no-protected - drop protected: true from converted incrementals (only if you want full refreshes to rebuild them).

  • --tags a,b - stamp Dataform tags onto every generated action.

  • --no-annotate - omit the -- source: provenance comments.

4. Scaffold the Dataform project

Point --output at your project’s definitions/ directory and add --init-project to scaffold a workflow_settings.yaml next to it:

sql2sqlx ./legacy_sql -o ./my_dataform_project/definitions \
    --report report.json \
    --init-project \
    --default-project my-gcp-project \
    --default-dataset analytics \
    --insert-strategy incremental \
    --merge-strategy incremental-when-safe

--init-project writes a Dataform-core 3.x settings file (it is never silently overwritten):

defaultProject: "my-gcp-project"
defaultLocation: "US"
defaultDataset: "analytics"
defaultAssertionDataset: "dataform_assertions"
dataformCoreVersion: "3.0.61"

Set the location with --default-location if you are not in US. If the output directory already contains .sqlx files, sql2sqlx refuses to write unless you pass --overwrite - a guard against clobbering hand-written actions.

5. Compile with Dataform

sql2sqlx produces a project that is designed to compile, but only Dataform can confirm the whole dependency graph resolves against your real schemas. This is the authoritative final gate:

cd my_dataform_project
npx @dataform/cli@3.0.61 compile

Fix any compile errors (usually an unresolved source you want to --declare-external, or an operations action that needs a manual dependency), then re-run the conversion. Conversion is deterministic and idempotent, so re-running over the same input with the same options yields the same tree - safe to run repeatedly in CI with --overwrite.

6. Review the operations actions

Everything the tool could not convert provably lives in an operations action, flagged in the report. Walk these with intent:

  • SCRIPT_FILE - a whole file kept together for transaction/temp/variable scope. Decide whether it should stay a script or be refactored into separate models. Its SCRIPT_WRITES warning lists the tables it writes, and downstream readers are already ordered after it.

  • MERGE_FALLBACK / FALLBACK_SELECT_ALIAS - the SQL shape was outside the provable set. Often a small rewrite (naming the key column in UPDATE SET, or aliasing an anonymous expression) lets a re-run convert it.

  • DYNAMIC_SIDE_EFFECTS - CALL/EXECUTE IMMEDIATE whose reads and writes cannot be seen statically. Add any needed dependencies by hand.

  • ORPHAN_SELECT - a bare SELECT. Decide whether it should be a table, a view, or a Dataform assertion.

7. Commit and iterate

Commit the generated definitions/ tree and the report.json together. On subsequent source changes, re-run the same command with --overwrite; the deterministic output means version control shows only genuine changes.

A practical CI recipe:

# Regenerate, gate on failures, then let Dataform have the final word.
sql2sqlx ./legacy_sql -o ./definitions --overwrite --report report.json -q
python ci/check_report.py report.json      # your policy on warning codes
npx @dataform/cli@3.0.61 compile

See the report reference for a ready-made check_report.py skeleton.

What to expect over time

A first pass typically lands most CREATE ... AS statements as clean table/view actions with wired ${ref()} dependencies, and parks the genuinely imperative parts (scripts, procedures, ad-hoc DML) as operations. That is the intended outcome: a working, behavior-preserving project on day one, which you incrementally make more idiomatic as you validate each strategy against your data.