Dataform for BigQuery: Google-Native Transformation, Assertions & CI/CD

Dataform is the piece that turns a pile of ad-hoc BigQuery SQL scripts into a versioned, tested, dependency-aware pipeline — a set of .sqlx files where each file is a single SELECT plus a small config block, compiled into the exact CREATE OR REPLACE TABLE/VIEW statements BigQuery runs, wired together into a dependency graph the framework builds for you from the ref() calls in your queries. The hard problem in analytics was never writing one transform; it was the sprawl. Once a team has two hundred scheduled queries, nobody knows the run order, nobody notices when an upstream table silently changes shape, and every "quick fix" risks breaking a downstream report three hops away that no one remembers depends on it.

This guide is the practitioner's walkthrough of the Google-native way to close that gap — building the in-warehouse transformation layer as a governed, Git-backed project instead of a folder of scheduled queries — framed the way an interviewer or a design review actually probes it: what Dataform is now that it lives inside the BigQuery console as a managed Google Cloud service, how SQLX and the dependency graph replace hand-ordered scripts, how assertions give you data quality checks that gate the graph, how CI/CD with dev workspaces, release configs, and scheduled workflows takes a change from a pull request to production safely, and how Dataform compares to dbt on BigQuery so you can pick deliberately. Each section pairs a teaching block with a worked answer — code, a step-by-step trace, an output table, then a concept-by-concept breakdown of why it works.

When you want hands-on reps immediately after reading, drill the data transformation practice library →, harden your checks on the data validation practice library →, and sharpen the pipeline-architecture axis with the system design practice library →.

---

On this page

• What Dataform is — BigQuery-native ELT and SQLX
• SQLX and the dependency graph
• Assertions — data quality inside the graph
• CI/CD — Git, release configs, and workflow schedules
• Dataform vs dbt on BigQuery
• Cheat sheet — Dataform recipes
• Frequently asked questions
• Practice on PipeCode

---

1. What Dataform is — BigQuery-native ELT and SQLX

Dataform is BigQuery-native ELT — SQLX files compile to SQL and the graph runs in your warehouse

The one-sentence invariant: Dataform is a transformation framework for BigQuery in which every model is a .sqlx file — a single SELECT statement wrapped in a config block — that Dataform compiles into the concrete CREATE OR REPLACE TABLE/VIEW/MERGE SQL BigQuery executes, resolving each ref() call into both a fully-qualified table name and an edge in a dependency graph, so instead of hand-ordering scheduled queries you declare what each table selects from and Dataform derives when to build it. It is ELT, not ETL: raw data is already loaded into BigQuery, and the entire transformation runs inside BigQuery — Dataform never moves data through its own compute, it only orchestrates the SQL.

What Dataform actually is.

• A compiler, not an engine. Dataform does not process rows. It compiles your SQLX project into BigQuery SQL and issues those statements to BigQuery, which does all the work. Your cost is BigQuery cost; the framework itself is free.
• SQLX = SQL + config. A SQLX file is ordinary BigQuery Standard SQL plus a config { } header (and optional js { } / pre_operations / post_operations blocks) that declare the object's type, destination, tags, documentation, and inline tests.
• A graph over your project. The set of all ref()/dependencies relations across your files is a directed acyclic graph; Dataform topologically sorts it so every model builds only after its inputs.
• Managed or open-source. The framework is open source (@dataform/core, the dataform CLI); Google also runs it as a managed service so you get scheduling, Git, and IAM without hosting anything.

Where Dataform lives now — a first-class Google Cloud service.

• Inside the BigQuery console. Dataform is part of BigQuery in the Google Cloud console: you create a repository, open a workspace, and edit SQLX with compilation and execution against your own datasets — no separate product to deploy.
• Git-backed by default. A repository connects to GitHub, GitLab, Azure DevOps, or Cloud Source Repositories; your SQLX project is a Git repository.
• IAM-integrated. Execution runs as a Google service account with normal BigQuery IAM, so access control is the same model as the rest of your GCP estate.
• Free managed service. You pay for the BigQuery jobs Dataform triggers; the orchestration, scheduling, and Git integration carry no separate Dataform charge.

The core object types you declare.

• Declarations describe existing source tables (raw ingested data) so you can ref() them without Dataform managing them.
• Views / tables / incremental tables are the models Dataform builds — type: "view", type: "table", or type: "incremental".
• Assertions are data-quality checks (type: "assertion" or inline in a model's config) that must return zero rows to pass.
• Operations (type: "operations") are arbitrary SQL statements (grants, DDL, calls) for things that are not a single SELECT.
• Includes are reusable JavaScript (includes/*.js) — constants and functions the SQLX can call to templatise SQL.

What interviewers and design reviews probe.

• Do you explain ELT in the warehouse — raw lands in BigQuery, the T runs in BigQuery — rather than describing a row-moving ETL tool? — foundational signal.
• Do you say ref() builds the dependency graph so run order is derived, not hand-maintained? — required answer.
• Do you know Dataform is BigQuery-native and free-managed (you pay BigQuery, not Dataform) and now lives inside the console? — senior signal.
• Do you distinguish declarations (sources) from models (built objects) and know assertions are zero-row checks? — required answer.
• Do you frame a Dataform project as version-controlled, tested, and IAM-governed, not a folder of scheduled queries? — senior signal.

Worked example — a first SQLX model with config, ref, and a SELECT

Detailed explanation. The atom of a Dataform project is one SQLX file: a config block that tells Dataform what to build and where, and a SELECT that tells it what the data is. Build a stg_orders staging view over a raw declared source and read the SQL Dataform actually runs.

• The source. A raw ingested table raw.orders, described to Dataform with a declaration so it can be ref()-ed.
• The model. stg_orders — a view that cleans and renames columns.
• The compile. ${ref("raw_orders")} becomes the fully-qualified project.dataset.orders, and the whole file becomes a CREATE OR REPLACE VIEW.

Question. Write the SQLX for a stg_orders staging view over a declared raw_orders source, and show the BigQuery SQL it compiles to.

Input.

Piece	Value
Source declaration	raw_orders → project.raw.orders
Model type	type: "view"
Destination dataset	staging
Reference	${ref("raw_orders")}

Code.

-- definitions/sources/raw_orders.sqlx — a DECLARATION of an existing source table.
config {
  type: "declaration",
  database: "my-project",
  schema: "raw",
  name: "orders"
}

-- definitions/staging/stg_orders.sqlx — a VIEW model that cleans the source.
config {
  type: "view",
  schema: "staging",
  description: "One clean row per order, typed and renamed.",
  columns: {
    order_id:    "Primary key of an order",
    customer_id: "FK to customers",
    revenue:     "Order total in dollars"
  }
}

SELECT
  CAST(id AS INT64)                 AS order_id,
  CAST(cust_id AS INT64)           AS customer_id,
  SAFE_DIVIDE(total_cents, 100)    AS revenue,
  TIMESTAMP(created)               AS created_at
FROM ${ref("raw_orders")}          -- resolves to `my-project.raw.orders` AND adds a graph edge
WHERE total_cents IS NOT NULL

Step-by-step explanation.

1. The declaration file registers raw_orders as a source Dataform does not build — it only needs the fully-qualified name so ref("raw_orders") can resolve to my-project.raw.orders.
2. The stg_orders config block sets type: "view" and schema: "staging", so Dataform knows to emit a view into the staging dataset; description and columns become documentation surfaced in the compiled graph and BigQuery.
3. The SELECT is ordinary BigQuery SQL. The only Dataform-specific token is ${ref("raw_orders")}, which the compiler replaces with the backtick-quoted fully-qualified name and records as an edge raw_orders → stg_orders.
4. Because the model is a view, Dataform compiles the file into CREATE OR REPLACE VIEW \my-project.staging.stg_ordersAS SELECT ... — no data is copied; the view re-reads the source on query.
5. You never hard-code my-project.raw.orders in the SQL. Using ref() means the name is resolved per environment (dev vs prod dataset) and the dependency is tracked — the two things a raw table name in a string cannot do.

Output.

What you wrote	What Dataform runs in BigQuery
type: "view"	CREATE OR REPLACE VIEW my-project.staging.stg_orders
${ref("raw_orders")}	`my-project.raw.orders` (+ graph edge)
columns { ... }	column descriptions attached to the object
the SELECT body	the view definition, verbatim

Rule of thumb. A SQLX model is a config block plus one SELECT; always read upstream tables through ref() (never a hard-coded name) so Dataform resolves the environment-correct name and records the dependency. Declare raw sources once; build everything else as views, tables, or incrementals.

Worked example — declarations vs models and what ref() compiles to

Detailed explanation. The distinction that trips people up is declaration versus model: a declaration is a promise "this table already exists, don't build it," while a model is something Dataform creates. Both are ref()-able, but only models get an execution node. Trace how ref() resolves differently for each and why hard-coded names break.

• Declaration. raw_orders — Dataform manages nothing, just resolves the name.
• Model. stg_orders — Dataform builds it and schedules it after its refs.
• The failure it prevents. A hard-coded my-project.raw.orders string has no edge, so Dataform may build a dependent before its input.

Question. Show why ref() on a declaration and ref() on a model both resolve to a name but only the model creates a graph node, and what goes wrong with a hard-coded name.

Input.

Object	Kind	Dataform builds it?	Adds graph node?	ref()-able?
raw_orders	declaration	no	no (leaf source)	yes
stg_orders	view model	yes	yes	yes
'my-project.raw.orders'	hard-coded string	n/a	no edge	no tracking

Code.

-- A model that refs BOTH a declaration and another model.
config { type: "table", schema: "marts" }

SELECT
  o.order_id,
  o.revenue,
  c.segment
FROM ${ref("stg_orders")}     AS o     -- ref a MODEL  -> edge stg_orders -> this
JOIN ${ref("dim_customers")}  AS c      -- ref a MODEL  -> edge dim_customers -> this
  ON o.customer_id = c.customer_id
-- ANTI-PATTERN (do NOT do this): FROM `my-project.staging.stg_orders`
--   no edge is recorded, so Dataform may run THIS before stg_orders exists.

-- What Dataform compiles the two refs into (names resolved, edges recorded):
CREATE OR REPLACE TABLE `my-project.marts.fct_customer_orders` AS
SELECT o.order_id, o.revenue, c.segment
FROM `my-project.staging.stg_orders`     AS o
JOIN `my-project.marts.dim_customers`    AS c
  ON o.customer_id = c.customer_id;

Step-by-step explanation.

1. ref("stg_orders") and ref("dim_customers") each resolve to a fully-qualified name and register an edge, so Dataform's graph now knows this table must build after both inputs.
2. The declaration raw_orders (referenced transitively via stg_orders) is a graph leaf: Dataform never builds it, it just anchors the fully-qualified name so the resolution works.
3. At compile time every ${ref(...)} is textually replaced with the backtick-quoted name, producing the plain CREATE OR REPLACE TABLE ... AS SELECT you see — the model is a normal BigQuery job once compiled.
4. The commented anti-pattern — a hard-coded `my-project.staging.stg_orders` — resolves to the same name but records no edge. Dataform would be free to schedule this table before stg_orders is built, producing a "table not found" or a stale read.
5. This is the whole value proposition of ref(): it couples name resolution and dependency tracking, so the run order is derived from the SQL itself and can never drift out of sync with a hand-maintained schedule.

Output.

Reference style	Name resolved	Edge recorded	Safe run order
ref("stg_orders")	yes (env-aware)	yes	guaranteed
ref("raw_orders") (declaration)	yes	leaf source	guaranteed
hard-coded `...staging.stg_orders`	yes (fixed)	no	not guaranteed
typo ref("stg_order")	compile error	—	caught at compile

Rule of thumb. Declare every raw source once and reference everything through ref() so name resolution and dependency tracking stay coupled. A hard-coded table name compiles fine but silently drops the edge — the single most common way a Dataform graph runs in the wrong order.

Worked example — choosing view, table, or incremental

Detailed explanation. Every model has a type, and the choice is a cost/freshness trade-off: a view stores no data but recomputes on every read, a table materialises the result on each run, and an incremental table appends or merges only new rows. Pick the type for a small dimension, a medium mart, and a large event fact.

• View. No storage, always fresh, cost paid by the reader. Best for light transforms and small data.
• Table. Materialised each run, cheap to read, recompute cost paid at build. Best for medium marts read often.
• Incremental. Only new rows processed each run. Best for large append-mostly event tables.

Question. Assign view, table, or incremental to a customer dimension, a daily-sales mart, and a raw event fact, and justify each by cost and freshness.

Input.

Model	Size / pattern	Read frequency	Type
dim_customers	small, changes slowly	often	table
stg_events_raw	thin passthrough	rarely, ad-hoc	view
fct_events	huge, append-only	often	incremental

Code.

-- Small dim read often → TABLE (materialise once per run, cheap reads).
config { type: "table", schema: "marts" }
SELECT customer_id, segment, country FROM ${ref("stg_customers")};

-- Thin passthrough read rarely → VIEW (no storage, recompute on read).
config { type: "view", schema: "staging" }
SELECT * EXCEPT(_loaded_at) FROM ${ref("raw_events")};

-- Huge append-only fact → INCREMENTAL (process only new rows each run).
config {
  type: "incremental",
  schema: "marts",
  uniqueKey: ["event_id"],
  bigquery: { partitionBy: "DATE(event_ts)" }
}
SELECT event_id, user_id, event_ts, event_type
FROM ${ref("stg_events_raw")}
${ when(incremental(), `WHERE event_ts > (SELECT MAX(event_ts) FROM ${self()})`) }

Step-by-step explanation.

1. dim_customers is small but read by many downstream joins, so a table materialises it once per run and every reader pays only a cheap scan — recomputing it as a view on every read would be wasteful.
2. stg_events_raw is a thin passthrough that is rarely read directly, so a view stores nothing and simply forwards to the source; there is no materialisation cost for data almost nobody queries.
3. fct_events is huge and append-only, so incremental is the only economical choice: after the first full build, each run processes just the rows newer than what is already there.
4. The when(incremental(), ...) block is the incremental filter — it is included only on incremental runs (not the first full build), and self() refers to the table being built, so WHERE event_ts > (SELECT MAX(event_ts) FROM self) picks up only new events.
5. Partitioning fct_events by DATE(event_ts) means even the "max timestamp" probe and downstream reads prune to a few partitions — incrementality and partitioning together are what keep a large fact table cheap.

Output.

Type	Storage	Cost paid at	Freshness	Best for
view	none	read time	always live	thin/small, rare reads
table	full result	build time	last run	medium marts, frequent reads
incremental	full result	build (new rows only)	last run	large append-only facts

Rule of thumb. Default to table for marts read often, view for thin transforms or rarely-read passthroughs, and incremental for large append-mostly facts — pairing incremental with a partition column so both the incremental probe and downstream reads prune. The type is a cost/freshness decision, not a stylistic one.

Senior interview question on Dataform fundamentals

A senior interviewer often opens with: "Your team has 150 scheduled BigQuery queries with the run order maintained by hand in a spreadsheet, and last week a downstream dashboard broke because someone changed an upstream table nobody knew it depended on. Explain what Dataform is, how moving these queries into a SQLX project fixes the ordering and dependency problem, why it is ELT-in-the-warehouse rather than a data-movement tool, and how declarations, models, and ref() change the way run order is decided."

Solution Using declarations, ref-derived ordering, and typed SQLX models

-- 1. Declare the raw sources ONCE (Dataform manages nothing here, just names them).
config { type: "declaration", database: "my-project", schema: "raw", name: "orders" }

-- 2. Each transform is a typed SQLX model that reads upstreams via ref().
--    staging (view) -> mart (table) -> report (incremental) — order DERIVED from refs.
config { type: "view", schema: "staging" }
SELECT CAST(id AS INT64) AS order_id, cust_id AS customer_id, total_cents
FROM ${ref("orders")};                                 -- edge: orders -> stg_orders

config { type: "table", schema: "marts" }
SELECT customer_id, SUM(total_cents)/100 AS revenue
FROM ${ref("stg_orders")}                               -- edge: stg_orders -> fct_customer
GROUP BY customer_id;

config { type: "incremental", schema: "reports", uniqueKey: ["day"] }
SELECT DATE(created_at) AS day, SUM(revenue) AS revenue
FROM ${ref("fct_customer_revenue")}                     -- edge: fct -> daily_report
${ when(incremental(), `WHERE DATE(created_at) >= CURRENT_DATE() - 3`) }
GROUP BY day;

Step-by-step trace.

Before (150 scheduled queries)	After (Dataform SQLX project)
run order kept in a spreadsheet	order derived from ref() edges
dependencies invisible	dependency graph, visualised
upstream change breaks silently	edge + assertions surface it
table names hard-coded per query	ref() resolves per environment
no version control	Git-backed repository
ELT ambiguous	explicit ELT: T runs in BigQuery

After the migration, each of the 150 queries becomes a typed SQLX model that reads its inputs through ref(). Dataform assembles every edge into one directed acyclic graph, topologically sorts it, and runs each model only after its inputs — so the spreadsheet disappears and the ordering can never drift from the SQL. The raw tables are declared once as sources; everything else is a view, table, or incremental Dataform builds inside BigQuery. Because the whole project is a Git repository, the change that broke the dashboard would have shown up as an edge in a pull request.

Output:

Metric	Hand-scheduled queries	Dataform project
Run order source	manual spreadsheet	derived from the graph
Broken-dependency detection	in production	at compile + assertion time
Environment portability	rewrite table names	ref() resolves per env
Change review	none	Git pull request
Where transforms run	ambiguous	in BigQuery (ELT)

Why this works — concept by concept:

• ref-derived ordering — every ref() couples name resolution with a graph edge, so the run order is computed from the SQL itself and can never fall out of sync with a hand-maintained schedule.
• Declarations vs models — declaring raw sources once anchors their names as graph leaves, while models get build nodes, so Dataform knows exactly what it owns and what it merely reads.
• Typed SQLX — type: view/table/incremental makes each model's materialisation and cost explicit, turning 150 opaque scripts into a legible, tuned pipeline.
• ELT in BigQuery — Dataform compiles to SQL and BigQuery does the work, so there is no separate compute to size or data movement to debug — the transformation runs where the data already lives.
• Cost — one compile step and a topologically-sorted set of BigQuery jobs, versus a fragile spreadsheet and per-query name maintenance. The eliminated cost is an entire class of ordering and silent-dependency incidents — O(compile) to know the graph instead of O(incidents) to discover it.

Data transformation
Topic — data-transformation
Transformation problems on in-warehouse ELT modelling

Practice →

Design Topic — design Design problems on pipeline and dependency structure

Practice →

---

2. SQLX and the dependency graph

config, ref, and tags build the DAG — incremental tables make it cheap

The mental model in one line: a SQLX file's config block declares what to build (type, destination, tags, partitioning, inline assertions) while its ${ref(...)} calls declare what it depends on, and from every ref across the project Dataform assembles a single directed acyclic graph — the dependency graph — that it topologically sorts so each model builds after its inputs, with tags letting you run named subsets and incremental tables compiling to a MERGE/INSERT that touches only new rows instead of rebuilding the whole table. You describe the shape of each node and its inbound edges; Dataform derives the entire execution plan.

Inside the config block.

• Destination and type. type (view/table/incremental/assertion/operations), schema (dataset), and optional database (project) decide where the object lands.
• BigQuery physical options. bigquery: { partitionBy, clusterBy, labels } pushes partitioning and clustering into the compiled DDL — the levers that keep large tables cheap.
• Documentation. description and a columns { } map attach human-readable docs to the object and to the graph.
• Inline assertions. An assertions: { uniqueKey, nonNull, rowConditions } block generates data-quality checks on the model (covered in section 3).

How ref() builds the graph.

• Two jobs at once. ${ref("model")} returns the fully-qualified, backtick-quoted name and records an edge from that model to the current one.
• Topological sort. Dataform sorts the whole edge set so every node runs after its inputs; parallelism is derived from the graph's independent branches.
• Compile-time safety. A ref() to a name that does not exist is a compile error, and a cycle is rejected — both caught before anything runs.
• dependencies for non-ref edges. When a model must run after something it does not SELECT from (a grant, a load), dependencies: ["other_action"] adds the edge explicitly.

Tags and selective runs.

• Tag a model. tags: ["hourly", "finance"] labels the node.
• Run a subset. Execute --tags hourly (CLI) or select tags in a workflow config to build just the hourly branch and its needed upstreams.
• Schedule by tag. Different cadences (hourly vs daily) map cleanly onto different tag selections in different workflow configs.

Incremental tables — the cost lever.

• The pattern. type: "incremental" plus a when(incremental(), <filter>) block: the filter is applied on incremental runs and omitted on the first full build.
• uniqueKey → MERGE. Set uniqueKey and Dataform compiles a MERGE that updates matched rows and inserts new ones (idempotent re-runs); without it, it compiles an INSERT (append-only).
• self(). Refers to the table being built, used in the incremental filter to read the current high-water mark.
• Full refresh. A --full-refresh run (or a schema change) rebuilds the whole table, ignoring the incremental filter.

The failure modes practitioners pre-empt.

• Hard-coded names. A literal table name drops the edge; the model may run out of order. Mitigation: always ref().
• Rebuilding a huge table every run. A type: "table" over a billion-row fact re-scans everything nightly. Mitigation: make it incremental with a partition column.
• Incremental without uniqueKey on a re-processed window. Re-running a window double-appends rows. Mitigation: set uniqueKey so it compiles a MERGE, making re-runs idempotent.

Common interview probes on the graph.

• "How does Dataform know the run order?" — from the ref() edges; it topologically sorts the DAG.
• "How do you run just part of the project?" — tags plus a tag selection.
• "How do you avoid rebuilding a huge table?" — incremental with when(incremental()) and a partition.
• "How do you make an incremental re-run safe?" — a uniqueKey so it compiles to an idempotent MERGE.

Worked example — a staging → mart chain and the graph it builds

Detailed explanation. The clearest way to see the graph is a three-node chain: a declared source, a staging view, and a mart table, each referencing the previous. Build it and read off the edges and the derived run order.

• Source. raw_orders (declaration).
• Staging. stg_orders (view) refs raw_orders.
• Mart. fct_daily_sales (table) refs stg_orders.

Question. Write the three SQLX files, list the graph edges, and state the run order Dataform derives.

Input.

Node	Type	Refs	Runs after
raw_orders	declaration	—	(source leaf)
stg_orders	view	raw_orders	raw_orders
fct_daily_sales	table	stg_orders	stg_orders

Code.

-- stg_orders.sqlx — a view over the declared source.
config { type: "view", schema: "staging" }
SELECT
  CAST(id AS INT64) AS order_id,
  DATE(created)     AS order_date,
  region,
  total_cents
FROM ${ref("raw_orders")};

-- fct_daily_sales.sqlx — a table aggregating the staging view.
config {
  type: "table",
  schema: "marts",
  bigquery: { partitionBy: "order_date", clusterBy: ["region"] },
  tags: ["daily"]
}
SELECT
  order_date,
  region,
  COUNT(*)              AS orders,
  SUM(total_cents) / 100 AS revenue
FROM ${ref("stg_orders")}
GROUP BY order_date, region;

Step-by-step explanation.

1. stg_orders refs raw_orders, so Dataform records the edge raw_orders → stg_orders; the declaration is a leaf it does not build.
2. fct_daily_sales refs stg_orders, recording stg_orders → fct_daily_sales; the two edges chain into raw_orders → stg_orders → fct_daily_sales.
3. Topologically sorting that chain yields the run order: stg_orders first (its only input is a source), then fct_daily_sales. Dataform never has to be told this — it is derived.
4. The mart's bigquery.partitionBy: "order_date" and clusterBy: ["region"] are pushed into the compiled CREATE TABLE, so downstream queries filtering by date and region prune and cluster-scan cheaply.
5. The tags: ["daily"] label means a --tags daily run selects fct_daily_sales and pulls in its required upstream stg_orders automatically — you run the branch, not a hand-picked list.

Output.

Edge set	Topological order	Physical layout
raw_orders → stg_orders	1. stg_orders	view (no storage)
stg_orders → fct_daily_sales	2. fct_daily_sales	table, partitioned + clustered
tag daily on the mart	--tags daily builds the branch	selective run

Rule of thumb. Let the chain of ref() calls define the graph and let Dataform sort it — never encode run order by hand. Push partitionBy/clusterBy into the mart's config so the largest tables are physically laid out for the queries that read them, and tag branches so you can run them selectively.

Worked example — an incremental table and the MERGE it compiles to

Detailed explanation. The single most important cost pattern in Dataform is the incremental table: process only new rows, and use a uniqueKey so re-processing a window is idempotent. Build an incremental fct_events and read the MERGE it compiles into.

• The filter. when(incremental(), WHERE event_ts > (SELECT MAX ...)) — only new events on incremental runs.
• The key. uniqueKey: ["event_id"] — makes Dataform emit a MERGE, not an INSERT.
• The partition. partitionBy: DATE(event_ts) — prunes the max-timestamp probe and the merge.

Question. Write an incremental fct_events model with a uniqueKey, and show the MERGE Dataform compiles for an incremental run.

Input.

Setting	Value	Effect
type	incremental	build only new rows after first run
uniqueKey	["event_id"]	compile a MERGE (idempotent)
incremental filter	event_ts > MAX(self)	pick up new events
partitionBy	DATE(event_ts)	prune probe + merge

Code.

-- fct_events.sqlx
config {
  type: "incremental",
  schema: "marts",
  uniqueKey: ["event_id"],
  bigquery: { partitionBy: "DATE(event_ts)" }
}

SELECT event_id, user_id, event_ts, event_type, payload
FROM ${ref("stg_events")}
${ when(incremental(),
       `WHERE event_ts > (SELECT MAX(event_ts) FROM ${self()})`) }

-- What an INCREMENTAL run compiles to (uniqueKey → MERGE, idempotent re-runs):
MERGE `my-project.marts.fct_events` AS T
USING (
  SELECT event_id, user_id, event_ts, event_type, payload
  FROM `my-project.staging.stg_events`
  WHERE event_ts > (SELECT MAX(event_ts) FROM `my-project.marts.fct_events`)
) AS S
ON T.event_id = S.event_id
WHEN MATCHED THEN UPDATE SET
  user_id = S.user_id, event_ts = S.event_ts,
  event_type = S.event_type, payload = S.payload
WHEN NOT MATCHED THEN INSERT ROW;

-- What the FIRST (or --full-refresh) run compiles to (filter omitted):
CREATE OR REPLACE TABLE `my-project.marts.fct_events`
PARTITION BY DATE(event_ts) AS
SELECT event_id, user_id, event_ts, event_type, payload
FROM `my-project.staging.stg_events`;

Step-by-step explanation.

1. On the first run there is nothing to be incremental against, so Dataform omits the when(incremental()) filter and compiles a full CREATE OR REPLACE TABLE ... PARTITION BY — a normal build.
2. On subsequent incremental runs, the when(incremental(), ...) filter is included, restricting the source query to event_ts > MAX(event_ts of the existing table) — only genuinely new events are read.
3. Because uniqueKey: ["event_id"] is set, Dataform wraps the incremental select in a MERGE ... ON T.event_id = S.event_id: matched rows are updated and new rows inserted, so re-running an overlapping window never duplicates.
4. self() in the filter resolves to the table being built, so MAX(event_ts) FROM self reads the current high-water mark — the incremental cursor lives in the data, not in external state.
5. partitionBy: DATE(event_ts) means both the MAX(event_ts) probe and the MERGE's match scan prune to recent partitions, so an incremental run touches a sliver of the table rather than all of it — incrementality plus partitioning is what makes a billion-row fact affordable.

Output.

Run kind	Compiles to	Rows processed
first / --full-refresh	CREATE OR REPLACE TABLE	all
incremental, with uniqueKey	MERGE (update + insert)	new only, idempotent
incremental, no uniqueKey	INSERT (append)	new only, not idempotent
any read downstream	partition-pruned scan	recent partitions

Rule of thumb. For large append-mostly tables use type: "incremental" with a when(incremental()) high-water-mark filter and a uniqueKey so it compiles to an idempotent MERGE — and always partition on the incremental column so the probe and merge prune. Reserve --full-refresh for schema changes or backfills.

Worked example — tags and selective execution

Detailed explanation. A real project has models on different cadences — hourly KPIs, daily marts, weekly rollups. Tags let one project serve all three by running named subsets. Tag models by cadence and run just the hourly branch.

• Tag by cadence. tags: ["hourly"], ["daily"], ["weekly"] on the relevant models.
• Run a subset. --tags hourly builds tagged nodes and their required upstreams.
• Schedule per tag. Each workflow config selects a tag set and a cron.

Question. Tag a mixed-cadence project and show how an hourly run selects only the hourly branch plus its upstream dependencies.

Input.

Model	Tag	Included in --tags hourly?
stg_events (view)	(untagged upstream)	yes (required upstream)
kpi_hourly (table)	hourly	yes (selected)
mart_daily (table)	daily	no

Code.

-- kpi_hourly.sqlx — tagged hourly; refs an untagged upstream.
config { type: "table", schema: "reports", tags: ["hourly"] }
SELECT TIMESTAMP_TRUNC(event_ts, HOUR) AS hour, COUNT(*) AS events
FROM ${ref("stg_events")}
GROUP BY hour;

# Run ONLY the hourly branch. Dataform includes required upstreams by default.
dataform run --tags hourly --include-dependencies

# The daily mart is NOT built by this invocation:
#   selected:  kpi_hourly (tag=hourly)
#   pulled in: stg_events (upstream of kpi_hourly)
#   skipped:   mart_daily (tag=daily, not selected, not an upstream)

Step-by-step explanation.

1. kpi_hourly carries tags: ["hourly"]; mart_daily carries tags: ["daily"]; stg_events is untagged but sits upstream of the hourly KPI.
2. dataform run --tags hourly selects every node tagged hourly — here just kpi_hourly.
3. --include-dependencies (the safe default posture) pulls in the upstreams the selected nodes need, so stg_events is built first even though it is not tagged hourly.
4. mart_daily is neither tagged hourly nor an upstream of anything selected, so it is skipped — the hourly schedule does not pay to rebuild the daily mart.
5. Mapping tags to workflow configs (one cron per tag set) lets a single project serve multiple cadences without splitting into separate repositories — the graph is shared, the execution is sliced.

Output.

Invocation	Built	Skipped
--tags hourly (+deps)	stg_events, kpi_hourly	mart_daily
--tags daily (+deps)	stg_events, mart_daily	kpi_hourly
no tags	everything	nothing
--tags weekly (+deps)	weekly branch + upstreams	others

Rule of thumb. Tag models by cadence or domain and run named subsets with dependencies included, so one project serves hourly, daily, and weekly schedules from a shared graph. Always include dependencies on a selective run — selecting a leaf without its upstreams builds it against stale inputs.

Senior interview question on the dependency graph and incrementality

A senior interviewer might ask: "You have a raw event stream of billions of rows landing in BigQuery, plus a handful of dimensions and a set of hourly and daily reports. Design the Dataform graph: which models are views, tables, and incrementals; how ref() and tags control ordering and selective runs; how you keep the huge event fact cheap; and how you make an incremental re-run of a late-arriving window safe."

Solution Using ref-built ordering, tags, and an idempotent incremental fact

-- 1. Sources declared once; staging views are thin and cheap.
config { type: "declaration", database: "p", schema: "raw", name: "events" }

config { type: "view", schema: "staging" }               -- stg_events
SELECT event_id, user_id, TIMESTAMP(ts) AS event_ts, type
FROM ${ref("events")};

-- 2. The huge fact is INCREMENTAL + partitioned + keyed → idempotent MERGE.
config {
  type: "incremental", schema: "marts",
  uniqueKey: ["event_id"],
  bigquery: { partitionBy: "DATE(event_ts)", clusterBy: ["type"] },
  tags: ["hourly"]
}
SELECT event_id, user_id, event_ts, type
FROM ${ref("stg_events")}
${ when(incremental(),
     -- re-scan a 3-hour window so LATE events are picked up; MERGE dedupes.
     `WHERE event_ts >= TIMESTAMP_SUB(
        (SELECT MAX(event_ts) FROM ${self()}), INTERVAL 3 HOUR)`) }

-- 3. Reports read the fact; tagged by cadence for selective scheduling.
config { type: "table", schema: "reports", tags: ["daily"] }   -- mart_daily
SELECT DATE(event_ts) AS day, type, COUNT(*) AS events
FROM ${ref("fct_events")}
GROUP BY day, type;

Step-by-step trace.

Layer	Model	Type	Why
source	events	declaration	raw, not built
staging	stg_events	view	thin, no storage
fact	fct_events	incremental + uniqueKey	cheap + idempotent
report (hourly)	kpi_hourly	table, tag hourly	small, frequent
report (daily)	mart_daily	table, tag daily	daily cadence
ordering	all edges	ref()	topologically sorted

After deployment, Dataform sorts the graph so stg_events builds before fct_events, which builds before the reports. The fact is incremental, partitioned by day, and keyed on event_id, so each hourly run re-scans only a 3-hour window and MERGEs it — late-arriving events land and duplicates collapse. Tags split the reports onto hourly and daily workflow configs over the one shared graph, and every table name is a ref(), so promoting the project from a dev dataset to prod changes no SQL.

Output:

Metric	Naive (all tables, hand-ordered)	Dataform graph
Fact rebuild cost	full re-scan each run	3-hour window (incremental)
Late-event handling	missed or manual backfill	window re-scan + MERGE
Re-run safety	duplicate rows	idempotent (uniqueKey)
Run order	maintained by hand	derived from ref()
Multi-cadence	separate jobs	tags over one graph

Why this works — concept by concept:

• ref-built DAG — the topological sort over the ref() edges guarantees every model runs after its inputs, so ordering is a property of the SQL, not a schedule someone maintains.
• Incremental + partition — a when(incremental()) window filter plus partitionBy means each run reads a sliver of a billion-row fact, turning an unaffordable full rebuild into a cheap append.
• uniqueKey → idempotent MERGE — keying the incremental makes Dataform emit a MERGE, so re-scanning an overlapping window to catch late events updates in place instead of duplicating.
• Tags for selective runs — labelling models by cadence lets one graph feed multiple schedules, so the hourly branch never pays to rebuild the daily mart.
• Cost — one compile, a windowed merge on the fact, and tag-scoped runs, versus nightly full rebuilds and hand-ordered jobs. The eliminated cost is re-scanning the entire fact every run — O(window) instead of O(table) per build.

Data transformation
Topic — data-transformation
Transformation problems on incremental models and DAGs

Practice →

ETL Topic — etl ETL problems on pipeline ordering and dependencies

Practice →

---

3. Assertions — data quality inside the graph

Inline and manual assertions become zero-row checks that gate the dependents behind them

The mental model in one line: a Dataform assertion is a data quality test that compiles to a BigQuery query which must return zero rows to pass — either declared inline in a model's config (uniqueKey, nonNull, rowConditions, each auto-generating the query that finds violating rows) or written by hand as a type: "assertion" SQLX file selecting the bad rows — and because assertions are nodes in the dependency graph, a failing assertion fails its action and, when downstream models are configured to depend on their inputs' assertions, blocks those dependents from building on data that just failed a check. Tests live in the pipeline, run as part of the graph, and gate what runs next.

Inline assertions in a model's config.

• uniqueKey (or uniqueKeys). Asserts the listed column(s) are unique — Dataform generates a GROUP BY ... HAVING COUNT(*) > 1 that returns duplicate keys.
• nonNull. Asserts the listed columns are never null — generates a query returning rows where any of them is null.
• rowConditions. A list of boolean SQL expressions that must hold for every row — generates a query returning rows where a condition is false.
• Where they live. Inside config { assertions: { ... } } on the model, so the test travels with the model definition.

What an assertion compiles to.

• Zero-row semantics. Every assertion is a SELECT of violating rows; zero rows returned = pass, one or more = fail. There is no "true/false" — the row count is the verdict.
• A real BigQuery job. Each assertion is its own query job against the target table, so it costs a scan (bounded by partitioning/clustering).
• Named and visible. Assertions get their own dataset/objects and appear in the graph, so a failure names exactly which check on which table broke.

Manual assertions — arbitrary checks.

• type: "assertion". A SQLX file whose SELECT returns the rows that violate your rule; the same zero-row-is-pass contract.
• Cross-table checks. Referential integrity ("orders with a customer_id not in customers"), reconciliation ("today's total differs from the source by > 1%"), and freshness ("no rows in the last 24h") are all manual assertions.
• They ref() too. A manual assertion references the tables it checks, so it becomes a downstream node of them in the graph.

Failure behavior and gating.

• A failed assertion fails the action. The run reports the failure and, in a workflow, can fail the invocation.
• Blocking dependents. With dependOnDependencyAssertions: true (or per-dependency config), a model waits for its inputs' assertions to pass, so bad data does not propagate downstream.
• Warn vs block. You choose: some assertions merely surface (monitoring), others gate the graph (contracts). Critical keys and referential integrity are usually blocking.

The failure modes practitioners pre-empt.

• Assertions that don't gate. A check that runs but whose failure nothing depends on lets bad data flow downstream anyway. Mitigation: make critical assertions blocking via dependOnDependencyAssertions.
• Unbounded assertion scans. A rowConditions check that full-scans a huge unpartitioned table is expensive every run. Mitigation: assert against partitioned/clustered tables; scope checks to recent partitions where possible.
• Testing the wrong grain. A uniqueKey on a table that is legitimately many-to-one fails forever. Mitigation: assert the actual grain of the model.

Common interview probes on assertions.

• "How does a Dataform assertion pass or fail?" — it returns violating rows; zero rows is a pass.
• "Inline vs manual?" — inline uniqueKey/nonNull/rowConditions for common checks; manual type: "assertion" SQLX for cross-table or custom logic.
• "How do you stop bad data propagating?" — dependOnDependencyAssertions so dependents wait for their inputs' assertions.
• "Where do assertions run?" — as nodes in the graph, as BigQuery query jobs, on the target tables.

Worked example — inline uniqueKey, nonNull, and rowConditions

Detailed explanation. The cheapest data-quality win is inline assertions: declare the invariants of a model right in its config and Dataform generates the checks. Add uniqueness, non-null, and range checks to a fct_orders mart and read what they compile to.

• uniqueKey. order_id is the grain — must be unique.
• nonNull. order_id and customer_id must never be null.
• rowConditions. revenue >= 0 and a valid status.

Question. Add inline assertions to fct_orders for a unique key, non-null keys, and two row conditions, and show the queries they generate.

Input.

Assertion	Declares	Generated check returns
uniqueKey: ["order_id"]	grain is one row per order	duplicate order_ids
nonNull: [order_id, customer_id]	keys always present	rows with a null key
rowConditions: ["revenue >= 0"]	no negative revenue	rows where revenue < 0
rowConditions: ["status in (...)"]	valid status only	rows with a bad status

Code.

-- fct_orders.sqlx — the model carries its own data-quality contract.
config {
  type: "table",
  schema: "marts",
  assertions: {
    uniqueKey: ["order_id"],
    nonNull: ["order_id", "customer_id"],
    rowConditions: [
      "revenue >= 0",
      "status in ('paid', 'refunded', 'pending')"
    ]
  }
}
SELECT order_id, customer_id, revenue, status
FROM ${ref("stg_orders")};

-- What Dataform generates (each must return ZERO rows to pass):

-- uniqueKey → duplicate keys:
SELECT order_id FROM `p.marts.fct_orders`
GROUP BY order_id HAVING COUNT(*) > 1;

-- nonNull → rows with a null key:
SELECT * FROM `p.marts.fct_orders`
WHERE order_id IS NULL OR customer_id IS NULL;

-- rowConditions → rows failing a condition:
SELECT * FROM `p.marts.fct_orders`
WHERE NOT (revenue >= 0)
   OR NOT (status in ('paid', 'refunded', 'pending'));

Step-by-step explanation.

1. The assertions block sits inside the model's config, so the contract — grain, required columns, valid ranges — travels with the model and is reviewed alongside its SQL.
2. uniqueKey: ["order_id"] compiles to a GROUP BY order_id HAVING COUNT(*) > 1: if any order_id appears twice, those keys come back and the assertion fails, catching a broken join fan-out at the source.
3. nonNull compiles to a WHERE <col> IS NULL across the listed columns; any null key returns a row and fails, so a silently-dropped join key is caught before downstream models trust it.
4. Each rowConditions expression is negated in the generated query (WHERE NOT (revenue >= 0)), so any row violating the business rule is returned — the check passes only when every row satisfies every condition.
5. All four checks run as their own BigQuery jobs against fct_orders after it builds, and each is a graph node, so a failure names the exact table and rule — "fct_orders uniqueKey assertion failed" rather than a vague downstream error.

Output.

Check	Passes when	Catches
uniqueKey	no duplicate order_id	join fan-out / bad grain
nonNull	keys never null	dropped keys, bad casts
revenue >= 0	all revenue non-negative	sign/refund bugs
status in (...)	all statuses valid	unexpected enum values

Rule of thumb. Put the model's invariants — grain (uniqueKey), required columns (nonNull), and business rules (rowConditions) — inline in its config so the data-quality contract lives with the model. Each compiles to a zero-row check that names exactly what broke.

Worked example — a manual assertion for referential integrity

Detailed explanation. Inline assertions cover single-table invariants; cross-table rules need a manual assertion — a type: "assertion" SQLX that selects the offending rows. Write a referential-integrity check that fails if any order points at a customer that does not exist.

• The rule. Every fct_orders.customer_id must exist in dim_customers.
• The query. A LEFT JOIN ... WHERE dim IS NULL returns orphans.
• The gate. It refs both tables, so it becomes their downstream node.

Question. Write a manual assertion that returns orphaned orders (a customer_id absent from dim_customers) and explain how it gates the graph.

Input.

Piece	Value
Assertion type	type: "assertion"
Rule	orders' customer_id ∈ dim_customers
Violating rows	orders with no matching customer
Pass condition	zero orphaned orders

Code.

-- definitions/assertions/orders_have_customers.sqlx
config {
  type: "assertion",
  schema: "assertions",
  description: "Every order must reference an existing customer (referential integrity)."
}

-- Return the VIOLATING rows: orphaned orders. Zero rows = pass.
SELECT o.order_id, o.customer_id
FROM ${ref("fct_orders")}    AS o
LEFT JOIN ${ref("dim_customers")} AS c
  ON o.customer_id = c.customer_id
WHERE c.customer_id IS NULL

-- A downstream mart that must NOT build on orphaned data:
config {
  type: "table",
  schema: "reports",
  dependOnDependencyAssertions: true   -- wait for inputs' assertions to pass
}
SELECT customer_id, SUM(revenue) AS revenue
FROM ${ref("fct_orders")}
GROUP BY customer_id;

Step-by-step explanation.

1. The assertion's SELECT returns orphans — orders whose customer_id has no match in dim_customers — via a LEFT JOIN with WHERE c.customer_id IS NULL; if referential integrity holds, it returns zero rows and passes.
2. Because the assertion ref()s both fct_orders and dim_customers, Dataform places it downstream of both in the graph, so it runs only after both tables are built and it checks the current data.
3. The downstream reports mart sets dependOnDependencyAssertions: true, so it waits for the assertions on its inputs — including this referential-integrity check — to pass before it builds.
4. If some orders are orphaned, the assertion returns rows and fails; the gated mart is not built, so the bad relationship never propagates into a customer revenue report where it would silently misattribute or drop revenue.
5. This is the pattern for any cross-table contract — reconciliation, referential integrity, freshness — that a single-table inline assertion cannot express: encode the violation as a SELECT, and let the graph gate on it.

Output.

Data state	Assertion returns	Gated mart
all orders have customers	0 rows (pass)	builds
12 orphaned orders	12 rows (fail)	blocked
dim rebuilt, orphans fixed	0 rows (pass)	builds
assertion not depended on	fail (but ignored)	builds on bad data

Rule of thumb. Express cross-table and custom rules as manual type: "assertion" SQLX that selects the violating rows, and make critical dependents dependOnDependencyAssertions: true so a failed check actually blocks the build. An assertion nothing depends on is monitoring, not a gate.

Worked example — blocking vs warning and scoping assertion cost

Detailed explanation. Not every assertion should stop the pipeline, and not every assertion should scan a whole table. Decide which checks block versus warn, and scope an expensive check to recent partitions. Configure a blocking key check and a scoped, warn-only freshness check.

• Blocking. A uniqueKey/referential check gates dependents — bad grain must not propagate.
• Warn-only. A "row count dipped" monitor surfaces but does not block.
• Scoped. A freshness check reads only today's partition, not the whole fact.

Question. Set up one blocking assertion (grain) and one scoped warn-only assertion (freshness), and explain the cost and gating difference.

Input.

Assertion	Role	Gates build?	Scan cost
uniqueKey on fct_orders	contract	yes (blocking)	keyed, cheap
freshness (rows today)	monitor	no (warn)	one partition
full-table anomaly	monitor	no (warn)	whole table (avoid)

Code.

-- Blocking: the grain contract. Downstream depends on it (dependOnDependencyAssertions).
config {
  type: "table", schema: "marts",
  assertions: { uniqueKey: ["order_id"] }     -- must hold; dependents gate on it
}
SELECT order_id, customer_id, revenue, DATE(created_at) AS order_date
FROM ${ref("stg_orders")};

-- Warn-only + SCOPED: freshness check reads ONLY today's partition, and nothing
-- downstream depends on it, so a failure surfaces without blocking the pipeline.
config {
  type: "assertion",
  schema: "assertions",
  description: "WARN: no orders landed for today (monitoring, non-blocking)."
}
SELECT CURRENT_DATE() AS missing_day
WHERE NOT EXISTS (
  SELECT 1 FROM ${ref("fct_orders")}
  WHERE order_date = CURRENT_DATE()          -- partition prune → scans one day
)

Step-by-step explanation.

1. The uniqueKey assertion on fct_orders is a contract: it is cheap (a keyed group-by) and downstream marts set dependOnDependencyAssertions, so if the grain breaks, the pipeline stops rather than fanning out duplicates.
2. The freshness check is a monitor: it returns a row only when no orders exist for today, signalling a stalled upstream — useful to alert on, but not a reason to tear down the whole graph, so nothing depends on it.
3. The freshness query filters on order_date = CURRENT_DATE(), and because fct_orders is partitioned by order_date, BigQuery prunes to a single partition — the check costs a one-day scan, not a full-table scan every run.
4. The gating difference is deliberate: blocking assertions encode invariants that make downstream data wrong if violated; warn-only assertions encode conditions you want to know about but that do not corrupt dependents.
5. Scoping cost matters because assertions run every build — an unpartitioned full-table rowConditions on a billion-row fact is a recurring bill, so you assert against partitioned tables and restrict monitors to the window that can actually change.

Output.

Assertion	On failure	Cost per run
uniqueKey (blocking)	dependents blocked	cheap (keyed)
freshness (warn, scoped)	alert only	one partition
referential (blocking)	dependents blocked	join, bounded
full-table monitor (avoid)	alert only	whole-table scan

Rule of thumb. Make invariants that corrupt downstream data blocking (dependents dependOnDependencyAssertions) and make "good to know" conditions warn-only, and always scope assertion queries to partitioned columns so a check that runs every build stays cheap. Gate on correctness, monitor on health.

Senior interview question on assertions and data quality gating

A senior interviewer might ask: "Design the data-quality layer for a Dataform pipeline feeding financial reports. Cover which invariants you assert inline versus with manual assertions, how you stop a failed check from letting bad data reach the reports, how you decide blocking versus warning, and how you keep the assertions themselves from becoming an expensive full-table scan every run."

Solution Using inline contracts, a manual cross-table assertion, and blocking dependents

-- 1. Inline contract on the fact: grain, required keys, business rules.
config {
  type: "incremental", schema: "marts",
  uniqueKey: ["order_id"],
  bigquery: { partitionBy: "order_date" },
  assertions: {
    nonNull: ["order_id", "customer_id", "order_date"],
    rowConditions: ["revenue >= 0", "status in ('paid','refunded','pending')"]
  }
}
SELECT order_id, customer_id, revenue, status, DATE(created_at) AS order_date
FROM ${ref("stg_orders")}
${ when(incremental(), `WHERE DATE(created_at) >= CURRENT_DATE() - 2`) };

-- 2. Manual cross-table assertion: reconciliation within tolerance (blocking).
config { type: "assertion", schema: "assertions",
         description: "Daily revenue must match the source within 0.5%." }
WITH d AS (SELECT SUM(revenue) r FROM ${ref("fct_orders")}
           WHERE order_date = CURRENT_DATE()),
     s AS (SELECT SUM(total_cents)/100 r FROM ${ref("raw_orders")}
           WHERE DATE(created) = CURRENT_DATE())
SELECT d.r AS mart, s.r AS source
FROM d, s
WHERE ABS(d.r - s.r) / NULLIF(s.r, 0) > 0.005;      -- returns a row = mismatch = fail

-- 3. The report GATES on its inputs' assertions — no bad data reaches finance.
config { type: "table", schema: "reports", dependOnDependencyAssertions: true }
SELECT order_date, SUM(revenue) AS revenue
FROM ${ref("fct_orders")}
GROUP BY order_date;

Step-by-step trace.

Layer	Assertion	Blocking?	Scope
grain	uniqueKey: order_id	yes	keyed
completeness	nonNull keys	yes	keyed cols
validity	rowConditions	yes	recent partitions
reconciliation	manual, ±0.5%	yes	today's partition
gate	dependOnDependencyAssertions	—	report waits

After deployment, the fact carries its grain, completeness, and validity contracts inline; a manual reconciliation assertion compares today's mart revenue to the raw source within a 0.5% tolerance; and the finance report sets dependOnDependencyAssertions, so it builds only when every assertion on fct_orders — including reconciliation — passes. Because the fact is partitioned by order_date and the incremental filter and the checks all scope to recent days, the whole quality layer scans a couple of partitions per run, not the entire history. A failure names the exact check and the report simply does not refresh.

Output:

Metric	No assertions	Dataform quality layer
Bad grain reaching reports	possible	blocked at uniqueKey
Source/mart drift	undetected	caught at ±0.5% tolerance
Null keys downstream	possible	blocked at nonNull
Report on bad data	builds anyway	gated (dependOnDependencyAssertions)
Assertion scan cost	(n/a)	partition-scoped, cheap

Why this works — concept by concept:

• Inline contracts — uniqueKey, nonNull, and rowConditions put the fact's grain, completeness, and validity rules where the model is defined, so the contract is reviewed and versioned with the SQL.
• Manual reconciliation assertion — encoding "mart within 0.5% of source" as a query that returns rows only on mismatch catches drift no single-table check can see, using the same zero-row-is-pass contract.
• Blocking via dependOnDependencyAssertions — the report waits for its inputs' assertions, so a failed check does not merely alert — it actually prevents bad data from reaching finance.
• Partition-scoped checks — scoping the incremental filter and the assertions to recent partitions keeps a quality layer that runs every build from becoming a full-history scan.
• Cost — a handful of keyed and partition-pruned check queries per run, versus a production incident and a manual reconciliation. The eliminated cost is a wrong financial report shipped and later retracted — O(partition) checks instead of O(incident) cleanups.

Data validation
Topic — data-validation
Data validation problems on quality checks and constraints

Practice →

ETL Topic — etl ETL problems on validation gates and reconciliation

Practice →

---

4. CI/CD — Git, release configs, and workflow schedules

Git-backed repos, isolated dev workspaces, release configs, and scheduled workflows

The mental model in one line: a Dataform project is a Git repository, and the path to production is four moving parts — a dev workspace where each engineer compiles and runs into an isolated schema (a suffix like _dev or their name) without touching prod, a Git pull request gate where CI/CD runs dataform compile and the assertions so nothing merges to main that fails to compile or fails a check, a release config that compiles a chosen git commitish (usually main) with production vars into a versioned compilation result, and a workflow config that executes that release on a cron under a service account — so a change flows from a branch, through review and automated checks, into a scheduled production run, with dev and prod cleanly separated by dataset. Version control, isolation, automated tests, and scheduling are the four properties that turn SQLX files into a governed pipeline.

Git integration.

• The repo is the project. A Dataform repository connects to GitHub, GitLab, Azure DevOps, or Cloud Source Repositories; commits, branches, and PRs are ordinary Git.
• workflow_settings.yaml. The project's root config — default dataformCoreVersion, defaultProject (GCP project), defaultDataset, defaultLocation, and vars — the settings every compilation starts from.
• Branch-based work. Each engineer branches, edits SQLX, and opens a PR; review happens on the diff of the transformation logic.

Dev workspaces — isolation.

• A workspace is a branch + a sandbox. In the managed service, a workspace edits a branch and runs compilations/executions against your datasets.
• Schema suffix / dataset override. A dev compilation overrides the destination — a schema suffix (_dev) or a dev defaultDataset — so a developer's build lands in analytics_dev, never in prod.
• Compilation overrides. vars, defaultDatabase, and schemaSuffix are set per environment, so the same SQLX compiles to dev objects in dev and prod objects in prod — the payoff of never hard-coding names.

Release configs — compiling a version.

• A named compilation. A release config pins a git commitish (e.g. main), a set of production vars, and compilation overrides, and produces a compilation result — a versioned, immutable plan of what would run.
• Scheduled compilation. Release configs can recompile on a cadence so the latest main is always ready to execute.
• Separation of compile and run. Compiling (planning) is distinct from executing (running); a release is the artifact a workflow runs.

Workflow configs — scheduling.

• Cron + selection. A workflow config runs a release config's result on a schedule, optionally filtered by tags or a set of actions, as a chosen service account.
• Service-account IAM. The run's BigQuery permissions are the service account's — least-privilege, auditable, the standard GCP model.
• dataform run in CI. Outside the managed scheduler, the dataform CLI (compile, run, test) drives the same graph from any CI system.

CI on pull requests.

• Compile gate. dataform compile on the PR branch fails the check on a bad ref(), a typo, or a cycle — caught before merge.
• Assertion gate. Running the project (or the changed subset) into a CI/dev dataset and executing assertions proves the change does not break data-quality contracts.
• Merge protection. Branch protection requires the compile + assert checks to pass, so main stays releasable.

The failure modes practitioners pre-empt.

• No PR compile gate. A broken ref() merges and breaks the scheduled prod run. Mitigation: dataform compile as a required PR check.
• Shared dev schema collisions. Two developers building into the same dataset overwrite each other. Mitigation: per-developer schema suffix / workspace isolation.
• Prod vars baked into SQL. Environment values hard-coded in models instead of vars make dev and prod diverge. Mitigation: put environment differences in vars/compilation overrides, referenced via dataform.projectConfig.vars.

Common interview probes on CI/CD.

• "How do devs avoid stepping on prod?" — isolated workspaces with a dev schema suffix / dataset override.
• "What runs on a PR?" — dataform compile plus assertions, as required checks.
• "What actually schedules prod?" — a workflow config running a release config's compilation result on a cron as a service account.
• "How do the same files build dev and prod objects?" — compilation overrides (vars, schemaSuffix, defaultDataset).

Worked example — workflow_settings.yaml and a dev schema override

Detailed explanation. The root of every project is workflow_settings.yaml, and the trick that makes dev/prod separation work is a compilation override that suffixes the destination schema. Set up the base settings and a dev override so a developer's build lands in _dev datasets.

• Base settings. Project, default dataset, location, core version, vars.
• Dev override. A schemaSuffix: dev (or a dev dataset) so objects become analytics_dev.
• Same SQL. Because models use ref(), no model changes between environments.

Question. Write workflow_settings.yaml and show how a dev compilation override redirects a model from analytics.fct_orders to analytics_dev.fct_orders.

Input.

Setting	Prod	Dev
defaultDataset	analytics	analytics
schemaSuffix	(none)	dev
Resulting dataset	analytics	analytics_dev
vars.env	prod	dev

Code.

# workflow_settings.yaml — the project's base compilation settings.
defaultProject: my-gcp-project
defaultDataset: analytics
defaultLocation: US
dataformCoreVersion: 3.0.0
vars:
  env: prod
  lookback_days: "30"

# A dev compilation override (workspace / release config setting):
#   the SAME SQLX compiles into *_dev datasets, isolated from prod.
codeCompilationConfig:
  schemaSuffix: dev            # analytics -> analytics_dev
  vars:
    env: dev
    lookback_days: "3"         # smaller window in dev for cheap iteration

-- The model never changes between environments — it reads config via vars.
config { type: "table", schema: "analytics" }
SELECT * FROM ${ref("stg_orders")}
WHERE order_date >= CURRENT_DATE() - CAST(${dataform.projectConfig.vars.lookback_days} AS INT64)
-- prod: dataset analytics, 30-day window
-- dev : dataset analytics_dev, 3-day window

Step-by-step explanation.

1. workflow_settings.yaml sets the defaults every compilation inherits — the GCP project, the default dataset, the BigQuery location, the pinned dataformCoreVersion, and project vars.
2. The dev codeCompilationConfig overrides just two things: schemaSuffix: dev, which appends _dev to every destination dataset, and a dev vars block that shrinks the lookback window for cheap iteration.
3. The model's schema: "analytics" becomes analytics_dev under the dev override purely from the suffix — the SQLX is untouched, which is only possible because destinations are declared in config, not hard-coded.
4. The ${dataform.projectConfig.vars.lookback_days} reference makes the model read its window from vars, so prod scans 30 days and dev scans 3 — environment differences live in vars, not in forked SQL.
5. The result is true isolation: a developer compiles and runs into analytics_dev with a tiny window, and the identical code, under the prod release config, builds analytics with the full window — no code diff between environments.

Output.

Compilation	Destination	Lookback	Isolated from prod?
prod release (main)	analytics.*	30 days	—
dev workspace	analytics_dev.*	3 days	yes
second dev	analytics_alice.*	3 days	yes
CI check	analytics_ci.*	3 days	yes

Rule of thumb. Keep environment differences in workflow_settings.yaml vars and a per-environment codeCompilationConfig (a schemaSuffix or dev dataset), and read variable values via dataform.projectConfig.vars in models. The same SQLX then compiles to isolated dev objects and to prod objects with no code change — the whole point of ref() and config-declared destinations.

Worked example — a release config and a scheduled workflow config

Detailed explanation. Production runs come from two linked artifacts: a release config that compiles main with prod vars, and a workflow config that executes that compilation on a schedule as a service account. Define both and trace a scheduled run.

• Release config. Compile main, prod vars, no dev suffix → a compilation result.
• Workflow config. Cron + tag selection + service account → executes the release.
• The run. Nightly, builds the daily tag branch into prod, runs assertions.

Question. Configure a release config and a nightly workflow config that builds the daily-tagged models into production and stops on assertion failure.

Input.

Artifact	Key fields	Purpose
release config	gitCommitish: main, prod vars	versioned prod plan
workflow config	cron, tags, service account	scheduled execution
selection	tags: ["daily"]	build the daily branch
on failure	assertion fails invocation	stop bad prod build

Code.

# release config — compile main with production settings into a compilation result.
release_config:
  name: prod-release
  gitCommitish: main
  codeCompilationConfig:
    defaultDataset: analytics        # prod dataset, NO _dev suffix
    vars: { env: prod, lookback_days: "30" }
  cronSchedule: "0 1 * * *"          # recompile main nightly at 01:00

# workflow config — run the release's result on a schedule, as a service account.
workflow_config:
  name: nightly-daily
  releaseConfig: prod-release
  cronSchedule: "0 2 * * *"          # execute at 02:00 (after the 01:00 compile)
  serviceAccount: [e-mail]
  invocationConfig:
    includedTags: ["daily"]          # build only the daily branch
    transitiveDependenciesIncluded: true   # + its required upstreams
    # an assertion failure fails the invocation -> the run is marked failed

# The same graph from a CI runner (equivalent, outside the managed scheduler):
dataform compile
dataform run --tags daily --include-dependencies \
  --vars env=prod,lookback_days=30 --default-database my-gcp-project

Step-by-step explanation.

1. The release config pins gitCommitish: main and prod vars with no dev suffix, and recompiles nightly at 01:00 — so there is always a fresh, versioned compilation result representing exactly what production should run.
2. The workflow config references that release and executes at 02:00, an hour after the compile, as the dataform-runner service account — so the run's BigQuery permissions are that account's, auditable and least-privilege.
3. includedTags: ["daily"] with transitiveDependenciesIncluded: true selects the daily branch plus every upstream it needs, so the nightly run builds the daily marts and their staging inputs but not the hourly branch.
4. Assertions in the selected branch run as graph nodes; if one fails, the invocation is marked failed, so a broken data-quality contract stops the production build instead of publishing bad tables.
5. The dataform CLI block shows the same graph driven from any CI system with the identical selection and vars — the managed workflow config is convenience over the same primitives, so a self-hosted CI pipeline is a first-class alternative.

Output.

Step	Time	Action
compile release	01:00	main + prod vars → compilation result
run workflow	02:00	build daily branch as service account
assertions	during run	pass → publish; fail → stop
result	02:xx	prod datasets refreshed or run failed

Rule of thumb. Separate the plan (a release config compiling main with prod vars) from the execution (a workflow config running it on a cron as a least-privilege service account), select the branch by tag with dependencies included, and let assertion failures fail the invocation. The CLI (compile/run) gives the identical pipeline from any CI runner.

Worked example — a compile-and-assert PR gate

Detailed explanation. The check that keeps main releasable is a CI job that compiles the project and runs the assertions on every pull request, into an isolated CI dataset. Write a GitHub Actions workflow that gates the merge.

• Compile. dataform compile fails on a bad ref(), typo, or cycle.
• Isolated run. Build into a _ci schema so the PR never touches prod or dev.
• Assert. Run the assertions; a failure fails the check and blocks the merge.

Question. Write a PR CI pipeline that compiles the Dataform project, builds the changed models into an isolated CI dataset, and runs assertions as a required merge check.

Input.

Stage	Command	Fails the PR when
install	npm i @dataform/cli	—
compile	dataform compile	bad ref / typo / cycle
run (CI dataset)	dataform run --schema-suffix ci	build error
assert	assertions in the run	a data-quality check fails

Code.

# .github/workflows/dataform-ci.yaml — required check on every PR.
name: dataform-ci
on: { pull_request: { branches: [main] } }

jobs:
  compile-and-assert:
    runs-on: ubuntu-latest
    steps:
      - uses: actions/checkout@v4
      - uses: actions/setup-node@v4
        with: { node-version: "20" }
      - run: npm install -g @dataform/cli

      # 1. Compile: a bad ref(), typo, or cycle fails the PR here.
      - run: dataform compile

      # 2. Auth to GCP as a least-privilege CI service account (Workload Identity).
      - uses: google-github-actions/auth@v2
        with: { workload_identity_provider: "${{ secrets.WIF }}",
                service_account: "[e-mail]" }

      # 3. Build into an ISOLATED ci dataset + 4. run assertions. Any failure = red check.
      - run: |
          dataform run \
            --default-database my-gcp-project \
            --schema-suffix "ci_pr_${{ github.event.number }}" \
            --vars env=ci,lookback_days=1 \
            --include-dependencies

Step-by-step explanation.

1. The workflow triggers on every pull request targeting main, so no change reaches the release branch without passing these checks.
2. dataform compile runs first and is the cheapest gate: a ref() to a nonexistent model, a syntax error, or a dependency cycle fails compilation and the PR turns red before any BigQuery job runs.
3. Authentication uses Workload Identity Federation to assume a dedicated, least-privilege dataform-ci service account, so CI can build into BigQuery without long-lived keys.
4. dataform run builds into a per-PR isolated dataset (schema-suffix ci_pr_<number>) with a one-day lookback for speed, so the PR's build cannot touch prod or another PR — and the assertions in the run execute against that isolated build.
5. Any failure — compile error, build error, or a failed assertion — fails the job; with branch protection requiring this check, the PR cannot merge, so main stays both compilable and passing its data-quality contracts.

Output.

PR change	Compile	CI run + assert	Merge
valid model	pass	pass	allowed
bad ref()	fail	(skipped)	blocked
breaks uniqueKey	pass	assert fails	blocked
valid + new assertion	pass	pass	allowed

Rule of thumb. Gate every PR with dataform compile plus an isolated dataform run that executes the assertions into a per-PR dataset, and make it a required check with branch protection. Compilation catches structural breakage for free; running the assertions catches data-quality regressions before they reach main.

Senior interview question on Dataform CI/CD and environments

A senior interviewer might ask: "Design the full delivery pipeline for a Dataform project with five engineers. Cover how developers work without colliding or touching prod, what runs automatically on a pull request, how the same SQLX builds dev and prod objects, how production is compiled and scheduled, and how a failed data-quality check stops a bad deployment — all with least-privilege access."

Solution Using isolated workspaces, a PR gate, a release config, and a scheduled workflow

# 1. Base settings; environment differences live in vars, not SQL.
# workflow_settings.yaml
defaultProject: my-gcp-project
defaultDataset: analytics
defaultLocation: US
dataformCoreVersion: 3.0.0
vars: { env: prod, lookback_days: "30" }

# 2. Isolation: each dev/workspace/CI compiles with its own schema suffix.
#    dev alice -> analytics_alice ; CI PR#42 -> analytics_ci_pr_42 ; prod -> analytics
codeCompilationConfig: { schemaSuffix: "alice", vars: { env: dev, lookback_days: "3" } }

# 3. PR gate (required check): compile + isolated run + assertions.
# .github/workflows/dataform-ci.yaml (abridged)
on: { pull_request: { branches: [main] } }
jobs:
  gate:
    steps:
      - run: dataform compile                         # structural gate
      - run: dataform run --schema-suffix "ci_pr_${{ github.event.number }}"
              --vars env=ci,lookback_days=1 --include-dependencies   # asserts run here

# 4. Prod: a release compiles main; a workflow runs it on a cron as a service account.
release_config:
  gitCommitish: main
  codeCompilationConfig: { defaultDataset: analytics, vars: { env: prod, lookback_days: "30" } }
workflow_config:
  releaseConfig: prod-release
  cronSchedule: "0 2 * * *"
  serviceAccount: [e-mail]
  invocationConfig: { includedTags: ["daily"], transitiveDependenciesIncluded: true }
  # an assertion failure fails the invocation -> bad data never publishes

Step-by-step trace.

Stage	Mechanism	Isolation / control
dev work	workspace + schemaSuffix	per-dev dataset, never prod
PR	compile + isolated run + assert	required merge check
environment	vars + compilation override	same SQLX, different objects
prod compile	release config on main	versioned compilation result
prod run	workflow config, cron, service account	least-privilege, scheduled
safety	assertion fails invocation	bad deploy stopped

After deployment, each of the five engineers works in a workspace that builds into their own analytics_<name> dataset, so nobody collides and nobody touches prod. Every pull request runs dataform compile and an isolated assertion run as required checks, so main never breaks structurally or in data quality. Production is a release config compiling main with prod vars into a versioned result, executed nightly by a workflow config as a dedicated service account; a failed assertion fails the invocation, so a bad build is stopped before it publishes. The identical SQLX produces dev, CI, and prod objects purely through compilation overrides.

Output:

Metric	Ad-hoc scripts	Dataform CI/CD
Dev collisions	overwrite prod/each other	isolated per-dev datasets
PR safety	none	compile + assert required
Env portability	fork SQL	vars + suffix, same SQL
Prod scheduling	manual/cron scripts	release + workflow config
Bad deploy	reaches prod	stopped by assertion gate
Access	broad/shared	per-run service account

Why this works — concept by concept:

• Isolated workspaces — a per-developer schemaSuffix builds every engineer's changes into their own dataset, so five people iterate in parallel without collisions and prod is never a developer's scratchpad.
• Compile-and-assert PR gate — a required check that compiles and runs the assertions into a throwaway CI dataset keeps main both structurally valid and passing its data-quality contracts, catching breakage before merge.
• vars + compilation overrides — putting environment differences in vars and a schema suffix means the same SQLX compiles to dev, CI, and prod objects, so there is exactly one source of truth and no per-environment fork.
• Release + workflow configs — separating a versioned compile of main from a scheduled, service-account execution gives auditable, least-privilege production runs where a failed assertion stops the deploy.
• Cost — one CI job per PR and one scheduled compile/run per cadence, versus a fleet of hand-maintained scheduled scripts and the incidents they cause. The eliminated cost is broken-main and bad-deploy firefighting — O(PR) prevention instead of O(incident) cleanup.

Design
Topic — design
Design problems on deployment and environment isolation

Practice →

ETL Topic — etl ETL problems on scheduling and release orchestration

Practice →

---

5. Dataform vs dbt on BigQuery

Same SQL-first ELT core — pick Dataform for a native GCP fit, dbt for portability and packages

The mental model in one line: Dataform and dbt solve the same problem — SQL-first in-warehouse transformation with a ref()-built DAG, tests/assertions, docs, and incremental models — so the choice is not about capability but about fit: Dataform is BigQuery-only, a native Google Cloud service with zero infrastructure and IAM-integrated execution, using SQLX plus a JavaScript API for programmatic model generation, and it is free-managed; dbt is multi-warehouse, uses Jinja templating and a large packages ecosystem, and runs via dbt Cloud or self-hosted dbt Core — so you pick Dataform when you are all-in on BigQuery/GCP and want the native, no-infra path, and dbt when you need warehouse portability, its package ecosystem, or Jinja macros. Both compile SQL and let BigQuery do the work; the edges differ.

The shared model — what both give you.

• ref() and a DAG. Both derive run order from ref() edges and topologically sort the graph.
• Tests / assertions. Dataform's assertions and dbt's tests both express data-quality checks that gate or warn.
• Incremental models. Both support incremental materialisations with a unique key and an incremental predicate.
• Docs and lineage. Both generate a documentation site / graph with column-level descriptions and lineage.

Where Dataform is distinct.

• BigQuery-native, zero infra. A managed Google Cloud service inside the BigQuery console — no runner to host, no dbt Cloud subscription.
• IAM-integrated execution. Runs as a GCP service account under normal BigQuery IAM.
• SQLX + a JavaScript API. SQL with a config block, plus includes/*.js and publish() for programmatic generation — JavaScript instead of Jinja.
• Free-managed. You pay BigQuery compute; the orchestration is included.

Where dbt is distinct.

• Multi-warehouse. Snowflake, Redshift, Databricks, Postgres, BigQuery, and more via adapters — portability Dataform does not offer.
• Jinja + macros. A mature templating language and macro system for reusable SQL logic.
• Packages ecosystem. dbt-utils, dbt_expectations, elementary, and many others — a large community library.
• dbt Cloud or Core. A managed SaaS or a self-hosted open-source runner, independent of any one cloud.

The JavaScript API — Dataform's templating.

• includes/*.js. Reusable constants and functions imported into SQLX; the equivalent of dbt macros, in JavaScript.
• publish(). Generate models programmatically — loop over a config list to emit N structurally-identical tables (per-region, per-source) from one script.
• Inline ${ }. Any JavaScript expression can be interpolated into SQL, so templating is a real language, not a DSL.

The failure modes practitioners pre-empt.

• Choosing for portability you never use. Picking dbt "in case we switch warehouses" while committed to BigQuery adds Jinja and infra for a migration that never comes. Mitigation: pick for the warehouse you actually run.
• Rebuilding dbt packages by hand in Dataform. Reimplementing what a dbt package already does. Mitigation: weigh the package ecosystem honestly when it is load-bearing.
• Ignoring the JS API. Copy-pasting near-identical SQLX instead of generating it with publish(). Mitigation: templatise repetitive models in JavaScript.

Common interview probes on the choice.

• "Dataform or dbt for a BigQuery-only shop?" — Dataform for the native, no-infra, IAM-integrated fit; dbt if you need its packages or Jinja.
• "What's Dataform's templating?" — the JavaScript API (includes, publish()), versus dbt's Jinja.
• "When does dbt clearly win?" — multi-warehouse portability or heavy reliance on the package ecosystem.
• "What do they share?" — SQL-first ELT, ref()/DAG, tests, incremental, docs.

Worked example — generating N models with the JavaScript API

Detailed explanation. The clearest thing the JS API buys you is generating many structurally-identical models from one script — the Dataform answer to a dbt macro loop. Generate a per-region daily-sales table for a list of regions with publish().

• The list. Regions ["EU", "US", "APAC"].
• The loop. forEach calling publish() to emit one table per region.
• The result. Three tables from one file, no copy-paste.

Question. Use the JavaScript API to generate a daily_sales_<region> table per region from a single definitions file.

Input.

Piece	Value
Regions	["EU", "US", "APAC"]
Generator	publish() in a loop
Output	daily_sales_eu, daily_sales_us, daily_sales_apac
Source	${ref("stg_orders")}, filtered by region

Code.

// definitions/daily_sales_by_region.js — generate one model per region.
const REGIONS = ["EU", "US", "APAC"];

REGIONS.forEach((region) => {
  publish(`daily_sales_${region.toLowerCase()}`, {
    type: "table",
    schema: "marts",
    tags: ["daily"],
  }).query((ctx) => `
    SELECT order_date, region, SUM(revenue) AS revenue
    FROM ${ctx.ref("stg_orders")}
    WHERE region = '${region}'
    GROUP BY order_date, region
  `);
});

-- One of the three tables this compiles to (daily_sales_eu):
CREATE OR REPLACE TABLE `my-project.marts.daily_sales_eu` AS
SELECT order_date, region, SUM(revenue) AS revenue
FROM `my-project.staging.stg_orders`
WHERE region = 'EU'
GROUP BY order_date, region;
-- ... plus daily_sales_us and daily_sales_apac, structurally identical.

Step-by-step explanation.

1. The file is JavaScript (.js), so it can loop: REGIONS.forEach(...) runs the body once per region, and each iteration calls publish() to register a model.
2. publish("daily_sales_eu", {config}) is the programmatic equivalent of a SQLX config block — it sets the model name, type, schema, and tags without a separate file per table.
3. .query((ctx) => ...) supplies the SQL as a template string, where ctx.ref("stg_orders") is the same ref() you use in SQLX — so every generated model still records its dependency edge.
4. The region value is interpolated with plain JavaScript ('${region}'), so the three generated models differ only in the filter and the name — one script, three maintained-in-lockstep tables.
5. This is the JS API's role as Dataform's templating: instead of Jinja macros, you use JavaScript to generate SQL, which means loops, arrays, imported includes/*.js helpers, and real expressions — but it stays BigQuery-specific.

Output.

Generated model	Filter	Dependency edge
daily_sales_eu	region = 'EU'	stg_orders → daily_sales_eu
daily_sales_us	region = 'US'	stg_orders → daily_sales_us
daily_sales_apac	region = 'APAC'	stg_orders → daily_sales_apac
(all)	one .js file	three graph nodes

Rule of thumb. Use the JavaScript API (publish() in a loop, includes/*.js helpers) to generate repetitive, structurally-identical models from one script instead of copy-pasting SQLX — it is Dataform's answer to dbt's Jinja macros, in a real language, at the cost of being BigQuery-specific.

Worked example — the same transform in Dataform SQLX and dbt

Detailed explanation. Seeing one transform written both ways makes the templating difference concrete: SQLX config + ${ref} versus dbt's {{ config }} + {{ ref }} Jinja. Write an incremental model in each.

• Dataform. config { } block, ${ref()}, when(incremental()).
• dbt. {{ config() }} Jinja, {{ ref() }}, {% if is_incremental() %}.
• The point. Same DAG semantics, different template syntax and portability.

Question. Express the same incremental fct_events model in Dataform SQLX and in dbt, and identify what differs.

Input.

Aspect	Dataform (SQLX)	dbt (Jinja)
Config	config { } block	{{ config(...) }}
Reference	${ref("x")}	{{ ref('x') }}
Incremental guard	when(incremental(), ...)	{% if is_incremental() %}
Warehouse	BigQuery only	any adapter

Code.

-- Dataform SQLX
config {
  type: "incremental",
  schema: "marts",
  uniqueKey: ["event_id"],
  bigquery: { partitionBy: "DATE(event_ts)" }
}
SELECT event_id, user_id, event_ts, event_type
FROM ${ref("stg_events")}
${ when(incremental(), `WHERE event_ts > (SELECT MAX(event_ts) FROM ${self()})`) }

-- dbt (Jinja) — same DAG semantics, different templating, warehouse-portable.
{{ config(
     materialized='incremental',
     unique_key='event_id',
     partition_by={'field': 'event_ts', 'data_type': 'timestamp'}
) }}
SELECT event_id, user_id, event_ts, event_type
FROM {{ ref('stg_events') }}
{% if is_incremental() %}
  WHERE event_ts > (SELECT MAX(event_ts) FROM {{ this }})
{% endif %}

Step-by-step explanation.

1. Both declare an incremental materialisation with a unique key and a partition — the semantics are identical, and both compile to a MERGE on BigQuery.
2. Dataform's config is a native config { } block and its reference is ${ref("stg_events")}; dbt's config is a Jinja {{ config(...) }} call and its reference is {{ ref('stg_events') }} — the same idea in two template languages.
3. The incremental guard is when(incremental(), ...) in Dataform versus {% if is_incremental() %} in dbt, and self() versus this for the table being built — direct analogues.
4. The one substantive difference is portability: the dbt version, with a different adapter and partition_by, runs on Snowflake or Redshift too; the Dataform version is BigQuery-specific by design.
5. So the choice between them is not "which can express this" — both can — but "which fit do I want": native-GCP/JavaScript/no-infra (Dataform) or multi-warehouse/Jinja/packages (dbt).

Output.

Feature	Dataform	dbt
Incremental MERGE on BigQuery	yes	yes
Templating language	JavaScript	Jinja
Runs on Snowflake/Redshift	no	yes
Managed with zero infra	yes (GCP native)	dbt Cloud or self-host

Rule of thumb. Dataform and dbt express the same incremental model with analogous primitives (config/ref/when(incremental()) vs {{ config }}/{{ ref }}/is_incremental()), so choose on fit, not capability: Dataform for a native, zero-infra BigQuery path in JavaScript, dbt for warehouse portability and its package ecosystem in Jinja.

Worked example — the decision matrix

Detailed explanation. The interview-ready output is a decision matrix mapping a situation to Dataform or dbt. Build it across the axes that actually decide it: warehouse commitment, infra appetite, ecosystem needs, and templating preference.

• Warehouse. BigQuery-only vs multi-warehouse (now or planned).
• Infra. Want zero infra / native GCP vs willing to run dbt Cloud/Core.
• Ecosystem. Need dbt packages vs comfortable building in JS.

Question. Produce a decision matrix that picks Dataform or dbt from a team's warehouse, infra, and ecosystem constraints.

Input.

Situation	Leans
All-in on BigQuery, want no infra	Dataform
Multi-warehouse now or soon	dbt
Heavy reliance on dbt packages	dbt
Want IAM-native, service-account runs	Dataform

Code.

Dataform vs dbt on BigQuery — decision matrix
=============================================

Pick DATAFORM when:
  - You are committed to BigQuery / GCP (no multi-warehouse plan).
  - You want a native, managed service with ZERO infra to host.
  - You want IAM-integrated, service-account execution inside GCP.
  - You prefer JavaScript templating (includes + publish()) over Jinja.
  - You want the orchestration free (pay only BigQuery compute).

Pick DBT when:
  - You run (or will run) multiple warehouses — Snowflake, Redshift, Databricks.
  - You depend on the package ecosystem (dbt-utils, dbt_expectations, elementary...).
  - Your team already knows Jinja/dbt and values portability.
  - You want dbt Cloud's IDE/scheduler or a warehouse-agnostic dbt Core setup.

Shared (NOT a differentiator — both do these):
  - ref()-built DAG, tests/assertions, incremental models, docs/lineage, SQL-first ELT.

Tie-breaker: if you are BigQuery-only and greenfield, Dataform's native fit and
zero infra usually win; if portability or packages are load-bearing, dbt wins.

Step-by-step explanation.

1. The matrix leads with the decisive axis — warehouse commitment: a genuine multi-warehouse need points at dbt immediately, because Dataform is BigQuery-only.
2. Absent that, infra appetite decides: Dataform's zero-infra, IAM-native, free-managed model is a strong pull for a BigQuery-committed team that does not want to run dbt Cloud or self-host dbt Core.
3. The ecosystem axis is the honest counterweight: if load-bearing dbt packages already solve your problem, reimplementing them in Dataform's JS API is real work — weigh it.
4. Templating preference (JavaScript vs Jinja) is a genuine but secondary factor; it rarely overrides warehouse and ecosystem, but it matters for team ergonomics.
5. The shared-capabilities line is the senior move: naming that both do ref()/DAG/tests/incremental/docs prevents the mistake of picking on a "feature" both have — the decision is fit, not capability.

Output.

Constraint	Dataform	dbt
BigQuery-only, zero infra	strong fit	works
Multi-warehouse	not possible	strong fit
dbt package reliance	rebuild in JS	native
IAM-native GCP runs	native	configure
Free-managed orchestration	yes	dbt Cloud paid / self-host Core

Rule of thumb. Decide on warehouse commitment first (multi-warehouse → dbt), then infra appetite (zero-infra native GCP → Dataform), then ecosystem (load-bearing dbt packages → dbt) — and never decide on a "feature" both tools share. For a BigQuery-committed, greenfield team, Dataform's native fit usually wins; where portability or packages are load-bearing, dbt does.

Senior interview question on choosing a transformation framework

A senior interviewer might ask: "A BigQuery-committed team asks whether to adopt Dataform or dbt for their transformation layer. Walk through how you'd decide: what the two share so you don't pick on a non-differentiator, where each is genuinely distinct, how Dataform's JavaScript API compares to dbt's Jinja, and what would flip your recommendation from one to the other."

Solution Using a shared-core baseline, the distinct edges, and a fit-driven decision

# 1. Establish the shared core FIRST (so the decision isn't made on a non-differentiator):
#    ref()-built DAG, tests/assertions, incremental models, docs/lineage, SQL-first ELT.
#    Both compile SQL and let BigQuery do the work. This is NOT where the choice is made.

-- 2. Dataform's distinct edge: native GCP, JS templating, zero infra.
--    includes/constants.js
const ACTIVE_STATUSES = ["paid", "refunded"];   // reusable across models (like a macro)

// definitions/orders_active.js — generate a model using the shared include + publish().
publish("orders_active", { type: "table", schema: "marts" }).query((ctx) => `
  SELECT * FROM ${ctx.ref("stg_orders")}
  WHERE status IN (${constants.ACTIVE_STATUSES.map((s) => `'${s}'`).join(", ")})
`);

# 3. The fit-driven decision (what flips the recommendation):
#    DEFAULT for this BigQuery-committed team -> DATAFORM
#      native GCP service, zero infra, IAM-integrated, JS API, free-managed.
#    FLIP to DBT if:
#      - a second warehouse (Snowflake/Redshift/Databricks) is on the roadmap  -> portability
#      - load-bearing dbt packages (dbt_expectations, elementary) are needed   -> ecosystem
#      - the team is deep in Jinja/dbt already and values that over native fit  -> ergonomics

Step-by-step trace.

Axis	Shared?	Decides toward
ref()/DAG, tests, incremental, docs	yes	neither (baseline)
Warehouse portability	no	dbt if multi-warehouse
Native GCP + zero infra	no	Dataform
Templating	no	JS → Dataform / Jinja → dbt
Package ecosystem	no	dbt if load-bearing

For a BigQuery-committed team, the recommendation defaults to Dataform: it is a native Google Cloud service with no infrastructure to host, IAM-integrated execution, a JavaScript API (includes + publish()) that covers the same ground as dbt macros, and free-managed orchestration. The recommendation flips to dbt only on a concrete, load-bearing need — a real multi-warehouse roadmap, reliance on specific dbt packages, or a team already deep in Jinja. The disciplined part is establishing the shared core first, so neither tool is chosen on a capability both have.

Output:

Metric	Pick on hype	Pick on fit
Decision basis	"feature" both share	warehouse + infra + ecosystem
BigQuery-only team	either (arbitrary)	Dataform (native fit)
Multi-warehouse team	maybe stuck	dbt (portability)
Templating	unexamined	JS vs Jinja, deliberate
Regret risk	high	low

Why this works — concept by concept:

• Shared core baseline — naming that both tools give you ref()/DAG, tests, incremental, and docs stops the decision being made on a non-differentiator, which is the most common way this choice goes wrong.
• Distinct edges — Dataform's native-GCP/zero-infra/IAM/JS-API profile versus dbt's multi-warehouse/Jinja/packages profile is where the real decision lives, so the comparison focuses there.
• JavaScript API vs Jinja — includes and publish() give Dataform real templating (loops, arrays, expressions) as the counterpart to dbt macros, so "templating" is not a reason to pick dbt for a BigQuery team.
• Fit-driven flip — tying the recommendation to concrete, load-bearing needs (a real second warehouse, a load-bearing package) makes the decision defensible and reversible only for a stated reason.
• Cost — one deliberate decision on warehouse/infra/ecosystem, versus adopting a tool for capabilities both share and paying migration or infra cost you did not need. The eliminated cost is a framework switch driven by a non-differentiator — O(fit analysis) instead of O(re-platform).

Design
Topic — design
Design problems on tooling and architecture trade-offs

Practice →

Optimization
Topic — optimization
Optimization problems on model materialization and cost

Practice →

---

Cheat sheet — Dataform recipes

• What Dataform is. A BigQuery-native transformation framework: .sqlx files (SQL + a config block) compile to CREATE OR REPLACE TABLE/VIEW/MERGE, ref() builds the dependency graph, and BigQuery does all the work (ELT). It is a free managed Google Cloud service inside the BigQuery console; you pay BigQuery, not Dataform.
• SQLX model skeleton. config { type: "view"|"table"|"incremental", schema: "...", tags: [...], bigquery: { partitionBy, clusterBy }, assertions: {...} } then one SELECT ... FROM ${ref("upstream")}. Declare raw sources once with type: "declaration"; reference everything via ref() — never a hard-coded name (it drops the graph edge).
• The graph. Every ref() = a fully-qualified name + an edge. Dataform topologically sorts the DAG; a bad ref or a cycle is a compile error. dependencies: [...] adds a non-ref edge. tags + --tags <t> --include-dependencies run named subsets over one graph.
• Incremental + MERGE. type: "incremental" + when(incremental(),WHERE ts > (SELECT MAX(ts) FROM ${self()})) processes only new rows. Add uniqueKey: [...] so it compiles to an idempotent MERGE (re-running a window dedupes); without it, it's an append INSERT. Always partitionBy the incremental column so probe + merge prune. --full-refresh rebuilds.
• Assertions (inline). In config.assertions: uniqueKey (→ GROUP BY ... HAVING COUNT(*)>1), nonNull (→ WHERE col IS NULL), rowConditions: ["revenue >= 0", ...] (→ WHERE NOT (cond)). Each is a query that must return zero rows to pass.
• Assertions (manual). type: "assertion" SQLX whose SELECT returns the violating rows (cross-table referential integrity, reconciliation, freshness). Make critical dependents dependOnDependencyAssertions: true so a failure blocks the build; leave monitors non-blocking. Scope checks to partitioned columns so they stay cheap every run.
• Environments. workflow_settings.yaml sets defaultProject/defaultDataset/defaultLocation/dataformCoreVersion/vars. A codeCompilationConfig with schemaSuffix (e.g. dev, ci_pr_42) or a dev dataset redirects destinations; read env values via ${dataform.projectConfig.vars.x}. Same SQLX → dev, CI, and prod objects, no code fork.
• CI/CD. Dev = isolated workspace (per-dev schema suffix). PR gate = dataform compile (structural) + dataform run into a per-PR dataset executing assertions (data quality), as required checks. Prod = a release config (compile main + prod vars → compilation result) run by a workflow config (cron + tags + service account); a failed assertion fails the invocation.
• JavaScript API. includes/*.js for reusable constants/functions (Dataform's macros); publish("name", {config}).query(ctx =>... ${ctx.ref("x")} ...) in a loop to generate N models. JavaScript templating, not Jinja — BigQuery-specific.
• Dataform vs dbt. Shared: ref()/DAG, tests/assertions, incremental, docs — don't decide here. Dataform: BigQuery-only, native GCP, zero infra, IAM-integrated, JS API, free-managed. dbt: multi-warehouse, Jinja, packages ecosystem, dbt Cloud/Core. Pick Dataform for a BigQuery-committed, zero-infra path; dbt for portability or load-bearing packages.
• Object types. declaration (source), view/table/incremental (models), assertion (zero-row check), operations (arbitrary SQL: grants/DDL/calls), includes (JS). Views cost the reader; tables cost the build; incrementals cost only new rows.

Frequently asked questions

What is Dataform and how does it work with BigQuery?

Dataform is a transformation framework for BigQuery in which each model is a .sqlx file — ordinary BigQuery SQL wrapped in a config block — that Dataform compiles into the concrete CREATE OR REPLACE TABLE/VIEW/MERGE statements BigQuery runs. It does not process data itself; it is a compiler and orchestrator that resolves every ref() call into a fully-qualified table name and a dependency edge, assembles those edges into a directed acyclic graph, topologically sorts it, and issues the SQL jobs to BigQuery in dependency order. That makes it ELT: raw data is already loaded into BigQuery, and the whole transformation runs inside BigQuery. Dataform is now a first-class Google Cloud service inside the BigQuery console, so you create a repository, work in a workspace, and schedule runs without hosting any infrastructure — you pay for the BigQuery jobs, and the orchestration itself is free.

Dataform vs dbt on BigQuery — which should I pick?

Both solve the same problem — SQL-first in-warehouse transformation with a ref()-built DAG, tests/assertions, incremental models, and docs — so decide on fit, not capability. Pick Dataform when you are committed to BigQuery and GCP and want the native path: a managed service with zero infrastructure to host, IAM-integrated execution as a service account, a JavaScript API (includes + publish()) for templating, and free-managed orchestration. Pick dbt when you run or plan to run multiple warehouses (Snowflake, Redshift, Databricks), depend on its package ecosystem (dbt-utils, dbt_expectations, elementary), or your team is invested in Jinja and values warehouse portability. The decisive axis is usually warehouse commitment — a real multi-warehouse need points at dbt immediately — followed by infrastructure appetite and how load-bearing dbt's packages are for you. For a BigQuery-only, greenfield team, Dataform's native fit and zero infra usually win.

What is SQLX and how is it different from plain SQL?

SQLX is Dataform's file format: plain BigQuery Standard SQL plus a config { } header (and optional js { }, pre_operations, and post_operations blocks). The config block declares what the file builds — its type (view, table, incremental, assertion, operations), destination schema/database, tags, description and columns documentation, BigQuery physical options like partitionBy and clusterBy, and inline assertions. The one Dataform-specific token inside the SQL is ${ref("model")}, which resolves to a fully-qualified table name and records a dependency edge, so the graph is derived from the SQL itself. Because destinations and dependencies live in config rather than hard-coded strings, the same SQLX file compiles to different objects in dev, CI, and prod purely through compilation overrides — something plain SQL scripts with literal table names cannot do.

How do Dataform assertions enforce data quality?

An assertion is a data-quality check that compiles to a BigQuery query which must return zero rows to pass — the returned rows are the violations. You declare common checks inline in a model's config.assertions: uniqueKey generates a GROUP BY ... HAVING COUNT(*) > 1 to catch duplicate keys, nonNull returns rows where a required column is null, and rowConditions returns rows that fail a boolean rule like revenue >= 0. For cross-table rules — referential integrity, reconciliation, freshness — you write a manual type: "assertion" SQLX whose SELECT returns the offending rows. Because assertions are nodes in the dependency graph, a downstream model that sets dependOnDependencyAssertions: true waits for its inputs' assertions to pass, so a failed check blocks the build and bad data never propagates; monitors you only want to know about are left non-blocking. Scope assertion queries to partitioned columns so a check that runs every build stays cheap.

How does CI/CD work in Dataform?

The project is a Git repository, and the path to production has four parts. Each engineer works in an isolated dev workspace that compiles and runs into their own dataset (via a schemaSuffix like _dev or their name), so nobody touches prod or collides. Every pull request runs a CI gate — dataform compile catches a bad ref(), a typo, or a cycle, and a dataform run into a throwaway per-PR dataset executes the assertions to catch data-quality regressions — as required checks with branch protection, so main stays releasable. A release config compiles a chosen commitish (usually main) with production vars into a versioned compilation result, and a workflow config executes that result on a cron as a least-privilege service account, optionally filtered by tags; a failed assertion fails the invocation so a bad build never publishes. Because environment differences live in vars and compilation overrides, the identical SQLX produces dev, CI, and prod objects with no code fork.

Is Dataform free, and where does it run now?

The Dataform framework is open source, and Google runs it as a free managed service — you pay only for the BigQuery jobs it triggers (the compute and storage), not for the orchestration, scheduling, or Git integration. It now runs as a first-class part of Google Cloud, embedded directly in the BigQuery console: you create a Dataform repository, connect it to GitHub, GitLab, Azure DevOps, or Cloud Source Repositories, edit SQLX in a workspace with compilation and execution against your own datasets, and schedule production runs with release and workflow configs — all without deploying or hosting any infrastructure. Execution happens under normal BigQuery IAM as a service account, so access control is the same model as the rest of your GCP estate. If you prefer, the open-source dataform CLI (compile, run, test) drives the identical project from any CI system.

Practice on PipeCode

• Drill the data transformation practice library → for the in-warehouse modelling, incremental-table, and dependency-graph patterns that SQLX and ref() make concrete.
• Harden your checks on the data validation practice library → for the uniqueness, non-null, referential-integrity, and reconciliation problems Dataform assertions encode as zero-row checks.
• Sharpen the pipeline-architecture axis with the system design practice library → for the environment-isolation, release, scheduling, and tooling trade-offs a Dataform CI/CD setup must get right.
• Stack the prerequisites against PipeCode's broader 450+ data-engineering catalogue to anchor the ELT-modelling, data-quality, and incremental-processing patterns against real graded inputs — transformation, validation, and pipeline design.

Lock in Dataform muscle memory

Docs explain SQLX, assertions, and release configs. PipeCode drills explain the decision — when a table should be `incremental` with a `uniqueKey`, when an assertion must block the build instead of just warning, and when Dataform's native BigQuery fit beats dbt's portability. Pipecode.ai is Leetcode for Data Engineering — transformation and data-quality practice tuned for the production trade-offs data engineers actually face.

Practice data transformation problems →
Practice data validation problems →