dbt lab: first models and tests

A tidy folder tree does nothing until SQL runs and tests fail for the right reasons. This part is the hands-on half of the lab: write staging models that hug sources, build a small mart with ref(), attach unique and not-null tests, and run a build you can explain in a pull request without waving your hands. You will not learn every dbt command. You will learn a loop you can repeat for the next ten models.

This is Part 2 of dbt project lab. Part 1 covered layout: staging, intermediate, marts, naming, and grain sentences. Here we implement. Keep SQL sharp, treat tests as quality habits from Data quality, and remember warehouse transforms still sit inside broader pipelines. Map: Learn. For AI-written SQL you might paste into models, still run a human check: How to check AI-written SQL.

What you’ll learn

How to write a staging model from a declared source
How ref() builds a dependency graph you can trust
Which generic tests to add first (unique, not_null, relationships)
How to run and read dbt run and dbt test for a subset
A worked orders path from staging to fct_orders
Common first-model mistakes and a practice loop

The build loop (keep it short)

Every change should fit a boring loop:

Write or edit SQL and YAML
Run the models you touched (and downstream if needed)
Run tests on those models
Inspect a few rows in the warehouse
Open a PR with grain sentences and test results in the description

If your personal workflow skips tests “until later,” later never comes. Generic tests are cheap. Empty confidence is expensive.

Rule of thumb: If a model has a primary key, it has unique and not_null tests before you call it done.

Staging model anatomy

A staging model is usually a single SELECT from a source with renames and casts. Example for orders:

-- models/staging/jaffle_shop/stg_jaffle_shop__orders.sql
with source as (
    select * from {{ source('jaffle_shop', 'orders') }}
),

renamed as (
    select
        id as order_id,
        user_id as customer_id,
        order_date::date as order_date,
        status as order_status,
        _etl_loaded_at as loaded_at
    from source
)

select * from renamed

Why the CTE style? Readability. source isolates the raw pull. renamed is the contract your project will use. Avoid SELECT * leaving the model if you can list columns; explicit columns make schema changes visible in code review.

Casts belong here when the source types are messy. Business calculations that need multiple sources do not belong here.

Filled dbt build loop from source orders through staging into a tested fact

Marts with ref()

ref('model_name') tells dbt to build dependencies in order and to point at the correct schema for the current target. Never hardcode analytics.stg_jaffle_shop__orders inside a mart if ref can do the job.

-- models/marts/core/fct_orders.sql
with orders as (
    select * from {{ ref('stg_jaffle_shop__orders') }}
),

customers as (
    select * from {{ ref('stg_jaffle_shop__customers') }}
),

joined as (
    select
        orders.order_id,
        orders.order_date,
        orders.order_status,
        orders.customer_id,
        customers.customer_name,
        customers.customer_email
    from orders
    left join customers
        on orders.customer_id = customers.customer_id
)

select * from joined

Grain sentence for this mart: one row per order, enriched with current customer attributes. If customer attributes are slowly changing and you need historical truth, that is a later design (snapshots or SCD logic). Do not pretend a simple left join solved history if it did not.

Tests that pay rent immediately

Generic tests in YAML are the on-ramp. Example _core__models.yml:

version: 2

models:
  - name: fct_orders
    description: One row per order with customer attributes at build time.
    columns:
      - name: order_id
        description: Primary key.
        tests:
          - unique
          - not_null
      - name: customer_id
        tests:
          - not_null
          - relationships:
              to: ref('dim_customers')
              field: customer_id
      - name: order_date
        tests:
          - not_null

  - name: dim_customers
    description: One row per customer for analytics.
    columns:
      - name: customer_id
        tests:
          - unique
          - not_null

What each test buys you:

unique on the PK: catches fan-out joins that duplicated orders.
not_null on the PK and required foreign keys: catches bad loads and bad joins.
relationships: catches orphan facts when dimensions lag or keys drift.

Accepted values tests help for status enums. Freshness tests on sources help for silent pipelines. Singular tests (custom SQL) come next when you need “yesterday’s order count should not drop 40%.” Start generic, then go specific when a real failure mode shows up twice.

Commands you will actually use

Exact flags evolve; the ideas stay stable. Typical lab commands:

# build one model and its parents
dbt run --select stg_jaffle_shop__orders+

# run tests for a model
dbt test --select fct_orders

# run model then tests in one breath (common in CI)
dbt build --select fct_orders+

# see the graph and docs locally
dbt docs generate
dbt docs serve

Selection syntax matters. model+ means the model and downstream. +model means the model and upstream. In a PR, select the smallest set that proves your change. In mainline CI, run the project or a broader package of critical marts.

Worked example: from empty staging to a tested fact

Scenario: Part 1 layout is in place. You will stand up customers and orders staging, a thin dim_customers, and fct_orders.

Step 1: sources.yml

version: 2

sources:
  - name: jaffle_shop
    schema: jaffle_shop
    tables:
      - name: customers
      - name: orders

Step 2: staging customers

with source as (
    select * from {{ source('jaffle_shop', 'customers') }}
),

renamed as (
    select
        id as customer_id,
        first_name,
        last_name,
        first_name || ' ' || last_name as customer_name,
        email as customer_email
    from source
)

select * from renamed

Step 3: staging orders

Use the earlier orders staging SQL. Confirm grain: one row per order_id.

Step 4: dim and fct

dim_customers can start as a pass-through of staging with only consumer-facing columns. fct_orders joins as shown above. Resist adding ten metrics “while you are here.” Ship the grain, then add measures deliberately (or leave metrics to the BI layer if that is your team contract).

Step 5: YAML tests

Attach unique and not_null on both primary keys. Add relationship from fct_orders.customer_id to dim_customers.customer_id.

Step 6: build and interpret

dbt build --select stg_jaffle_shop__customers stg_jaffle_shop__orders dim_customers fct_orders

If unique on fct_orders.order_id fails, you almost always joined wrong or staging already had duplicates. Fix upstream, do not select distinct in the mart to silence the alarm without understanding it.

Sanity query after green tests:

select order_status, count(*) as orders
from {{ ref('fct_orders') }}
group by 1
order by 2 desc;

In the warehouse, run the compiled SQL or query the built table. You want boring distributions, not a single status that swallowed nulls incorrectly.

Successful dbt build output with green unique and not_null tests on fct_orders and dim_customers

PR description template

## Models
- stg_jaffle_shop__customers (grain: one row per customer)
- stg_jaffle_shop__orders (grain: one row per order)
- dim_customers
- fct_orders (grain: one row per order with customer attrs)

## Tests
- unique + not_null on customer_id, order_id
- relationships fct_orders.customer_id -> dim_customers.customer_id

## How I verified
- dbt build --select ...
- Spot-checked order counts by status vs source

## Risk
- Left join means orders without customers still appear; customer fields null

Reviewers should not have to reverse engineer grain from SQL alone. Put the sentences where humans look.

When tests fail: a triage order

Source bad? Query the raw source for duplicate PKs or nulls.
Staging bad? Check renames, casts, accidental cross joins (rare in pure staging but possible with bad CTEs).
Join bad? Fan-out from many-to-many relationships is the classic unique failure on facts.
Timing bad? Dimension not loaded yet, relationship test fails for orphans; fix orchestration order or soften with a planned tolerance only if product agrees.
Test wrong? Rare for unique/not_null on true PKs. Question the grain sentence before deleting the test.

Incremental models: wait until it hurts

Full refresh tables are easier to reason about. Move to incremental materializations when runtime or cost demands it, and only after tests and grain are stable. Incremental logic introduces late-arriving data questions and unique key configuration. Earn that complexity with a measured pain, not premature optimization on a 200k row fact.

Common mistakes

Hardcoded database references instead of source/ref. Breaks every environment shift.
SELECT * through every layer. Unexpected columns leak; contracts stay fuzzy.
Tests added after the first production incident only. Start with PK tests.
Using distinct to “fix” unique tests. Hides join bugs.
Business metrics calculated three different ways in three marts. Centralize or document intentionally.
Skipping row spot checks after green tests. Tests catch classes of error, not every wrong filter.
PRs with no grain sentences. Reviewers guess; production inherits the guess.

How to practice this week

Day 1: Implement one staging model from a real source with explicit columns and a grain sentence in a SQL comment.
Day 2: Add unique and not_null tests. Run them. If they fail, fix data or SQL, do not delete tests casually.
Day 3: Build a thin mart with one join and a relationship test.
Day 4: Break the join on purpose in a branch to watch the unique test fail. Restore it. That memory sticks.
Day 5: Open a PR using the template. Ask a teammate to review only the YAML and grain sentences first, then the SQL.

Series wrap (so far)

dbt project lab Parts 1 and 2 give you a shape and a first working path: layers that scale, models that compile, tests that catch the usual join disasters. Later parts in a fuller lab might cover intermediate pivots, incremental facts, and CI. Even if you stop here, you already have the habits that separate a repo of scripts from a project people can inherit.

Quick recap

Staging uses source(); marts use ref(); both keep environments portable.
Primary keys get unique and not_null before you celebrate.
dbt build on a focused select is the daily driver; docs help onboarding.
Failed unique tests are usually join or grain problems, not nuisances.
PR text carries grain and verification so reviews stay honest.

Sources

dbt Docs: Models
dbt Docs: ref function
dbt Docs: source function
dbt Docs: Data tests
dbt Docs: Node selection syntax
dbt Docs: Documentation