Skip to content
LakeBench
ProblemsCommunityPricing
Sign inStart practicing
Back
  1. Home
  2. Interview prep
  3. BigQuery and dbt or Dataform

Snowflake, BigQuery & Databricks · BigQuery

BigQuery and dbt or Dataform

Mediumwarehouses-36
bigquerydataformdbttransformations

Question

How do Dataform and dbt compare for transformations in BigQuery?

Solution

Dataform and dbt solve the same problem: managing SQL transformations as code, with dependencies, incremental builds and tests. Dataform is Google's own tool and lives inside BigQuery. dbt is a separate tool with a larger community that works across many warehouses.

What they share

  • Models written as SQL files with references to other models, so the tool builds a dependency graph and runs things in the right order.
  • Incremental tables that process only new data.
  • Tests or assertions on data, such as uniqueness and not-null checks.
  • Documentation, version control and CI deployment.

Dataform

config { type: "incremental", uniqueKey: ["order_id"] }

SELECT * FROM ${ref("stg_orders")}
${when(incremental(), `WHERE updated_at > (SELECT MAX(updated_at) FROM ${self()})`)}

Files use SQLX, which is SQL plus a config block and JavaScript templating. It is built into the BigQuery console, with a Git-connected editor, scheduling through workflow configurations, and IAM-based access. There is no extra license cost for the tool itself: you pay for the BigQuery queries it runs. It only targets BigQuery.

dbt

Models are SQL plus Jinja, with a very large library of community packages, a mature testing framework, many adapters (Snowflake, Databricks, BigQuery, Postgres and more), and a big hiring pool of people who already know it. dbt Core is free and open source, and dbt Cloud (the hosted product) is paid. You need somewhere to run it, such as an orchestrator.

Choosing

  • Pick Dataform for a team that is all in on GCP and wants something simple, integrated and with no extra tooling.
  • Pick dbt if you work across multiple warehouses, want the package ecosystem, or already have dbt skills. Interviewers will most often ask about dbt.

Both give you the benefits of treating SQL as software. The tool matters less than the habits: small modular models, tests on keys, incremental loads, and reviews through pull requests.

PreviousNext