BQBrains | Dataform in BigQuery: Version-Controlled ELT Without Another Orchestrator

Most BigQuery estates grow the same way: a handful of scheduled queries, then thirty, then a spreadsheet that tries to record which one depends on which. Nobody can say what breaks if a column is renamed, and the only test is whether the dashboard looks wrong on Monday.

Dataform is Google's answer to that problem, and it is built into BigQuery at no extra charge — you pay only for the queries it runs. It gives you SQL-based transformations in Git, a dependency graph that is derived rather than declared by hand, assertions that fail loudly, and scheduled execution without standing up Airflow. This tutorial takes a small warehouse from raw tables to a tested, version-controlled pipeline.

What Dataform actually is

A Dataform repository is a Git repository of .sqlx files. Each file defines one output object — a table, an incremental table, a view, or an assertion — using SQL plus a small config block. Dataform compiles the whole repository into a DAG, works out the execution order from the ref() calls between files, and issues the resulting CREATE OR REPLACE/MERGE statements against BigQuery.

Three things follow from that design:

The graph is inferred. You never maintain a dependency list; if model B references model A, the edge exists.
Environments are cheap. The same code compiles into prod_analytics or dev_alice_analytics by changing one compilation variable, so developers get a private sandbox.
It is still just BigQuery SQL. No new engine, no data leaving the warehouse, no proprietary runtime to debug at 2am.

If your team already uses dbt Core, the mental model is nearly identical; the differences are that Dataform is managed inside the Google Cloud console, uses .sqlx and JavaScript instead of Jinja, and needs no separate hosting.

Step 1 — create the repository and workspace

In the console, go to BigQuery → Dataform, create a repository in the same region as your datasets, then create a development workspace inside it. The workspace is your branch; nothing you run there touches production.

Dataform executes as a service account (by default service-<project-number>@gcp-sa-dataform.iam.gserviceaccount.com). Grant it what it needs and nothing more:

BigQuery Data Editor   on the output datasets
BigQuery Data Viewer   on the raw/source datasets
BigQuery Job User      on the execution project

Then set your defaults in workflow_settings.yaml:

defaultProject: acme-analytics
defaultDataset: analytics
defaultLocation: US
defaultAssertionDataset: analytics_assertions
vars:
  env: dev

Step 2 — declare your sources

Never let a model point at a raw table by name. Declare it once, in definitions/sources/raw_orders.sqlx:

config {
  type: "declaration",
  database: "acme-ingest",
  schema: "raw",
  name: "orders",
  description: "Raw orders landed by Datastream CDC."
}

Now ${ref("orders")} resolves everywhere, and if the raw table moves you change one file.

Step 3 — a staging model

definitions/staging/stg_orders.sqlx:

config {
  type: "view",
  schema: "staging",
  description: "Typed, deduplicated orders.",
  columns: {
    order_id: "Natural key from the source system.",
    ordered_at: "Order timestamp in UTC.",
    net_amount: "Order value excluding tax, in USD."
  }
}

SELECT
  CAST(order_id AS STRING)        AS order_id,
  CAST(customer_id AS STRING)     AS customer_id,
  TIMESTAMP(ordered_at)           AS ordered_at,
  ROUND(amount_cents / 100, 2)    AS net_amount,
  _cdc_op                         AS cdc_op
FROM ${ref("orders")}
QUALIFY ROW_NUMBER() OVER (PARTITION BY order_id ORDER BY _cdc_timestamp DESC) = 1

Column descriptions in the config block are pushed into BigQuery as table metadata — your data catalogue gets populated as a side effect of writing the model.

Step 4 — an incremental fact table

Full rebuilds of a billion-row fact table are the most common source of surprise BigQuery bills. Incremental models fix that:

config {
  type: "incremental",
  schema: "marts",
  uniqueKey: ["order_id"],
  bigquery: {
    partitionBy: "DATE(ordered_at)",
    clusterBy: ["customer_id"],
    updatePartitionFilter: "ordered_at >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 3 DAY)"
  },
  assertions: {
    uniqueKey: ["order_id"],
    nonNull: ["order_id", "ordered_at", "net_amount"]
  }
}

SELECT
  order_id,
  customer_id,
  ordered_at,
  net_amount
FROM ${ref("stg_orders")}
WHERE cdc_op != 'DELETE'

${when(incremental(), `AND ordered_at >= (SELECT TIMESTAMP_SUB(MAX(ordered_at), INTERVAL 3 DAY) FROM ${self()})`)}

Two details matter more than the rest. uniqueKey turns the run into a MERGE rather than an append, so late-arriving CDC rows update in place instead of duplicating. updatePartitionFilter restricts the MERGE to recent partitions, which is what keeps the bytes billed — and the run time — small. Without it, the MERGE scans the entire target table on every run.

Step 5 — assertions: tests that run in production

The assertions block above generates uniqueness and non-null checks for free. Write bespoke ones as their own files when business logic needs guarding:

config {
  type: "assertion",
  description: "Daily revenue must never fall below zero or exceed 5x the trailing median."
}

WITH daily AS (
  SELECT DATE(ordered_at) AS d, SUM(net_amount) AS revenue
  FROM ${ref("fct_orders")}
  GROUP BY d
),
bounds AS (
  SELECT APPROX_QUANTILES(revenue, 2)[OFFSET(1)] * 5 AS upper_bound FROM daily
)
SELECT d, revenue
FROM daily, bounds
WHERE revenue < 0 OR revenue > bounds.upper_bound

An assertion passes when it returns zero rows. Because assertions are nodes in the same graph, downstream models can be configured to skip when an upstream assertion fails — the pipeline stops instead of quietly publishing bad numbers to a dashboard.

Step 6 — reusable logic without copy-paste

Dataform's JavaScript layer covers the cases where SQL alone gets repetitive. In includes/currency.js:

function toUsd(column, rateColumn) {
  return `ROUND(${column} * ${rateColumn}, 2)`;
}
module.exports = { toUsd };

Used as ${currency.toUsd("amount_local", "fx_rate")}. Use it sparingly: a repository where every model is generated by a loop is very clever and very hard to review.

Step 7 — environments and scheduling

Add a compilation override so dev output never lands in production datasets. In workflow_settings.yaml for the dev workspace, set defaultDataset: dev_${USER}_analytics; production release configurations keep analytics.

Then create, in the console:

A release configuration pinned to the main branch, compiling every few hours.
A workflow configuration that executes the compiled release on a cron schedule — for example 0 5 * * * — optionally restricted to tags such as hourly or daily, which you set per model via tags: ["daily"].

For CI, add a Cloud Build trigger on pull requests that calls the Dataform API to compile and dry-run the graph. A pull request that fails to compile never reaches main, and a schema change that breaks a downstream model is caught before it breaks the morning refresh.

Cost and operations notes

Dataform itself is free; every run is billed as ordinary BigQuery compute, so incremental models plus partition filters are where the savings live.
Query the execution history and tag your workloads — join Dataform runs against INFORMATION_SCHEMA.JOBS by label to see which model costs the most per day.
Give Dataform its own reservation or project assignment if you run editions, so a heavy backfill cannot starve analyst queries.
Keep the repository small enough to compile in seconds; a few hundred models is comfortable, thousands need tags and split repositories.

When Dataform is the wrong tool

Dataform orchestrates SQL inside BigQuery. It does not extract from APIs, it does not move files, and its scheduling is a cron, not a general-purpose DAG with sensors and branching. If your pipeline needs to wait for an SFTP drop, call a Python model, or coordinate work across Cloud Run and Dataflow, keep Cloud Composer or Workflows at the top and invoke a Dataform workflow as one step. That combination — Composer for orchestration, Dataform for transformation — is the arrangement we deploy most often.

Where to start

Pick your five most-referenced scheduled queries, convert them to .sqlx, declare their sources, and add one assertion to each. You will discover the real dependency graph within an afternoon, which is usually worth more than the automation.

If you want the migration done properly — repository layout, incremental strategy, CI on pull requests, and a reservation plan so the nightly run stops surprising your bill — our BigQuery data engineering team does this work every week. Get in touch with a description of your current scheduled-query sprawl and we will sketch the target graph.