Skip to content
Tutorials Center
My learning

Topics

Lesson 7 of 41 ยท Projects and Models

Writing Models and Using ref()

Write dbt models as SELECT statements, connect them with ref(), follow the import-CTE style, and learn why ref() beats hard-coded table names across environments.

A model is the unit of work in dbt: one .sql file, one SELECT, one table or view in the warehouse. Real projects have hundreds of them, each small and focused, connected by ref(). This lesson shows how to write good models and why ref() is the most important function in dbt.

Rules for a model file

  1. The file contains one SELECT (CTEs are fine) and nothing else.
  2. No CREATE, INSERT, DROP or trailing semicolon. dbt adds those.
  3. The file name is the model name and must be unique in the project.
  4. Reference other models with ref() and raw data with source().

That is the full rulebook. Everything else (views or tables, schemas, tags) is configuration.

Chaining models with ref()

Let's build a three-step chain on the sample shop data. Each step is its own model. The ref() calls are shown in comments, and the SQL below runs in SQLite with the models created as views in order, which mimics a dbt run.

stg_orders: one row per order, clean names.

stg_order_items: one row per item, with the line total computed.

order_totals: joins the two and summarizes per order.

SQL A chain of three models ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

The real order_totals.sql file would replace stg_orders with {{ ref('stg_orders') }} and stg_order_items with {{ ref('stg_order_items') }}. dbt sees those two references and guarantees both staging models exist before order_totals is built.

Why not just write the table name?

You could write from analytics.stg_orders directly. It would work in production and break everywhere else. ref() gives you three things a literal name cannot:

  • Environment awareness. In development ref('stg_orders') becomes dbt_maria.stg_orders; in production it becomes analytics.stg_orders. Same code, different schema.
  • Dependency tracking. dbt builds the graph from your ref() calls, so run order, lineage and --select +model all work.
  • Safe renames. Rename a model and every ref() that points at it fails at compile time instead of in production.

Here is the environment point as a runnable stand-in (real Jinja, simplified dbt). The same model text compiles differently per target:

Python Stand-in: one model, two environments

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

The import-CTE style

Long models become hard to read when you nest subqueries. The convention used by most dbt teams is to import every ref in CTEs at the top, do the logic in the middle, and finish with a simple select *.

SQL
with orders as (
    select * from {{ ref('stg_orders') }}
),

items as (
    select * from {{ ref('stg_order_items') }}
),

order_totals as (
    select
        orders.order_id,
        orders.customer_id,
        sum(items.line_total) as items_total
    from orders
    join items using (order_id)
    group by orders.order_id, orders.customer_id
)

select * from order_totals

Benefits: all dependencies are visible in the first lines, each step has a name, and debugging means changing the last line to select * from items to inspect any step. Try it:

SQL Import CTEs: debug any step ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

To inspect one step, change the last line to select * from stg_items. In a real dbt project, each of the first CTEs reads from a {{ ref(...) }} instead of a raw table.

ref() with packages and versions

ref() has two optional forms that you will meet later:

SQL
{{ ref('other_package', 'dim_customers') }}   -- a model from an installed package
{{ ref('dim_customers', v=2) }}               -- a specific version of a versioned model

What goes wrong

  • Misspelled model. ref('stg_ordres') fails at compile with "Model depends on a node named stg_ordres which was not found".
  • A cycle. Model A refs B and B refs A. dbt refuses to run.
  • Refs in the wrong place. ref() is Jinja, so it only works inside {{ }}, never in a plain comment you expect dbt to read. Note that dbt still parses refs inside SQL comments, so a commented-out {{ ref('old_model') }} still creates a dependency. Remove it, or write the comment as -- ref to old_model without the Jinja braces.
  • Selecting from a raw table directly. from raw.orders hides the dependency. Use source() (next chapter).

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. What should a model file contain?

    dbt supplies the CREATE wrapper, so the file is just a SELECT.
  2. 2. Which of these is a real benefit of ref() over a hard-coded table name?

    ref() makes the same code work in dev and prod, and builds the DAG.
  3. 3. In the import-CTE style, where do the ref() calls go?

    Importing every dependency at the top makes dependencies visible and each step easy to inspect.
  4. 4. What happens if a model refs a model name that does not exist?

    An unknown ref fails at compile time, before anything runs in the warehouse.

Summary

  • A model is one SELECT in one file; the file name is the model name.
  • ref('model') links models, builds the DAG and adapts the table name to each environment.
  • The import-CTE style keeps models readable and easy to debug.
  • Use source() for raw tables and ref() for everything dbt builds.

Next: Materializations: View, Table and Ephemeral