ref(): Models Built on Models

In the last lesson, one model turned raw_orders into stg_orders. Now a second model, daily_revenue, is built on the first. To read another model, you don't type its table name. You write {{ ref('stg_orders') }}. ref (short for reference) names a model by its file name. The {{ }} around it is Jinja, a small template language that dbt fills in before the SQL runs.

What ref does: dbt reads every ref in the project and works out which model needs which. It builds stg_orders first, then daily_revenue. In place of the ref it writes the model's real name, "shop"."main"."stg_orders": database, schema, view. A data team uses ref so dozens of models always build in the right order and read the right tables, with no list to keep by hand.

For example, a model that counts customers per city would read a staging model of customers like this:

SELECT
    city,
    COUNT(*) AS customers
FROM {{ ref('stg_customers') }}
GROUP BY city

You edit models/daily_revenue.sql. It starts with SELECT and the day column. The model it reads is given:

models/stg_orders.sql

SELECT
    order_id,
    customer_id,
    product,
    TRY_CAST(quantity AS INTEGER) AS quantity,
    CAST(unit_price AS INTEGER) AS unit_price,
    status,
    CAST(ordered_at AS TIMESTAMP) AS ordered_at
FROM raw_orders

Then dbt build must print this (dbt's compiled SQL is cut to its FROM line):

model stg_orders (view): OK
model daily_revenue (view): OK
daily_revenue, compiled:
  …
  FROM "shop"."main"."stg_orders"
  …
daily_revenue (view), 7 rows:
day | revenue
2026-05-01 | 274
2026-05-02 | 384
2026-05-03 | 249
2026-05-04 | 126
2026-05-05 | 436
2026-05-06 | 220
2026-05-07 | 595

At work you type dbt build in a terminal, and ref writes the names of a warehouse such as Snowflake or BigQuery. Here the grader runs the build for you on the DuckDB file shop.duckdb.

Your task: in models/daily_revenue.sql, read stg_orders through ref, add the column revenue (the sum of quantity times unit price), keep only the paid orders and group the rows by day.

Assignment
Follow the Coding Tutorial to build daily_revenue on top of stg_orders with ref.

Hint
Inside {{ }}, write ref with the model's file name in quotes, without .sql. Don't read raw_orders here: its columns are all text.