A short course

From raw rows
to a trusted warehouse

How dbt and BigQuery turn scattered source tables into a documented, tested, dependency-aware pipeline — using nothing fancier than SELECT and WHERE.

Roadmap

Nine ideas, in build order

Everything here assumes you can already write SELECT and WHERE. No window functions required — these concepts live one level up, in how models are organized, tested, documented, and physically built.

Foundation

source() and ref()

Two functions decide the shape of your whole pipeline. source() names a raw table dbt doesn't manage. ref() names another dbt model. Together they let dbt draw the full map of what depends on what.

# sources.yml — declare the raw table once
sources:
  - name: raw_ecommerce
    tables:
      - name: orders

-- models/staging/stg_orders.sql
select
  order_id,
  customer_id,
  order_date
from {{ source('raw_ecommerce', 'orders') }}

-- models/marts/fct_orders.sql
select *
from {{ ref('stg_orders') }}
where order_date >= '2024-01-01'

Because fct_orders calls ref('stg_orders') instead of typing a table name, dbt knows to build stg_orders first, every time.

Why it matters

ref() builds the DAG for you

A hardcoded table name is a dead end. A ref() is a live pointer — dbt reads every ref() and source() call in your project and assembles them into a directed acyclic graph: the same node network drifting behind this deck.

Build strategy

Materializations

A materialization is dbt's answer to: when this model runs, what does BigQuery actually end up storing? Set it with one line of config.

{{ config(materialized='table') }}

BigQuery storage

partition_by

BigQuery can physically split a table into date-sized chunks. A query that filters on the partition column only reads the chunks it needs — less data scanned, lower cost, faster results. Still just WHERE.

{{ config(
    materialized='incremental',
    partition_by={'field': 'order_date', 'data_type': 'date'}
) }}

select * from {{ ref('stg_orders') }}
-- on later runs, only process what's new
{% if is_incremental() %}
where order_date > (select max(order_date) from {{ this }})
{% endif %}

is_incremental() is true only after the table already exists, so the first build processes everything, and every run after processes only the rows past the last watermark.

Keeping re-runs clean

unique_key

Incremental models add new rows on every run — but source data sometimes sends an update to a row you've already loaded. unique_key tells dbt which column identifies "the same row," so an update replaces instead of duplicating.

{{ config(
    materialized='incremental',
    unique_key='order_id',
    incremental_strategy='merge'
) }}

Reusable logic

Macros

A macro is a snippet of SQL written once in Jinja and reused across models — the closest thing dbt has to a function. Anything you'd otherwise copy-paste into ten models, write once instead.

-- macros/cents_to_dollars.sql
{% macro cents_to_dollars(column_name) %}
    ({{ column_name }} / 100)
{% endmacro %}

-- used inside any model, plain SQL either way
select
  order_id,
  {{ cents_to_dollars('amount_cents') }} as amount_dollars
from {{ ref('stg_orders') }}

The macro is expanded at compile time — BigQuery only ever sees the final, ordinary SQL, so no new query concepts to learn.

Catching bad data

Built-in tests

A test is a query that should return zero rows. dbt ships four generic tests you attach to a column in schema.yml — no SQL to write, just config.

# models/staging/schema.yml
models:
  - name: stg_orders
    columns:
      - name: order_id
        tests:
          - unique
          - not_null
      - name: status
        tests:
          - accepted_values:
              values: ['placed', 'shipped', 'returned']
      - name: customer_id
        tests:
          - relationships:
              to: ref('stg_customers')
              field: customer_id

dbt test runs every one of these and reports which rows broke the rule — caught before anyone downstream sees bad numbers.

Beyond the four built-ins

Custom (singular) tests

Some rules are specific to your business and don't fit unique or not_null. A singular test is just a .sql file that selects the rows that shouldn't exist — if it returns anything, the test fails.

-- tests/assert_order_total_is_positive.sql
-- fails if this query returns any rows
select
  order_id,
  order_total
from {{ ref('fct_orders') }}
where order_total <= 0

Same SELECT ... WHERE you already know — the only new idea is that "passing" means the query comes back empty.

Deeper data quality

Great Expectations

Great Expectations is a standalone Python framework for data quality — you write "expectations" like expect_column_values_to_be_between or expect_column_mean_to_be_between and run them as a validation suite, independent of dbt.

# schema.yml, using the dbt-expectations package
columns:
  - name: order_total
    tests:
      - dbt_expectations.expect_column_values_to_be_between:
          min_value: 0
          max_value: 100000

dbt's built-in tests answer is this row valid? Great Expectations-style checks can also answer does this whole column still look right? — ranges, distributions, formats.

Trust, readably

Documentation lives in schema.yml

The same file that holds your tests also holds plain-English descriptions. Write them once, and dbt turns them into a browsable, searchable site.

# models/marts/schema.yml
models:
  - name: fct_orders
    description: "One row per order, ready for reporting."
    columns:
      - name: order_id
        description: "Primary key for an order."
      - name: order_total
        description: "Order amount in USD, after discounts."

Native to BigQuery

The APPENDS() table function

BigQuery can hand you exactly the rows added to a table in a time window, without dbt at all. APPENDS() is a table-valued function — call it like a table, filter it like one.

select *
from APPENDS(
  TABLE mydataset.orders,
  TIMESTAMP('2024-01-01 00:00:00'),
  TIMESTAMP('2024-01-02 00:00:00')
)
where _CHANGE_TYPE = 'INSERT'

It reads straight from BigQuery's change history — no timestamp column of your own required, no rescanning the whole table. Same SELECT ... WHERE shape you already know.

Cheat sheet

Recap

End

source → ref → materialize → partition → merge on unique_key → wrap the repeated parts in a macro → test it → document it. That's the whole loop most dbt projects run on.