Conversion rules

The governing principle:

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

An operations action executes the original SQL unchanged, so the fallback path can never alter behavior - it only forgoes idiomatic Dataform structure. This page is the exhaustive reference for how each statement class maps, the proof obligations for the opt-in strategies, how metadata is translated, and what every warning code means.

For the shape of what is produced (config block, provenance, source fidelity), see core concepts. For how references and ordering are wired, see the dependency model.

Statement mapping

Input statement	Result	Notes
CREATE [OR REPLACE] TABLE x ... AS query	type: "table"	PARTITION BY / CLUSTER BY / OPTIONS mapped to config. A non-OR REPLACE create still becomes a table and reports CREATE_REPLACE_SEMANTICS (Dataform rebuilds rather than erroring on an existing target).
CREATE TABLE IF NOT EXISTS x AS query	operations (default)	--if-not-exists table opts into a typed action and reports the lost guard (IF_NOT_EXISTS).
CREATE TABLE x AS with a column list	operations	Column DDL can carry types/constraints the table type cannot express (COLUMN_DDL).
CREATE TEMP TABLE	operations	No Dataform equivalent (TEMP_TABLE).
CREATE TABLE ... LIKE/CLONE/COPY	operations	No typed equivalent (FALLBACK_OPERATIONS, with the specific form in the message).
CREATE TABLE x (schema) (no AS)	operations (default) or declaration	--plain-create declaration drops the DDL and declares the source (DECLARATION_DROPPED_DDL); the default reports CREATE_NO_AS and, without a re-run guard, RERUN_RISK.
CREATE EXTERNAL TABLE / SNAPSHOT	operations	Tracked as a writer of the target (EXTERNAL_TABLE / SNAPSHOT_TABLE); eligible for hasOutput/${self()}.
CREATE [OR REPLACE] VIEW x AS query	type: "view"	Guarded views stay operations by default; view column lists are pushed into the select list as aliases.
CREATE MATERIALIZED VIEW	type: "view", materialized: true	MV options (e.g. enable_refresh) pass through bigquery.additionalOptions.
INSERT [INTO] x SELECT ...	operations (default)	See below; --insert-strategy incremental opts into typed conversion.
INSERT ... VALUES	operations	No query body (INSERT_VALUES).
MERGE	operations (default)	--merge-strategy incremental-when-safe converts shape-proven MERGEs to incremental + uniqueKey and reports the required target-schema check.
UPDATE / DELETE / TRUNCATE	operations	Write target tracked for dependency chaining.
DROP / ALTER (table/view)	operations	Write target tracked.
LOAD DATA INTO/OVERWRITE	operations	Write target tracked.
CREATE / DROP / ALTER SEARCH|VECTOR INDEX ... ON t	operations	Kept verbatim; the affected table t is parsed from the ON clause and tracked as a write target so the DDL is ordered after t and after earlier mutations of t (INDEX_DDL). Never elected as t’s owner.
CREATE / DROP ROW ACCESS POLICY ... ON t, DROP ALL ROW ACCESS POLICIES ON t	operations	Same handling as index DDL (ROW_ACCESS_POLICY_DDL); table reads inside a FILTER USING (...) subquery are still rewritten to ${ref(...)}.
GRANT / REVOKE ... ON TABLE|VIEW|EXTERNAL TABLE ... t	operations	Table-scoped grants track t as a write target so they are ordered after it (GRANT_REVOKE_DCL). Grants on non-table resources (e.g. ON SCHEMA) have no table action and stay standalone.
Bare SELECT / WITH	operations + ORPHAN_SELECT	Review: table, view or assertion?
File requiring shared script context	one whole-file operations action	Transactions, temporary objects, variables, procedural blocks and dynamic side effects stay together (SCRIPT_FILE).
Everything else (CALL, EXPORT DATA, procedures, …)	operations	Verbatim.

Worked example: CREATE ... AS with metadata

CREATE OR REPLACE TABLE analytics.daily_sales
PARTITION BY DATE(order_ts)
CLUSTER BY region, product_id
OPTIONS(description = 'Daily sales rollup', require_partition_filter = true)
AS
SELECT region, product_id, SUM(amount) AS total
FROM raw.orders
GROUP BY region, product_id;

becomes:

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

Note how PARTITION BY DATE(order_ts) is preserved as the raw expression text, CLUSTER BY becomes a string array, and the description option is promoted to a top-level config key.

INSERT -> incremental: explicit migration strategy

Dataform’s incremental type appends this query’s rows on normal runs, but creates the table from the query on first run and may rebuild it during a full refresh. Since that is not equivalent to an imperative INSERT in every environment, the semantics-preserving default is operations. When the user explicitly selects --insert-strategy incremental, sql2sqlx:

• emits protected: true by default (disable with --no-protected) so an accidental full refresh cannot drop pre-existing rows;

• records INSERT_INCREMENTAL suggesting ${when(incremental(), ...)} around date filters;

• when the INSERT has a column list, rewrites the select list so its output names match (INSERT INTO t (a) SELECT x -> SELECT x AS a) - and only when every item’s output name is exactly determinable at the token level. * expansion, SELECT AS STRUCT, ambiguous INTERVAL 1 DAY items or arity mismatches trigger the operations fallback (FALLBACK_SELECT_ALIAS) instead of a guess;

• rejects alias changes when a later GROUP BY, HAVING, QUALIFY, ORDER BY or pipe stage references the old alias.

• requires every incremental query output name to be exactly derivable, unique (case-insensitively), and safe for Dataform’s runtime backtick quoting. An unexpanded *, an anonymous expression, duplicate names, or an identifier containing a decoded backtick/backslash/control character falls back instead of relying on warehouse metadata guesses.

For example, with --insert-strategy incremental:

INSERT INTO analytics.events (event_id, ts)
SELECT id, created_at FROM raw.events;

converts to (note the select list is aliased to the target column names):

config {
  type: "incremental",
  schema: "analytics",
  name: "events",
  protected: true
}

-- source: input.sql:1 (INSERT converted by sql2sqlx v0.1.0)
SELECT id AS event_id, created_at AS ts FROM raw.events

On later runs Dataform projects every column in the existing target metadata by name from the incremental query. The INSERT_INCREMENTAL warning therefore requires the operator to verify that the query output matches the complete target schema; an original positional/subset INSERT does not establish that fact. This strategy is explicitly opt-in because its first-run/full-refresh behavior and this schema contract are not equivalent to an imperative INSERT in every environment.

MERGE -> incremental proof obligations and schema precondition

With --merge-strategy incremental-when-safe, a MERGE converts only when all of the following hold (otherwise: operations + MERGE_FALLBACK with the exact reason):

1. the source is an aliased subquery;

2. ON is a pure conjunction of same-named target.k = source.k equalities;

3. exactly WHEN MATCHED THEN UPDATE SET with only direct same-named col = source.col assignments (no conditions, no expressions), including every unique-key column because Dataform updates those columns too;

4. exactly WHEN NOT MATCHED [BY TARGET] THEN INSERT with an explicit matching column/value list (INSERT ROW is order-dependent and rejected);

5. the subquery’s derivable output columns are exactly the key columns plus the updated columns, in the explicit INSERT column order;

6. every output/key name is a simple, unreserved identifier because Dataform emits unique-key and source-side update references without backticks.

A MERGE that satisfies all six:

MERGE analytics.dim_users T
USING (SELECT id, name, email FROM raw.users) S
ON T.id = S.id
WHEN MATCHED THEN UPDATE SET id = S.id, name = S.name, email = S.email
WHEN NOT MATCHED THEN INSERT (id, name, email) VALUES (S.id, S.name, S.email);

converts to an incremental with a uniqueKey, and emits two warnings - the conversion note and the schema precondition:

config {
  type: "incremental",
  schema: "analytics",
  name: "dim_users",
  protected: true,
  uniqueKey: ["id"]
}

-- source: input.sql:1 (MERGE converted by sql2sqlx v0.1.0)
SELECT id, name, email FROM raw.users

Under those conditions the MERGE that Dataform generates for type: "incremental" + uniqueKey has identical row-level effects when the existing target’s columns exactly match the source output. Dataform builds the generated assignments from live target metadata, which SQL text alone cannot prove. Every conversion therefore also reports TARGET_SCHEMA_REQUIRED; verify the complete target column set before its first incremental run. The default operations strategy has no such precondition.

Common reasons a MERGE stays operations (MERGE_FALLBACK): the UPDATE SET omits a unique-key column, uses a condition or an expression; the ON clause is not a pure same-named equality conjunction; INSERT ROW is used; or the source is not an aliased subquery.

Metadata mapping

SQL	Dataform config
PARTITION BY expr	bigquery.partitionBy (raw expression text)
CLUSTER BY a, b	bigquery.clusterBy: ["a", "b"]
OPTIONS(description = '...')	description
OPTIONS(labels = [("k","v")])	bigquery.labels
OPTIONS(partition_expiration_days = n)	bigquery.partitionExpirationDays
OPTIONS(require_partition_filter = b)	bigquery.requirePartitionFilter
any other option, or a non-literal value	bigquery.additionalOptions with the raw SQL text preserved
view column OPTIONS(description)	config.columns

Options that do not have a dedicated Dataform key - or whose value is a non-literal expression - are preserved verbatim under bigquery.additionalOptions as raw SQL text, so nothing is silently dropped. For instance a materialized view:

CREATE MATERIALIZED VIEW mart.mv
OPTIONS(enable_refresh = true, refresh_interval_minutes = 60)
AS SELECT k, COUNT(*) AS n FROM raw.t GROUP BY k;

produces:

config {
  type: "view",
  schema: "mart",
  name: "mv",
  materialized: true,
  bigquery: {
    additionalOptions: {
      enable_refresh: "true",
      refresh_interval_minutes: "60"
    }
  }
}

References and dependencies

These rules are summarized here and covered in depth, with examples, in the dependency model.

• Every read of a table that some statement produces becomes ${ref(...)}, carrying the producer’s own qualification (ref("t"), ref("schema","t"), or the object form with database).

• Reads additionally gain dependencies on the latest writer that precedes the reader, so a future mutation is never pulled before an earlier read.

• Within one source file, a later writer also depends on readers since the prior write, preserving read-before-write order under parallel scheduling.

• If a read occurs before the table’s eventual owner, it stays literal and reports FUTURE_CREATOR; a generated ref() would reverse corpus order.

• Writers of the same table are chained in corpus order (sorted file path, then position); cross-file chains raise ORDER_ASSUMED.

• Only operations that actually create a table/view can be elected with hasOutput: true and ${self()}. Mutating DML is never misdeclared as a producer; --declare-external can declare an existing ownerless target.

• Duplicate producers of one target are demoted to ordered verbatim operations (DUPLICATE_TARGET) - Dataform allows one owner per target. Demotion also drops the abandoned typed-conversion warnings (for example INSERT_INCREMENTAL) so the report describes the operations action that was actually emitted.

• Resource-attached DDL/DCL (CREATE/DROP/ALTER SEARCH|VECTOR INDEX, CREATE/DROP ROW ACCESS POLICY, table-scoped GRANT/REVOKE) is kept verbatim but tracked as a writer of the affected table, so it is ordered after that table’s creator and after earlier mutations of the same table. Such statements are never elected as the table’s owner.

• CTE names, table aliases (including alias.column paths), UNNEST, table-valued functions, EXTRACT(... FROM ...) and the statement’s own target are never rewritten.

• --declare-external synthesizes type: "declaration" files under sources/ for referenced-but-never-produced tables (INFORMATION_SCHEMA, region-*, wildcard tables and table decorators excluded) and refs them too. Unqualified sources are declarable when --default-dataset resolves them.

• A reference or manual ordering edge that would create a cycle is omitted, the table path remains literal, and DEPENDENCY_CYCLE is reported.

Warning codes

Every warning is a stable code plus a specific message and source location. Gate and filter on the code. See the report reference for the JSON shape and a triage workflow.

Code	Meaning
FALLBACK_OPERATIONS	Statement kept verbatim; reason in message
FALLBACK_SELECT_ALIAS	Column-list rewrite not provably safe
COLUMN_DDL	CTAS with typed column list
TEMP_TABLE / EXTERNAL_TABLE / SNAPSHOT_TABLE	Special CREATE forms kept as operations
INDEX_DDL / ROW_ACCESS_POLICY_DDL	Index or row-access-policy DDL kept verbatim and ordered after its ON table
GRANT_REVOKE_DCL	Table-scoped GRANT/REVOKE kept verbatim and ordered after its table
IF_NOT_EXISTS	Create-if-absent guard lost (or preserved per option)
CREATE_REPLACE_SEMANTICS	Typed Dataform action rebuilds a non-OR REPLACE target on later runs
CREATE_NO_AS	Plain CREATE TABLE kept as operations
RERUN_RISK	Plain CREATE without OR REPLACE / IF NOT EXISTS fails on re-run
DECLARATION_DROPPED_DDL	Plain CREATE emitted as a declaration
INSERT_VALUES	INSERT without a query body
INSERT_INCREMENTAL	Semantics note for the incremental conversion
MERGE_INCREMENTAL / MERGE_FALLBACK	MERGE shape result / fallback reason
TARGET_SCHEMA_REQUIRED	Converted MERGE requires exact target/query schema compatibility
PROCEDURE_PRESERVED	Stored procedure body kept verbatim and not treated as immediately executed DML
DYNAMIC_SIDE_EFFECTS	CALL or dynamic SQL may need manually declared dependencies
ORPHAN_SELECT	Standalone query needs review
SCRIPT_FILE / SCRIPT_WRITES	Whole-file script preserved; targets it writes
SELF_REFERENCE	Statement reads its own target; left literal
DUPLICATE_TARGET	Second producer demoted to operations
ORDER_ASSUMED	Multi-file writer order inferred from sorted paths
FUTURE_CREATOR	Read left literal because its eventual owner occurs later in corpus order
DEPENDENCY_CYCLE	Dependency omitted and reference left literal to keep the Dataform graph acyclic