Dataform vs dbt on GCP: A Comparison

Loading data into a warehouse is the easy half of analytics engineering. The other half is the hundred SQL statements that turn raw tables into trusted ones, and the questions they raise. Which runs first? What breaks if this column changes? Who tested it? Dataform and dbt both answer those questions in the same way: every table is a SELECT statement in a file, dependencies are declared in the code, tests run with the build, and everything lives in Git.

They are more alike than different. This comparison shows that first, with the same project written in each, and then sets out where they diverge.

The shared idea

Both tools ask you to write a model: a file containing a SELECT. The tool turns it into a table or view. To use another model you call a ref function instead of typing its name, and from those calls the tool builds a dependency graph and runs everything in order.

Here is a staging model in Dataform, in a SQLX file (Google Cloud: create tables):

config {
  type: "view",
  assertions: {
    uniqueKey: ["invoice_id"],
    nonNull: ["invoice_id", "customer_id"]
  }
}

SELECT InvoiceId AS invoice_id,
       CustomerId AS customer_id,
       CAST(InvoiceDate AS DATE) AS invoice_date,
       BillingCountry AS country,
       Total AS total
FROM ${ref("invoices")}

And the same model in dbt, as a SQL file with its tests in YAML (dbt: SQL models; dbt: data tests):

{{ config(materialized="view") }}

SELECT InvoiceId AS invoice_id,
       CustomerId AS customer_id,
       CAST(InvoiceDate AS DATE) AS invoice_date,
       BillingCountry AS country,
       Total AS total
FROM {{ source("raw", "invoices") }}

models:
  - name: stg_invoices
    columns:
      - name: invoice_id
        data_tests: [unique, not_null]
      - name: customer_id
        data_tests: [not_null]

The SELECT is identical. What differs is the wrapping: a config block and ${ } in one, Jinja's {{ }} and a YAML file in the other.

Both projects, checked locally

The script takes those models, plus a second one that totals revenue by country, and checks each project with its own tool. dbt builds the models and runs the tests against DuckDB, loaded with the Invoice table of the Chinook sample database (Chinook on GitHub). The open-source Dataform command-line tool compiles the SQLX project into SQL and a dependency graph. Neither step touches BigQuery, and the Dataform project is compiled, not executed.

# The same two models and tests, defined for dbt and for Dataform, checked locally. dbt builds them on
# DuckDB with the Chinook sample data; the open-source Dataform CLI compiles the SQLX project without
# connecting to anything. Neither step touches BigQuery.
import json
import os
import sqlite3
import subprocess
import sys

import duckdb

HERE = os.path.dirname(os.path.abspath(__file__))
DBT, DATAFORM = os.path.join(HERE, "dbt_project"), os.path.join(HERE, "dataform_project")

# Source data: the Chinook Invoice table, copied into a local DuckDB file as raw.invoices.
database = os.path.join(DBT, "warehouse.duckdb")
if os.path.exists(database):
    os.remove(database)
rows = sqlite3.connect(os.path.join(HERE, "../../sqlds/Chinook_Sqlite.sqlite")).execute(
    "SELECT InvoiceId, CustomerId, InvoiceDate, BillingCountry, Total FROM Invoice").fetchall()
con = duckdb.connect(database)
con.execute("CREATE SCHEMA raw")
con.execute("CREATE TABLE raw.invoices (InvoiceId INTEGER, CustomerId INTEGER, InvoiceDate TIMESTAMP,"
            " BillingCountry VARCHAR, Total DOUBLE)")
con.executemany("INSERT INTO raw.invoices VALUES (?, ?, ?, ?, ?)", rows)
con.close()

# dbt: build the models and run the tests.
dbt = os.path.join(os.path.dirname(sys.executable), "dbt")
subprocess.run([dbt, "build", "--project-dir", DBT, "--profiles-dir", DBT], cwd=DBT,
               stdout=subprocess.DEVNULL, stderr=subprocess.DEVNULL, check=True)
results = json.load(open(os.path.join(DBT, "target", "run_results.json")))["results"]
print("dbt build on DuckDB")
for result in sorted(results, key=lambda r: r["unique_id"]):
    kind, _, name = result["unique_id"].split(".", 2)
    print(f"  {kind:6} {name.split('.')[0][:44]:44} {result['status']}")
con = duckdb.connect(database, read_only=True)
print("  revenue_by_country, top 3:",
      con.execute("SELECT * FROM reporting.revenue_by_country ORDER BY revenue DESC, country LIMIT 3").fetchall())
con.close()

# Dataform: compile the SQLX project to SQL and a dependency graph.
compiled = json.loads(subprocess.run("npx -y @dataform/cli@latest compile --json", cwd=DATAFORM, shell=True,
                                     capture_output=True, text=True, check=True).stdout)
print("\nDataform compile")
for table in sorted(compiled["tables"], key=lambda t: t["target"]["name"]):
    depends = ", ".join(d["name"] for d in table.get("dependencyTargets", []))
    print(f"  {table['type']:6} {table['target']['schema']}.{table['target']['name']:22} depends on {depends}")
for assertion in sorted(compiled["assertions"], key=lambda a: a["target"]["name"]):
    print(f"  assert {assertion['target']['name']}")
print(f"  graph errors: {len(compiled.get('graphErrors', {}).get('compilationErrors', []))}")
query = next(t["query"] for t in compiled["tables"] if t["target"]["name"] == "stg_invoices")
print("  ref resolved to:", query.split("FROM")[1].strip())

dbt build on DuckDB
  model  revenue_by_country                           success
  model  stg_invoices                                 success
  test   not_null_stg_invoices_customer_id            pass
  test   not_null_stg_invoices_invoice_id             pass
  test   unique_stg_invoices_invoice_id               pass
  revenue_by_country, top 3: [('USA', 91, 523.06), ('Canada', 56, 303.96), ('France', 35, 195.1)]

Dataform compile
  table  reporting.revenue_by_country     depends on stg_invoices
  view   reporting.stg_invoices           depends on invoices
  assert reporting_stg_invoices_assertions_rowConditions
  assert reporting_stg_invoices_assertions_uniqueKey_0
  graph errors: 0
  ref resolved to: `analytics-prod.raw.invoices`

What the output shows:

• dbt built two models and passed three tests. The reporting table exists, and its top row is the United States with 91 invoices.
• Dataform compiled the same two actions and found the same dependencies. The two built-in assertions became two assertion actions, with no graph errors.
• The reference became a full table name. Dataform resolved ${ref("invoices")} to the declared source table. This is what both tools do with references, and why a table can be renamed in one place.

The structure, the graph and the tests are the same. So are most of the skills.

Where they differ

Scope. Dataform is a service inside Google Cloud for developing, testing and scheduling SQL workflows in BigQuery (Google Cloud: Dataform overview). dbt is a framework that runs against many warehouses (dbt: what is dbt?). If your company might move, or already runs more than one warehouse, that portability matters. If everything is and will be in BigQuery, it does not.

Language for reuse. Both let you avoid repeating SQL. Dataform uses JavaScript: functions and constants in include files. dbt uses Jinja macros. Neither is hard, and teams tend to prefer whichever they met first. JavaScript is a real programming language with ordinary tooling; Jinja is a templating language that most analysts find quicker to read.

Where it runs. Dataform is built into the console: a repository, a browser workspace, a compile step and scheduled runs, with nothing to install or host. dbt is a program you run, on a laptop, in a CI pipeline, from an orchestrator, or on dbt's hosted platform. Dataform's integration is the lower-effort start. dbt's independence is more flexible.

Tests. A Dataform assertion is a query that finds violating rows and fails if any are returned, and its results are stored as views you can inspect (Google Cloud: test data quality). A dbt data test is the same idea: a query that returns failing records. The vocabulary differs and the concept does not.

Cost. Dataform itself is free; you pay for the BigQuery queries it runs (Google Cloud: Dataform pricing). dbt's open source engine is free to run yourself, and you pay for BigQuery in the same way. The hosted dbt platform, which has a free developer plan and paid plans above it, adds scheduling, CI and monitoring so that you do not operate them yourself.

Ecosystem. This is dbt's clearest advantage. Its documentation cites a community of more than 100,000 members, and there are packages, courses and many job adverts that name it. Skills in it travel between employers and warehouses.

One note on versions. dbt now exists in two generations: the original Python engine, called v1, and a newer Rust engine, v2, which is the default when you install it today. The example above ran on v1. Project files are written the same way for both.

Choosing

1. Only BigQuery, a small team, and a wish for nothing to host? Dataform. It is already in your console and costs nothing extra.
2. More than one warehouse, now or plausibly later? dbt.
3. Hiring analytics engineers, or training people for the wider market? dbt is the more portable skill.
4. A team of JavaScript-comfortable engineers? Dataform's language will feel natural.
5. Existing dbt project? Keep it. dbt works well against BigQuery, and migrating between the two for its own sake has no payoff.

Whichever you pick, the habits are the same and matter more than the tool: one model per file, staging models that only rename and cast, tests on every key, and every change through a pull request.

What neither tool does

Both transform data that is already in the warehouse. Neither loads it; see loading data into BigQuery. Neither is a general scheduler for work outside SQL; for that see Cloud Composer. And for a handful of independent statements, scheduled queries may be all you need.

SQL transformation workflows are covered in the Data Engineering course.

Sources

• Dataform overview (Google Cloud Documentation) — Google Cloud
• Create tables (Google Cloud Documentation) — Google Cloud
• Test data quality (Google Cloud Documentation) — Google Cloud
• Dataform pricing (Google Cloud) — Google Cloud
• What is dbt? (dbt Developer Hub) — dbt Labs
• SQL models (dbt Developer Hub) — dbt Labs
• Add data tests to your DAG (dbt Developer Hub) — dbt Labs
• Chinook sample database (GitHub) — GitHub