ref() function for model dependencies in dbt - Step-by-Step Execution

Concept Flow - ref() function for model dependencies

Start: Model A

↓

Call ref('Model B')

↓

dbt finds Model B

↓

Build dependency graph

↓

Run Model B first

↓

Run Model A after Model B

↓

Complete execution

The ref() function tells dbt which models depend on others, so it builds and runs them in the right order.

Execution Sample

dbt

select * from {{ ref('customers') }}
where active = true

This code uses ref() to get data from the 'customers' model before filtering active ones.

Execution Table

Step	Action	Input	Output	Notes
1	Start building 'orders' model	orders model SQL with ref('customers')	Detect dependency on 'customers'	ref() identifies 'customers' as dependency
2	Check if 'customers' model is built	Dependency graph	Not built yet	Must build 'customers' first
3	Build 'customers' model	customers model SQL	Table/view created	Customers data ready
4	Build 'orders' model	orders model SQL with ref resolved	Table/view created	Orders built using customers data
5	Execution complete	-	-	All models built in correct order

💡 All dependent models built; ref() resolved dependencies correctly

Variable Tracker

Variable	Start	After Step 1	After Step 2	After Step 3	After Step 4	Final
dependency_graph	empty	contains 'customers'	checks 'customers' built	adds 'customers' built	adds 'orders' built	complete
model_status	none built	'customers' needed	'customers' building	'customers' built	'orders' building	all built

Key Moments - 2 Insights

Why does dbt build the 'customers' model before 'orders'?

Because the 'orders' model uses ref('customers'), dbt knows 'orders' depends on 'customers' and builds 'customers' first (see execution_table step 2 and 3).

What happens if you forget to use ref() and just write the table name?

dbt won't know about the dependency, so it might build models in the wrong order, causing errors or stale data (reflected in dependency_graph variable_tracker).

Visual Quiz - 3 Questions

Look at the execution_table, at which step does dbt start building the 'customers' model?

A Step 3

B Step 2

C Step 1

D Step 4

💡 Hint

Check the 'Action' column in execution_table for when 'customers' model is built.

According to variable_tracker, what is the state of 'model_status' after Step 3?

A 'orders' building

B 'customers' built

C none built

D 'customers' needed

💡 Hint

Look at the 'model_status' row and the column 'After Step 3' in variable_tracker.

If you remove ref('customers') from 'orders' model, what likely happens?

A dbt builds both models simultaneously without issues

B dbt ignores 'orders' model

C dbt builds 'orders' before 'customers' causing errors

D dbt automatically detects dependency anyway

💡 Hint

Recall key_moments about missing ref() causing wrong build order.

Concept Snapshot

ref('model_name') tells dbt which models depend on others.
It builds a dependency graph to run models in correct order.
Always use ref() to link models, not raw table names.
This ensures data freshness and avoids build errors.

Full Transcript

The ref() function in dbt is used to declare dependencies between models. When a model uses ref('other_model'), dbt knows it must build 'other_model' first. This creates a dependency graph that dbt uses to run models in the right order. For example, if 'orders' model uses ref('customers'), dbt builds 'customers' before 'orders'. This prevents errors and ensures data is fresh. If ref() is missing, dbt might build models in the wrong order, causing problems. The execution table shows each step: detecting dependencies, building dependent models, and completing execution. The variable tracker shows how the dependency graph and model statuses change step-by-step. Remember to always use ref() to link models in dbt projects.

Practice

1. What is the main purpose of the ref() function in dbt?

easy

A. To create new database users

B. To write raw SQL queries inside dbt models

C. To link models and define dependencies between them

D. To schedule dbt runs automatically

Solution

1. Step 1: Understand the role of ref()

   The ref() function is used to link one model to another in dbt, so dbt knows the order to run models and their dependencies.

2. Step 2: Identify what ref() does not do

   It does not write raw SQL, create users, or schedule runs. Its main role is linking models.

3. Final Answer:

   To link models and define dependencies between them -> Option C

4. Quick Check:

   ref() links models = A [OK]

Hint: Remember: ref() connects models, not SQL or users [OK]

Common Mistakes:

• Thinking ref() writes SQL code
• Confusing ref() with scheduling tools
• Assuming ref() manages database users

2. Which of the following is the correct syntax to reference a model named customers inside another model using ref()?

easy

A. select * from {{ ref('customers') }}

B. select * from ref('customers')

C. select * from ref(customers)

D. select * from {{ ref(customers) }}

Solution

1. Step 1: Recall dbt Jinja syntax for ref()

   In dbt, ref() must be wrapped in double curly braces and the model name must be a string in quotes.

2. Step 2: Check each option

   select * from {{ ref('customers') }} uses {{ ref('customers') }} which is correct. Options B and C miss the curly braces or quotes. select * from {{ ref(customers) }} misses quotes around the model name.

3. Final Answer:

   select * from {{ ref('customers') }} -> Option A

4. Quick Check:

   Use {{ ref('model_name') }} syntax = A [OK]

Hint: Always use {{ ref('model_name') }} with quotes and braces [OK]

Common Mistakes:

• Omitting curly braces {{ }}
• Not putting model name in quotes
• Using ref() without Jinja syntax

3. Given the following dbt model code, what will be the output SQL after compilation if the orders model exists?

select order_id, customer_id
from {{ ref('orders') }}

medium

A. select order_id, customer_id from orders

B. select order_id, customer_id from {{ ref('orders') }}

C. select order_id, customer_id from dbt.orders

D. select order_id, customer_id from ref('orders')

Solution

1. Step 1: Understand what ref() compiles to

   The ref() function compiles to the actual table name of the referenced model, usually just the model name like 'orders'.

2. Step 2: Check the compiled SQL output

   The compiled SQL replaces {{ ref('orders') }} with orders, so the output is select order_id, customer_id from orders.

3. Final Answer:

   select order_id, customer_id from orders -> Option A

4. Quick Check:

   ref('orders') compiles to orders = C [OK]

Hint: ref() compiles to the model's table name without braces [OK]

Common Mistakes:

• Leaving ref() uncompiled in SQL
• Adding extra schema prefix without config
• Using ref() as a string literal

4. You wrote this dbt model code:

select * from ref('sales')

When you run dbt, you get an error. What is the problem?

medium

A. Quotes around 'sales' should be removed

B. Model name 'sales' does not exist

C. ref() cannot be used inside select statements

D. Missing double curly braces around ref()

Solution

1. Step 1: Check the syntax of ref() usage

   In dbt, ref() must be wrapped in double curly braces to be interpreted as Jinja code.

2. Step 2: Identify the error cause

   The code uses ref('sales') without {{ }}, so dbt treats it as plain text, causing an error.

3. Final Answer:

   Missing double curly braces around ref() -> Option D

4. Quick Check:

   Use {{ ref('model') }} not ref('model') alone = D [OK]

Hint: Always wrap ref() in {{ }} to avoid errors [OK]

Common Mistakes:

• Forgetting {{ }} around ref()
• Assuming ref() works without Jinja
• Removing quotes from model name

5. You have two models: customers and orders. You want to create a new model customer_orders that joins these two. Which is the best way to use ref() to ensure correct dependencies and flexible naming?

hard

A. select c.customer_id, o.order_id from customers c join orders o on c.customer_id = o.customer_id

B. select c.customer_id, o.order_id from {{ ref('customers') }} c join {{ ref('orders') }} o on c.customer_id = o.customer_id

C. select c.customer_id, o.order_id from 'customers' c join 'orders' o on c.customer_id = o.customer_id

D. select c.customer_id, o.order_id from ref('customers') c join ref('orders') o on c.customer_id = o.customer_id

Solution

1. Step 1: Use ref() with correct Jinja syntax for both models

   To link models and ensure dbt knows dependencies, use {{ ref('model_name') }} for both customers and orders.

2. Step 2: Avoid hardcoding table names or missing Jinja syntax

   Options A and C hardcode names or use quotes incorrectly. select c.customer_id, o.order_id from ref('customers') c join ref('orders') o on c.customer_id = o.customer_id misses curly braces, so it won't compile.

3. Final Answer:

   select c.customer_id, o.order_id from {{ ref('customers') }} c join {{ ref('orders') }} o on c.customer_id = o.customer_id -> Option B

4. Quick Check:

   Use {{ ref('model') }} for all dependencies = B [OK]

Hint: Use {{ ref('model') }} for all model references [OK]

Common Mistakes:

• Hardcoding table names instead of using ref()
• Forgetting curly braces around ref()
• Using quotes incorrectly around model names