ref() function for model dependencies in dbt - Mini Project: Build & Apply

Using the ref() Function for Model Dependencies in dbt

📖 Scenario: You are working on a data project using dbt (data build tool). You have two models: one with raw sales data and another that summarizes total sales per product. You want to connect these models so the summary model uses the raw data model as its source.

🎯 Goal: Build two dbt models where the second model uses the ref() function to depend on the first model. This will help you understand how to manage dependencies between models in dbt.

📋 What You'll Learn

Create a dbt model called raw_sales.sql with a simple SELECT statement.

Create a variable called summary_model_name with the value 'summary_sales'.

Create a second dbt model called summary_sales.sql that uses ref('raw_sales') in its FROM clause.

Print the SQL query of the summary_sales.sql model to see the final output.

💡 Why This Matters

🌍 Real World

In real data projects, dbt helps organize SQL models and manage dependencies so that data transformations happen in the right order.

💼 Career

Understanding ref() is essential for data analysts and engineers using dbt to build reliable data pipelines.

1

Create the raw_sales model

Create a dbt model file named raw_sales.sql with the exact SQL query: SELECT product_id, sales_amount FROM raw_data.sales.

dbt

-- Create the raw_sales model with the exact SELECT statement
-- Your code here

Hint

This model selects product IDs and sales amounts from the raw_data.sales table.

2

Define the summary model name variable

Create a variable called summary_model_name and set it to the string 'summary_sales'.

dbt

SELECT product_id, sales_amount FROM raw_data.sales

# Create the variable summary_model_name with value 'summary_sales'
# Your code here

Hint

This variable will hold the name of the summary model.

3

Create the summary_sales model using ref()

Create a dbt model file named summary_sales.sql with a SQL query that selects product_id and the sum of sales_amount as total_sales from ref('raw_sales'), grouped by product_id.

dbt

SELECT product_id, sales_amount FROM raw_data.sales

summary_model_name = 'summary_sales'

-- Create the summary_sales model using ref('raw_sales') in the FROM clause
-- Your code here

Hint

Use the ref() function inside double curly braces to refer to the raw_sales model.

4

Print the summary_sales model SQL

Print the SQL query of the summary_sales.sql model exactly as written, including the ref() function call.

dbt

SELECT product_id, sales_amount FROM raw_data.sales

summary_model_name = 'summary_sales'

-- summary_sales.sql model
SELECT product_id, SUM(sales_amount) AS total_sales
FROM {{ ref('raw_sales') }}
GROUP BY product_id

# Print the summary_sales model SQL
# Your code here

Hint

Use a print statement to show the SQL query exactly as it appears in the model.

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