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

Need a 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

Need a 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

Need a 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

Need a hint?

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