The `ref()` function and the DAG it builds

Description

ref() is, in its simplest form, a Jinja function that takes one argument: the name of another dbt model in the same project.

select * from {{ ref('stg_stores') }}

At first glance it looks like source() — both are Jinja functions, both get used inside a FROM, dbt validates both before touching the warehouse. But they point at fundamentally different things: source('kiosko_raw', 'stores') points at raw data no one in this project produced — a file on disk, generated by an external system. ref('stg_stores') points at a model this same dbt project built, in an earlier run or in the same run, a moment before. That distinction isn't a minor technical detail: it's what lets dbt assemble the project's complete DAG — which model depends on which — and compute, every time, the one execution order that respects all of those dependencies at once.

This lesson demonstrates that mechanism with three real models, run on purpose to fail in three different ways — the same treatment module 2's lesson 3 gave source() — and closes by showing something source() could never do: letting dbt build, automatically and in the correct order, a whole chain of models that you never had to order by hand.

Connection to the module. Lesson 1 promised that ref() is the function that assembles Kiosko's DAG. This lesson delivers on that promise with executed code, before lesson 4 uses it to build dim_store for real.

An analogy: asking for an already-prepared ingredient, not a raw product

Module 2 compared source() to logging, in a receiving record, which external supplier each raw ingredient comes from. ref() is the other half of that same kitchen: when a cook needs tomato sauce for a dish, they don't go back to the pantry to ask for raw tomatoes — someone else already did that, at another station, a while ago — they ask, inside the kitchen itself, for "the tomato sauce already prepared at station 3." That sauce exists because someone else cooked it first, following their own recipe, and the cook asking for it doesn't need to know or remember exactly when it was prepared — they only need to know that, when they ask for it, it's going to be ready.

ref('stg_stores') is exactly that request: "give me the already-prepared result of the stg_stores model," with fact_orders (or any model that uses that ref()) not caring whether stg_stores got built a second ago or an hour ago. And just as, in a professional kitchen, the head chef knows, by looking at each station's order, what order to fire each burner in so nothing arrives late, dbt knows, by looking at every ref() in the project, what order to build each model in — with no human having to shout "sauce first, then the dish!" on every shift.

Worked example: three models, three ways to fail

You're going to write three deliberately broken versions of a trivial model that depends on stg_stores — the same staging model you already built in module 2. None of these three files are part of the final project; delete them as soon as you finish the exercise.

Model 1 — table name written by hand, no ref()

-- models/marts/_temp_hardcoded.sql (temporary, for this exercise)
select store_id, store_name, city
from stg_stores

None of this is syntactically wrong — stg_stores is, in fact, the real name of the object module 2 already built inside kiosko.duckdb. But try it against a completely empty database, where stg_stores doesn't exist yet:

rm -f kiosko.duckdb
dbt run --select _temp_hardcoded

What to expect.

1 of 1 START sql table model main._temp_hardcoded .............................. [RUN]
1 of 1 ERROR creating sql table model main._temp_hardcoded ..................... [ERROR in 0.04s]

Finished running 1 table model in 0 hours 0 minutes and 0.11 seconds (0.11s).

Completed with 1 error, 0 partial successes, and 0 warnings:

[ERROR]: in model _temp_hardcoded (models/marts/_temp_hardcoded.sql)
  Runtime Error in model _temp_hardcoded (models/marts/_temp_hardcoded.sql)
  Catalog Error: Table with name stg_stores does not exist!
  Did you mean "pg_settings"?

  LINE 11: from stg_stores
                ^

  compiled code at target/compiled/kiosko_analytics/models/marts/_temp_hardcoded.sql

Done. PASS=0 WARN=0 ERROR=1 SKIP=0 NO-OP=0 REUSED=0 TOTAL=1

Notice 1 of 1 START ... [RUN]: dbt already started running this model before discovering stg_stores doesn't exist. And notice something even more important — dbt never tried to build stg_stores first. Why would it? Nothing in this file told it stg_stores was a dependency; to dbt, stg_stores is just a word inside a FROM, indistinguishable from any other table name. You already saw this exact error, with the exact same message, in module 2's lesson 3 with source() — DuckDB can't find a table, and it doesn't care whether that table should have existed.

Model 2 — ref() to a name that doesn't exist in the project

-- models/marts/_temp_typo.sql (temporary, for this exercise)
select store_id, store_name, city
from {{ ref('stg_stors') }}

A typo in the model's name: stg_stors instead of stg_stores. Run the same command:

dbt run --select _temp_typo

What to expect.

Running with dbt=1.12.2
Registered adapter: duckdb=1.11.0
[ERROR]: Encountered an error:
Compilation Error
  Model 'model.kiosko_analytics._temp_typo' (models/marts/_temp_typo.sql) depends on a node named 'stg_stors' which was not found

Compare this to Model 1, line by line. There's no 1 of 1 START, no Concurrency: 4 threads — dbt never even tried to touch the warehouse. The error says, verbatim, Compilation Error, and tells you exactly which model (_temp_typo) depends on which name (stg_stors) it couldn't find anywhere in the project. This is ref()'s central guarantee, identical in spirit to the one you already saw with source(): dbt knows, ahead of time, the project's complete graph of declared dependencies, and validates the whole thing before running a single query — no matter whether the project has 3 models or 300.

Model 3 — correct ref(), but without building the dependency first

-- models/marts/_temp_correct.sql (temporary, for this exercise)
select store_id, store_name, city
from {{ ref('stg_stores') }}

This time there's no typo at all. Against an empty database, and selecting only this model (without asking dbt to bring its dependencies):

rm -f kiosko.duckdb
dbt run --select _temp_correct

What to expect.

Running with dbt=1.12.2
Registered adapter: duckdb=1.11.0
Found 8 models, 8 data tests, 4 sources, 500 macros

Concurrency: 4 threads (target='dev')

1 of 1 START sql table model main._temp_correct ................................ [RUN]
1 of 1 ERROR creating sql table model main._temp_correct ....................... [ERROR in 0.04s]

Finished running 1 table model in 0 hours 0 minutes and 0.10 seconds (0.10s).

Completed with 1 error, 0 partial successes, and 0 warnings:

[ERROR]: in model _temp_correct (models/marts/_temp_correct.sql)
  Runtime Error in model _temp_correct (models/marts/_temp_correct.sql)
  Catalog Error: Table with name stg_stores does not exist!
  Did you mean "pg_catalog.pg_settings"?

  LINE 11: from "kiosko"."main"."stg_stores"
               ^

  compiled code at target/compiled/kiosko_analytics/models/marts/_temp_correct.sql

Done. PASS=0 WARN=0 ERROR=1 SKIP=0 NO-OP=0 REUSED=0 TOTAL=1

This third result deserves a careful read, because at first glance it seems to contradict everything you just read: ref('stg_stores') is correct, stg_stores exists in the project — and yet, dbt run fails with the same Catalog Error as Model 1. Look at the compiled line: from "kiosko"."main"."stg_stores" — dbt did resolve the ref() correctly, into a real reference inside DuckDB's catalog. The problem is that, by writing --select _temp_correct with no additional operator, you asked dbt to run only that model, without its dependencies — and since the database was empty, stg_stores never got built beforehand.

Now repeat the same command, adding a single character:

rm -f kiosko.duckdb
dbt run --select +_temp_correct

What to expect.

Running with dbt=1.12.2
Registered adapter: duckdb=1.11.0
Found 8 models, 8 data tests, 4 sources, 500 macros

Concurrency: 4 threads (target='dev')

1 of 2 START sql view model main.stg_stores .................................... [RUN]
1 of 2 OK created sql view model main.stg_stores ............................... [OK in 0.04s]
2 of 2 START sql table model main._temp_correct ................................ [RUN]
2 of 2 OK created sql table model main._temp_correct ........................... [OK in 0.03s]

Finished running 1 table model, 1 view model in 0 hours 0 minutes and 0.18 seconds (0.18s).

Completed successfully

Done. PASS=2 WARN=0 ERROR=0 SKIP=0 NO-OP=0 REUSED=0 TOTAL=2

The + before the model's name (+_temp_correct) tells dbt: "run this model, and everything it needs to exist." dbt walked the ref('stg_stores') declared inside the file, understood stg_stores was a real dependency, added it to the run, and built it first — with you never specifying that order anywhere. This is the complete difference between writing a table name by hand and declaring a dependency with ref(): it isn't just that ref() validates the name exists (it does that too, as you saw with Model 2) — it's that it gives dbt enough information to automatically build the whole chain, in the correct order, with a single character of syntax. This module's lesson 7 goes deeper into + and its variants (model+, +model+); for now, keep the central idea: without ref(), that + would have no graph to work from.

Delete the three temporary files before continuing:

rm models/marts/_temp_hardcoded.sql
rm models/marts/_temp_typo.sql
rm models/marts/_temp_correct.sql

And rebuild the complete project, to leave kiosko.duckdb in the clean state the rest of the module expects:

dbt run --select staging

Going deeper: what ref() compiles to, compared with source()

It's worth looking at the compiled SQL of both, side by side, because the difference explains why they exist as two separate functions instead of one:

dbt compile --inline "select * from {{ source('kiosko_raw', 'stores') }}"
dbt compile --inline "select * from {{ ref('stg_stores') }}"

What to expect.

Compiled inline node is:
select * from 'raw_data/kiosko/stores.csv'

Compiled inline node is:
select * from "kiosko"."main"."stg_stores"

source() compiles to a file path — the same external_location mechanism you met in module 2, run by DuckDB directly against disk, with no dbt model ever having gotten involved. ref() compiles to a catalog reference — "kiosko"."main"."stg_stores", database, schema, object name — exactly the way you'd write a query by hand against any real table or view inside DuckDB, because that's literally what stg_stores is at this point: an object another dbt model already built. That's the exact boundary between the two functions: source() is the entry door to data no one in the project produced; ref() is how you ask for something the project itself already cooked.

Common mistakes

Using ref() to point at a source, or source() to point at another model. What happens: someone writes {{ ref('orders') }} thinking of the raw table, instead of {{ source('kiosko_raw', 'orders') }}, or the other way around, tries {{ source('kiosko_raw', 'stg_orders') }} to refer to a staging model. Why it happens: both functions look similar and live in the same spot inside a FROM, so it's easy to mix up which one corresponds to what. How to spot it: ref('orders') fails with Compilation Error ... depends on a node named 'orders' which was not found — because no model with that name exists, even though a source with that table name does; they're two completely separate namespaces in dbt. How to fix it: the rule has no exceptions — source() always points at raw data external to the project (declared in some _sources.yml); ref() always points at another dbt model (a .sql file inside models/). They never combine or substitute for each other.

Running dbt run --select model_name and being surprised it fails over missing dependencies. What happens: someone, as you saw in this lesson's Model 3, runs a --select with no + operator against a database where the dependencies don't exist yet, and the "table not found" error feels like a failure of ref() itself. Why it happens: in a project where you already ran dbt run with no --select at least once, every dependency is already built, so --select individual_model works with no problem — the error only shows up the first time, on a fresh database, and it's easy not to recognize the cause. How to spot it: if the error is a Catalog Error (not a Compilation Error) on a model you're sure has its ref() written correctly, suspect what you selected first, not the .sql file itself. How to fix it: use --select +model_name (with the + in front) when you want dbt to automatically bring in everything that model needs — or, simpler, run dbt run with no --select at all when you want the complete project, in the correct order, end to end.

Thinking ref() replaces the need for the referenced model to already be correctly written. What happens: someone writes {{ ref('stg_stores') }} correctly, but stg_stores.sql itself has an error (for example, a column that doesn't exist in the source). Why it happens: it's easy to think that, if the ref() itself compiles without complaint, the rest of the chain is guaranteed. How to spot it: dbt reports the error on the model where it actually occurs — stg_stores, not on the model that references it — and any model that depends on stg_stores is going to show up as SKIP in dbt run's report, not as ERROR, because dbt never got to try building it. How to fix it: when you see SKIP in a run, don't look for the problem in that model — look upstream in its ref() chain, at the model that actually failed with ERROR.

Exercises

Exercise 1 — Predict the type of error before running it. Without running anything yet, for each of these three lines, predict whether the error (if there is one) would be a Compilation Error (before touching the warehouse) or a runtime error (while running), assuming kiosko.duckdb already has every module-2 model built:

-- (a)
select * from {{ ref('stg_orders') }}

-- (b)
select * from {{ ref('stg_orderz') }}

-- (c)
select * from stg_orders

Exercise 2 — Reproduce Model 3 with a different dependency. Write a temporary model that depends, via ref(), on stg_products instead of stg_stores. Against an empty database, confirm that dbt run --select model_name (no +) fails, and that dbt run --select +model_name (with +) works.

-- models/marts/_temp_products.sql (temporary, for this exercise)
select product_id, product_name, category
from {{ ref('stg_products') }}

rm -f kiosko.duckdb
dbt run --select _temp_products      # fails: Catalog Error, stg_products doesn't exist yet
rm -f kiosko.duckdb
dbt run --select +_temp_products     # works: dbt builds stg_products first, then _temp_products

Exercise 3 — Explain, in your own words, why ref() allows the + operator and source() doesn't. Using this lesson's analogy (the already-prepared ingredient, versus module 2's receiving record), explain in 2-3 sentences why it makes sense for --select +model to work with ref(), but no equivalent exists for "automatically bringing in" a source().

Summary and next step

In this lesson you saw, run three times, exactly what ref() guarantees and what a project gains by using it instead of writing table names by hand: it validates — at compile time, before touching the warehouse — that the referenced model exists in the project, it compiles to a real catalog reference (not a file path, like source()), and — the piece no other function in this guide offers — it lets dbt automatically build a whole chain of dependencies with the + operator, with you never declaring the order anywhere but inside each individual ref().

Before moving on you should be able to: explain the difference between what source() compiles to and what ref() compiles to; and predict when dbt run --select model is going to fail over a missing dependency, and how to fix it with the + operator.

Lesson 3 stops on this module's other half: materializations. You already know how to declare that one model depends on another — now it's time to decide how each model's result gets stored inside kiosko.duckdb, and why that decision isn't the same for every one.

Resources

• dbt Developer Hub — "ref()," the complete official reference for this function, including the syntax of the selection operators (+model, model+) used in this lesson's worked example. docs.getdbt.com/reference/dbt-jinja-functions/ref. In English.
• dbt Developer Hub — "Node selection syntax," the complete reference for --select and its operators, including the + you saw working in this lesson — this module's lesson 7 comes back to this same page in more depth. docs.getdbt.com/reference/node-selection/syntax. In English.
• dbt Developer Hub — "dbt compile," the reference for the command used to inspect, without running anything, what each of this lesson's two functions compiles to. docs.getdbt.com/reference/commands/compile. In English.