Core concepts

This page explains the shape of what sql2sqlx produces and the vocabulary the rest of the documentation uses. Read it once and the conversion rules, dependency model and report reference will read much faster.

From .sql statements to Dataform actions

sql2sqlx reads BigQuery SQL - DDL (CREATE), DML (INSERT, UPDATE, DELETE, MERGE), and BigQuery scripts (BEGIN ... END, DECLARE, EXECUTE IMMEDIATE, …) - and rewrites each top-level statement into a Dataform action: a single .sqlx file that Dataform compiles into one node of its execution graph.

Every action has a type, carried in a leading config { ... } block. sql2sqlx emits exactly five of the Dataform action types:

Action type	What it is, and when sql2sqlx emits it
table	A table rebuilt from a query on every run. Emitted for CREATE [OR REPLACE] TABLE x AS query.
view	A logical view. Emitted for CREATE [OR REPLACE] VIEW x AS query, and for CREATE MATERIALIZED VIEW (with materialized: true).
incremental	A table that Dataform appends to on normal runs and rebuilds on a full refresh. Only emitted for INSERT/MERGE when you explicitly opt in (see conversion rules).
operations	One or more raw SQL statements Dataform runs verbatim in a BigQuery script context. This is the safe fallback: anything that cannot be converted provably without changing behavior becomes an operations action, so the SQL that runs is exactly the SQL you wrote.
declaration	A reference to a table that already exists and is managed outside Dataform (a source). Declarations carry no SQL body.

The governing rule is worth stating up front, because it explains almost every decision the tool makes:

Convert when it is provably safe; otherwise fall back to a verbatim operations action and record a warning.

An operations action runs the original statement unchanged, so the fallback path can never alter behavior - it only forgoes idiomatic Dataform structure. When the tool does choose a typed action, or when you opt into a strategy that changes first-run behavior, it records a warning explaining the trade-off.

Anatomy of a generated .sqlx file

A converted statement produces a file with up to three parts, in this order:

1. the config { ... } block;

2. a provenance comment (unless disabled) plus the statement’s own leading comments;

3. the SQL body (absent for declaration actions).

config {
  type: "table",
  schema: "analytics",
  name: "daily_sales",
  description: "Daily sales rollup",
  bigquery: {
    partitionBy: "DATE(order_ts)",
    clusterBy: ["region", "product_id"],
    requirePartitionFilter: true
  }
}

-- source: input.sql:1 (CREATE TABLE converted by sql2sqlx v0.1.0)
SELECT region, product_id, SUM(amount) AS total
FROM raw.orders
GROUP BY region, product_id

The config { ... } block

The config block is JavaScript object-literal syntax (Dataform’s format), not JSON: keys are bare identifiers where possible and quoted only when they are not valid JavaScript identifiers. sql2sqlx emits keys in a fixed canonical order so that output is deterministic and diff-friendly, regardless of the order metadata was discovered in. The top-level order is:

type, database, schema, name, materialized, protected, hasOutput,
uniqueKey, description, columns, dependencies, tags, bigquery, disabled

and inside the nested bigquery object:

partitionBy, clusterBy, updatePartitionFilter, labels,
partitionExpirationDays, requirePartitionFilter, additionalOptions

Any key not in these lists is emitted after the known keys, in insertion order. The most important fields:

type

The Dataform action type (one of the five above). Always present.

database / schema / name

The target’s project / dataset / table. name is always present. schema (dataset) and database (project) are emitted only when the source SQL qualified them explicitly - sql2sqlx never invents a project or dataset in the target identity from a --default-* value. (Defaults are still used to resolve references; see below.)

dependencies

Ordering edges to other actions. See the dependency model.

bigquery

BigQuery-specific physical options mapped from PARTITION BY, CLUSTER BY and OPTIONS(...).

Booleans such as protected: true, materialized: true, hasOutput: true and uniqueKey: [...] appear only when the corresponding rule applies.

The provenance comment

Unless you pass --no-annotate (CLI) or annotate=False (API), every action body is preceded by a single provenance line:

-- source: <source-path>:<line> (<ORIGINAL KIND> converted by sql2sqlx v<version>)

• <source-path> is the input file path, relative to the conversion root.

• <line> is the 1-based line of the original statement.

• <ORIGINAL KIND> is the leading keyword(s) of the original statement, e.g. CREATE TABLE, MERGE, INSERT, or SCRIPT for a whole-file script.

Declarations carry no body and therefore no provenance comment.

The SQL body and source fidelity

The single most important guarantee sql2sqlx makes about the body is:

Untouched SQL is preserved character-for-character after decoding.

Bodies are not re-serialized from a parse tree. Instead the emitter takes a slice of the original source text and applies a small set of span edits

• surgical (start, end, replacement) replacements at exact character offsets. The only things that change are:

• table references rewritten to ${ref(...)};

• the statement’s own target rewritten to ${self()} (only for elected hasOutput operations);

• select-list aliases inserted for a column-list INSERT -> incremental rewrite;

• constant SQLX placeholders that neutralize literal ${ text and SQLX-unsafe comments (see below).

Everything else - your capitalization, indentation, inline comments, string literals, trailing whitespace inside the statement - survives exactly. One cosmetic normalization is applied to the body as a whole: exactly one trailing semicolon is stripped (Dataform runs the body as a script, and a trailing empty statement is at best noise).

Generated files are always written as UTF-8 with \n newlines.

Reference and self forms

A rewritten reference carries exactly the qualification the producer was declared with:

Producer declared as	Emitted reference
CREATE TABLE t ...	${ref("t")}
CREATE TABLE ds.t ...	${ref("ds", "t")}
CREATE TABLE `proj`.ds.t ...	${ref({database: "proj", schema: "ds", name: "t"})}

An elected hasOutput operation has its own target path replaced with ${self()}, so the verbatim DDL still writes to the Dataform-managed object:

CREATE SNAPSHOT TABLE ${self()} CLONE analytics.users

Literal ${ and SQLX-unsafe comments

Dataform interprets ${ ... } in a .sqlx file as a JavaScript placeholder. If your SQL contains a literal ${ - inside a string literal, a backtick-quoted identifier, or a quoted parameter - it would be mis-interpreted by the Dataform compiler, and Dataform provides no SQL-level backslash escape for it. sql2sqlx therefore rewrites such literals through a constant placeholder that evaluates back to the exact original text:

-- input:   SELECT '${literal}' AS v
-- output:  SELECT ${"'${literal}'"} AS v

The replacement ${"'${literal}'"} is a placeholder whose JavaScript value is the original string '${literal}', so the compiled SQL is unchanged. References inserted by sql2sqlx (${ref(...)}, ${self()}) are of course left active.

Two GoogleSQL comment forms are also lexical hazards in a .sqlx file, and are normalized without changing BigQuery semantics:

• # line comments are rewritten to --. GoogleSQL treats # and -- as equivalent single-line comments, but the Dataform SQLX lexer does not recognize #, so # ... text could be mis-parsed.

• Separator-looking comments - a line whose comment text is exactly --- (with only surrounding horizontal whitespace) - are rewritten to -- ---. Dataform treats a line of exactly three dashes as an operations statement separator; prefixing -- keeps it a comment in both engines.

These rewrites are semantics-preserving in BigQuery and only exist so the generated file compiles correctly under Dataform.

Output file names and layout

Each action is written to <stem>.sqlx, where <stem> is derived from the target table name (or, for scripts and unnamed actions, the source file stem). Names are sanitized to the character class [A-Za-z0-9_]; Windows reserved device names are prefixed with _, and very long names are truncated with a short hash suffix to stay filesystem-safe. Collisions within a run are resolved deterministically by appending _2, _3, …

For directory conversions the --layout option controls placement:

• mirror (default) reproduces the input directory structure under the output root, so staging/a.sql becomes staging/a.sqlx;

• flat writes every .sqlx directly into the output root.

Synthesized declarations (from --declare-external) are always written under sources/.

Determinism

For a given input tree and a given set of options, sql2sqlx produces byte-identical output, no matter how many worker processes ran the parse phase. Action ordering, name-collision resolution and cross-file writer chains are all resolved from a stable corpus order (sorted relative path, then statement position), so re-running the tool in CI never produces a spurious diff.