Building a DAG of models in dbt - Mini Project: Build & Apply

Building a DAG of models

📖 Scenario: You work in a company that uses dbt to organize data transformations. You want to build a simple Directed Acyclic Graph (DAG) of models where one model depends on another. This helps keep your data organized and updated in the right order.

🎯 Goal: Create two dbt models where the second model depends on the first. This will build a DAG of models showing how data flows from one step to the next.

📋 What You'll Learn

Create a base model with a simple SQL SELECT statement

Create a second model that selects from the first model using the ref() function

Use the ref() function to define dependencies between models

Run the models to see the DAG in action

💡 Why This Matters

🌍 Real World

In real companies, dbt helps organize complex data transformations by building DAGs of models. This keeps data pipelines clear and maintainable.

💼 Career

Data engineers and analysts use dbt to build reliable data workflows. Knowing how to build DAGs is essential for managing dependencies and ensuring data quality.

1

Create the base model

Create a dbt model file called base_customers.sql with a SQL query that selects id and name from a table called raw_customers. Write the exact SQL: select id, name from raw_customers.

dbt

-- Create the base model SQL query
-- Your code here

Hint

This model is the starting point. It just selects data from the raw source table.

2

Create a dependent model

Create a second dbt model file called active_customers.sql. Use the ref() function to select all columns from the base_customers model. Write the exact SQL: select * from {{ ref('base_customers') }}.

dbt

-- Create the dependent model SQL query
-- Your code here

Hint

This model depends on the base model. The ref() function tells dbt about this dependency.

3

Add a filter to the dependent model

Modify the active_customers.sql model to select only customers with id greater than 100. Use the exact SQL: select * from {{ ref('base_customers') }} where id > 100.

dbt

-- Modify the dependent model to filter customers with id > 100
-- Your code here

Hint

Adding a filter shows how you can transform data in dependent models.

4

Print the DAG structure

Run the dbt command to show the DAG graph. Write the exact command: dbt ls --select active_customers --output graph.

dbt

# Run the command to show the DAG graph
# Your code here

Hint

This command lists the models and shows their dependencies as a graph.

Practice

1.

What does a DAG represent in dbt?

easy

A. The configuration settings for dbt profiles

B. The syntax rules for writing SQL queries

C. The order in which models depend on each other

D. The list of all tables in the database

Solution

1. Step 1: Understand what DAG means in dbt context

   A DAG (Directed Acyclic Graph) shows how models are connected by dependencies.

2. Step 2: Identify the role of DAG in dbt

   dbt uses the DAG to know which models to run first based on dependencies.

3. Final Answer:

   The order in which models depend on each other -> Option C

4. Quick Check:

   DAG = model dependency order [OK]

Hint: DAG shows model dependencies and run order [OK]

Common Mistakes:

• Confusing DAG with SQL syntax
• Thinking DAG lists all tables
• Mixing DAG with dbt config files

2.

Which of the following is the correct way to reference another model in a dbt SQL file?

SELECT * FROM ___

easy

A. ref(model_name)

B. ref('model_name')

C. 'ref(model_name)'

D. ref:"model_name"

Solution

1. Step 1: Recall the syntax for referencing models in dbt

   dbt uses the function ref() with the model name as a string inside parentheses.

2. Step 2: Check each option for correct syntax

   ref('model_name') uses ref('model_name') which is correct; others have syntax errors or wrong quotes.

3. Final Answer:

   ref('model_name') -> Option B

4. Quick Check:

   Use ref('model_name') with quotes [OK]

Hint: Use ref('model_name') with quotes and parentheses [OK]

Common Mistakes:

• Omitting quotes around model name
• Using wrong quote types
• Using colons or other symbols

3.

Given these two models, what is the order dbt will run them?

-- model_a.sql
SELECT * FROM source_table

-- model_b.sql
SELECT * FROM {{ ref('model_a') }}

medium

A. model_a runs first, then model_b

B. model_b runs first, then model_a

C. Both run simultaneously

D. dbt will error due to circular dependency

Solution

1. Step 1: Identify dependencies from ref()

   model_b references model_a using ref(), so model_b depends on model_a.

2. Step 2: Determine run order based on dependencies

   dbt runs model_a first, then model_b to ensure data is ready.

3. Final Answer:

   model_a runs first, then model_b -> Option A

4. Quick Check:

   Dependency order = model_a before model_b [OK]

Hint: Models run in dependency order: referenced first [OK]

Common Mistakes:

• Assuming ref() means reverse dependency
• Thinking models run simultaneously
• Confusing circular dependency errors

4.

What is wrong with this dbt model code snippet?

SELECT * FROM {{ ref(model_a) }}

medium

A. Model name should be uppercase

B. ref() cannot be used inside SELECT

C. Missing FROM keyword

D. Missing quotes around model name in ref()

Solution

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

   ref() requires the model name as a string with quotes inside the parentheses.

2. Step 2: Identify the error in the code snippet

   model_a is not quoted, causing a syntax error in dbt compilation.

3. Final Answer:

   Missing quotes around model name in ref() -> Option D

4. Quick Check:

   ref('model_name') needs quotes [OK]

Hint: Always put model names in quotes inside ref() [OK]

Common Mistakes:

• Forgetting quotes around model names
• Thinking ref() can't be in SELECT
• Assuming case sensitivity causes error

5.

You have three models: model_x, model_y, and model_z. model_y references model_x, and model_z references both model_x and model_y. Which of the following is the correct order dbt will run these models?

hard

A. model_x, model_y, model_z

B. model_y, model_x, model_z

C. model_z, model_y, model_x

D. model_x, model_z, model_y

Solution

1. Step 1: Analyze dependencies among models

   model_y depends on model_x; model_z depends on both model_x and model_y.

2. Step 2: Determine run order respecting dependencies

   model_x runs first (no dependencies), then model_y (depends on model_x), then model_z (depends on both).

3. Final Answer:

   model_x, model_y, model_z -> Option A

4. Quick Check:

   Run order respects dependencies [OK]

Hint: Run models so dependencies are built before dependents [OK]

Common Mistakes:

• Running dependent models before their dependencies
• Ignoring multiple dependencies
• Assuming any order works if models reference each other