References and the dependency graph

Dataform executes actions as a DAG: a node runs only after the nodes it depends on. sql2sqlx reconstructs that graph from plain SQL by answering two questions for every statement:

  1. Which tables does it read, and are any of those produced elsewhere in the corpus? Each such read becomes a ${ref(...)} call.

  2. Which tables does it write, and in what order relative to other statements? That ordering becomes dependencies edges.

This page explains exactly how those edges are derived, and how to read the warnings that flag the cases where SQL text alone is not enough to be sure.

Producers, writers and readers

Every statement is classified by its relationship to a target table:

Producer (creator)

A statement that creates or replaces a table or view - CREATE [OR REPLACE] TABLE/VIEW ... AS, and an INSERT/MERGE you have opted to convert to incremental. Dataform allows exactly one owner per target; the producer is that owner.

Writer (mutator)

A statement that changes an existing table without owning it - UPDATE, DELETE, TRUNCATE, MERGE/INSERT kept as operations, DROP, ALTER, LOAD DATA, and so on. These stay verbatim operations actions but are still tracked as writers of their target so ordering is preserved.

Reader

A statement that reads a table in a FROM/JOIN/USING position.

Reference rewriting

When a reader reads a table that some producer in the corpus owns, the read site is rewritten to a ${ref(...)} call carrying the producer’s own qualification (see core concepts). Dataform derives an implicit dependency on the owner from the ref() itself, so no explicit dependencies entry is needed just to depend on the owner.

${ref(...)} is emitted only for tables the corpus actually produces. A read of a table nobody produces is left as a literal path (it is an external source). Pass --declare-external to turn such sources into declaration actions and rewrite those reads to ref() too (see below).

What is never rewritten

The reference scanner tracks query scope so that only genuine physical table paths are rewritten. The following are deliberately left untouched, because they are not references to a corpus table:

  • CTE names and their uses (WITH t AS (...) SELECT * FROM t);

  • table aliases and alias-qualified column paths (a.col where a is a range variable);

  • UNNEST(...) operands and their aliases;

  • table-valued function calls and TABLE table_path arguments inside table functions (e.g. ML.PREDICT(MODEL m, TABLE d.t));

  • EXTRACT(part FROM col) - the FROM here is not a table source;

  • a statement’s own target (that is handled by ${self()}, not ref());

  • INFORMATION_SCHEMA views, region-* meta tables, wildcard tables (`ds.events_*`) and table decorators (`ds.t$20240101`).

The scanner understands recursive vs. ordered CTE visibility, correlated scalar subqueries vs. non-lateral FROM subqueries, set-operation branches (UNION/INTERSECT/EXCEPT), implicit aliases and dashed-project adjacency, all without ever re-serializing the SQL.

Ordering dependencies

${ref()} handles “read the current contents of an owned table”. Everything else about ordering is expressed with explicit dependencies entries, whose values are:

  • the owner’s qualified path (analytics.t, or proj.ds.t) when the dependency is on a producer, matching what a ref() resolves to; or

  • the depended-on action’s name (t_delete) when it is a non-owner writer or a script.

Three rules build these edges. All of them operate over a stable corpus order: files sorted by relative path, then statements by position.

1. Writer chains

Every writer of a table depends on the previous writer of that same table, so a create/mutate/mutate sequence keeps its original order:

CREATE OR REPLACE TABLE analytics.t AS SELECT 1 AS id;
DELETE FROM analytics.t WHERE id = 0;

The DELETE becomes an operations action that depends on the table it mutates:

config {
  type: "operations",
  name: "t_delete",
  dependencies: ["analytics.t"]
}

2. Read-before-write ordering

Within one file, a later writer also depends on every reader that read the table since the previous write. This preserves “read the old value, then mutate” order even under Dataform’s parallel scheduling:

CREATE OR REPLACE TABLE analytics.src AS SELECT 1 AS id;   -- producer
CREATE OR REPLACE VIEW  analytics.rpt AS
  SELECT * FROM analytics.src;                             -- reader
DELETE FROM analytics.src WHERE id = 0;                    -- later writer

The DELETE waits for both the producer and the intervening reader:

config {
  type: "operations",
  name: "src_delete",
  dependencies: ["analytics.rpt", "analytics.src"]
}

while rpt reads ${ref("analytics", "src")} and depends on src implicitly through that ref().

3. Reader dependencies on the latest writer

A reader depends (beyond the owning ref()) on the latest writer that precedes it, so a read never floats ahead of a mutation that was written before it. In the writer-chain example above, if a view read analytics.t after the DELETE, it would gain dependencies: ["t_delete"] in addition to ${ref("analytics", "t")}.

Edges the tool refuses to invent

SQL text cannot always prove a safe edge exists. In these cases sql2sqlx chooses the conservative option - leave the reference literal and/or omit the edge - and records a warning rather than emitting a graph that would reorder your pipeline or fail to compile.

FUTURE_CREATOR

A read occurs before the table’s eventual owner in corpus order. Rewriting it to ref() would pull that future creator ahead of the read and reverse your intended order, so the read stays literal. (The owner still gains an ordering dependency on the earlier reader.)

CREATE OR REPLACE VIEW analytics.early AS
  SELECT * FROM analytics.late;      -- read stays literal (FUTURE_CREATOR)
CREATE OR REPLACE TABLE analytics.late AS SELECT 1 AS id;
SELF_REFERENCE

A statement reads its own target inside its defining query. Dataform cannot ref() an action into itself, so the reference is left literal.

DUPLICATE_TARGET

A second statement produces a table another statement already owns. The duplicate is demoted to a verbatim operations action, ordered after the owner, because Dataform permits only one owner per target.

DEPENDENCY_CYCLE

An edge (a ref() or a manual ordering edge) would introduce a cycle into the Dataform graph. The unsafe edge is omitted and the path left literal, so the compiled graph stays acyclic.

ORDER_ASSUMED

Several different files write the same table. Their relative order is inferred from sorted file paths - a heuristic, because cross-file execution order is not encoded in the SQL itself. Review these if your build order depended on something other than lexical file order.

hasOutput election

Some statements create a real table but cannot be expressed as a typed table/view action - for example CREATE SNAPSHOT TABLE ... CLONE, CREATE TABLE ... LIKE, or a guarded CREATE TABLE IF NOT EXISTS ... AS kept as operations. When such an operations action is the sole creator of its target, it is elected as a Dataform producer: it gains hasOutput: true and its own target path is rewritten to ${self()}, so downstream ref() calls resolve to it and it participates in the graph like any other producer.

config {
  type: "operations",
  schema: "backups",
  name: "users_snap",
  hasOutput: true
}

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

Mutating DML (UPDATE/DELETE/MERGE/INSERT) is never elected as a producer, even when it targets an ownerless table - a mutation is not a creator, and mis-declaring it as one would corrupt the graph.

Name resolution and defaults

References are matched to producers by resolved identity (project, dataset, table). --default-project and --default-dataset fill in missing qualifiers for matching purposes only:

  • An unqualified FROM t matches an unqualified producer t, or - when --default-dataset ds is set - a producer declared as ds.t.

  • Defaults never appear in a target’s config identity (database/schema are emitted only from explicit source qualification), but they do let a qualified producer and an unqualified read resolve to the same table.

Physical-name ambiguity that defaults cannot resolve is left literal; supply a more specific default or qualify the source path.

--declare-external: sources

By default a table nobody in the corpus produces is left as a literal path. With --declare-external, sql2sqlx synthesizes a type: "declaration" action under sources/ for each referenced-but-never-produced table and rewrites those reads to ref() as well:

config {
  type: "declaration",
  schema: "raw",
  name: "events"
}

Only genuinely declarable sources are emitted. INFORMATION_SCHEMA views, region-* meta tables, wildcard tables and table decorators are excluded (they are table expressions, not declarable objects). An unqualified source is declarable only when --default-dataset resolves it to a concrete dataset.