3. Models and ref()

Chapter 1 said a model is a SELECT. This chapter is about what dbt does with it, and about ref() — the function that earns most of dbt’s keep.

A model, end to end

One file:

-- models/staging/stg_orders.sql

select
    id            as order_id,
    user_id       as customer_id,
    order_date,
    status,
    amount / 100.0 as amount_usd
from {{ source('jaffle_shop', 'raw_orders') }}
where status != 'deleted'

Configured as a view, dbt sends this:

create or replace view analytics.dbt_sarah.stg_orders as (
    select
        id            as order_id,
        user_id       as customer_id,
        order_date,
        status,
        amount / 100.0 as amount_usd
    from raw.jaffle_shop.raw_orders
    where status != 'deleted'
);

Three transformations happened.

{{ source(...) }} resolved to a real table name.
dbt wrapped the query in the DDL for the configured materialization.
dbt worked out the target database and schema from the profile and project config.

You wrote the middle. dbt wrote the rest. The file name became the relation name — stg_orders.sql produces stg_orders. There is no place to declare the output name; it is the filename, always.

ref(), and why it is the whole game

-- models/marts/customer_orders.sql

select
    c.customer_id,
    c.customer_name,
    count(o.order_id) as order_count
from {{ ref('stg_customers') }} as c
left join {{ ref('stg_orders') }} as o
    on c.customer_id = o.customer_id
group by 1, 2

ref('stg_customers') returns the fully-qualified name of that model in the current target. In development it renders analytics.dbt_sarah.stg_customers; in production, analytics.analytics.stg_customers. Same file, different string.

It does two jobs at once, and both are load-bearing.

Job one — environments. Because the name is computed rather than written down, the same code builds into anybody’s schema. There is no find-and-replace between dev and prod, no {{ 'dev' if ... }} conditionals. This is what makes a personal development sandbox free.

Job two — the dependency graph. dbt reads every ref() in every model before running anything and builds a directed acyclic graph. It knows customer_orders depends on stg_customers and stg_orders, so it builds those first, and it knows the two staging models do not depend on each other, so it builds them in parallel.

   source: raw_customers ──▶ stg_customers ──┐
                                             ├──▶ customer_orders
   source: raw_orders    ──▶ stg_orders    ──┘

   dbt runs:
     wave 1:  stg_customers  ║  stg_orders     (parallel)
     wave 2:  customer_orders

Nobody wrote that order down. It is derived from the text of the models. Add a model, and the graph updates itself. Delete one, and dbt tells you what broke.

Contrast the alternative, which is what teams did before: a list, in a scheduler, of which table to build when, maintained by hand, always slightly wrong.

Never hard-code a model name

The single most important rule in dbt:

-- ✗ WRONG
from analytics.analytics.stg_orders

-- ✓ RIGHT
from {{ ref('stg_orders') }}

The wrong version works. It will run and produce correct numbers, which is exactly why it is dangerous. What it destroys:

The graph edge. dbt does not know this model depends on stg_orders, so it may build them in the wrong order, or in parallel, and you get stale or missing data.
Environments. It reads production from your development run.
Lineage. The docs and the DAG are now wrong.
--select state selection. Chapter 12’s slim CI silently skips this model.

A hard-coded reference is a silent, permanent bug. dbt-project-evaluator (chapter 13) catches them; so does code review, if people know to look.

source(), and where raw data comes in

ref() points at models dbt builds. source() points at tables dbt does not build — the raw data something else loaded.

Declared in YAML:

version: 2

sources:
  - name: jaffle_shop
    database: raw
    schema: jaffle_shop
    description: Raw tables landed by Fivetran from the app database.

    freshness:
      warn_after:  {count: 12, period: hour}
      error_after: {count: 24, period: hour}
    loaded_at_field: _fivetran_synced

    tables:
      - name: raw_orders
        description: One row per order, as the app writes it.
        columns:
          - name: id
            description: Primary key.
            tests: [unique, not_null]

      - name: raw_customers

Used as {{ source('jaffle_shop', 'raw_orders') }} — always two arguments, source name then table name.

Why bother, when you could type the table name? Four reasons:

One place to change. The warehouse moves from raw to raw_v2; you edit one YAML file.
Freshness checks. dbt source freshness queries max(_fivetran_synced) per table and warns or errors. Chapter 6 covers it.
Lineage that starts at the truth. Raw tables appear in the graph, so you can see what a source change would break.
Tests on raw data, catching problems at the boundary rather than three models downstream.

Sources are the edge of the project. dbt does not create them, cannot rebuild them, and will not touch them. If the loader is broken, dbt’s job is to notice.

Configuring a model

Three places, from broadest to most specific:

In dbt_project.yml, by folder:

models:
  jaffle_shop:
    marts:
      +materialized: table

In a YAML file, per model:

models:
  - name: customer_orders
    config:
      materialized: table
      tags: ['finance', 'daily']

In the model file itself:

{{ config(
    materialized = 'incremental',
    unique_key   = 'order_id',
    tags         = ['finance']
) }}

select ...

More specific wins. The in-file config() block beats YAML, which beats the project file. Chapter 11 works through the full precedence rules, including the two places where they are genuinely surprising.

Rule of thumb: defaults by folder in dbt_project.yml, exceptions in the model. If every model carries a config() block, the folder defaults are wrong.

CTEs and how to structure a model

The house style, and worth following because it makes review easy:

with

source as (
    select * from {{ ref('stg_orders') }}
),

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

joined as (
    select
        source.order_id,
        source.amount_usd,
        customers.customer_name
    from source
    left join customers using (customer_id)
),

final as (
    select
        *,
        case when amount_usd > 100 then 'large' else 'small' end
            as order_size
    from joined
)

select * from final

The conventions:

Import CTEs first — one per ref(), at the top, doing nothing but selecting. Every dependency visible in the first fifteen lines.
Logic CTEs in the middle, each named for what it does.
final at the bottom, and the model ends with select * from final.

Warehouses do not care. Snowflake and BigQuery inline CTEs and optimise across them; this costs nothing at runtime. It is entirely for the human reading the pull request.

One real caveat: on some engines, a CTE referenced multiple times may be materialised or re-computed depending on the optimiser. If you have a heavy CTE used twice and the model is slow, that is worth checking — but write for clarity first and optimise the model that is actually slow.

Model versions and contracts

For models other teams depend on, dbt can enforce a shape.

models:
  - name: customers
    config:
      contract:
        enforced: true
    columns:
      - name: customer_id
        data_type: integer
        constraints:
          - type: not_null
      - name: customer_name
        data_type: varchar

With enforced: true, dbt checks the built model’s columns and types against the YAML at build time and fails the run if they drift. A column you quietly renamed stops the build instead of breaking a dashboard.

Contracts are opt-in and cost effort, so the usual advice is: enforce them on the handful of models other people depend on, and nowhere else.

Model versions (customers_v1, customers_v2) let you publish a breaking change while consumers migrate. Useful across team boundaries, overkill inside one.

Python models

dbt can run Python instead of SQL on warehouses that support it — Snowflake via Snowpark, Databricks, BigQuery via Dataproc.

def model(dbt, session):
    dbt.config(materialized="table")
    orders = dbt.ref("stg_orders").to_pandas()
    # ... arbitrary Python ...
    return result_df

dbt.ref() does what {{ ref() }} does, so a Python model sits in the same graph as everything else.

Use them sparingly. They are slower to develop, harder to test, unavailable on several adapters, and they pull data into a compute context that costs differently. The realistic use is the small number of steps that genuinely are not SQL — a scikit-learn scoring step, an API call, an odd statistical function. If it can be SQL, make it SQL.

What to take from this chapter

The filename is the relation name. There is nowhere else to declare it.
ref() does two jobs: it makes environments work by computing names at compile time, and it builds the dependency graph from the text of your models.
Never hard-code a model name. It works, which is what makes it dangerous — it silently breaks ordering, environments, lineage and state selection.
source() marks the edge of the project — data dbt does not build. Declaring sources buys one place to change names, freshness checks, honest lineage, and tests at the boundary.
Config lives in three places and the most specific wins. Defaults by folder; exceptions in the model.
Structure models as import CTEs, logic CTEs, then final. It costs nothing at runtime and makes review fast.
Contracts catch schema drift at build time. Enforce them on models other teams depend on; skip them elsewhere.
Python models exist and should be rare. If it can be SQL, make it SQL.

Next: materializations — view, table, incremental, ephemeral and materialized view, and the DDL each one compiles to.

Written by Sepuri Sai Krishna