Building a DAG of models in dbt - Practice Problems & Coding Challenges

Challenge - 5 Problems

🧠 Conceptual

intermediate

Understanding DAG dependencies in dbt

In dbt, models are organized in a Directed Acyclic Graph (DAG). What does it mean when a model depends on another model in this DAG?

A The dependent model runs before the model it depends on.

B Both models run at the same time regardless of dependency.

C The dependent model runs after the model it depends on.

D The dependent model ignores the model it depends on.

💡 Hint

Think about the order of execution when one model uses data from another.

In a DAG, a model that depends on another must run after the model it depends on to ensure data is available.

❓ data_output

intermediate

Resulting DAG nodes count

Given these dbt models with dependencies:

• model_a (no dependencies)
• model_b depends on model_a
• model_c depends on model_a
• model_d depends on model_b and model_c

How many nodes will the DAG contain after compilation?

A 5

B 3

C 2

D 4

💡 Hint

Count each unique model as one node.

Each model is a node. Here, model_a, model_b, model_c, and model_d make 4 nodes.

❓ Predict Output

advanced

Output of dbt run with model dependencies

Consider this simplified dbt project with models and their dependencies:

models:
  model_x.sql: no dependencies
  model_y.sql: depends on model_x
  model_z.sql: depends on model_y

If you run dbt run --models model_z, which models will be run and in what order?

A model_x, model_y, then model_z run in order.

B Only model_z runs.

C model_z runs first, then model_y and model_x.

D model_y and model_z run, model_x is skipped.

💡 Hint

dbt runs all dependencies of the specified model.

dbt runs dependencies first to ensure data is ready. So model_x runs, then model_y, then model_z.

🔧 Debug

advanced

Identifying a cyclic dependency error

You have these dbt models:

• model_1 depends on model_2
• model_2 depends on model_3
• model_3 depends on model_1

What error will dbt raise when compiling this DAG?

A dbt will raise a missing model error.

B dbt will raise a cyclic dependency error.

C dbt will skip model_3 and run others.

D dbt will run models in any order without error.

💡 Hint

Think about what happens when dependencies loop back to the start.

A DAG cannot have cycles. dbt detects this and raises a cyclic dependency error.

🚀 Application

expert

Optimizing DAG execution with selective model runs

You have a large dbt project with many models. You want to run only models affected by changes in model_sales and its downstream models. Which dbt command achieves this?

A <code>dbt run --models +model_sales</code>

B <code>dbt run --models model_sales+</code>

C <code>dbt run --models model_sales</code>

D <code>dbt run --models +model_sales+</code>

💡 Hint

Use dbt's selector syntax to include downstream models.

The model_sales+ selector runs model_sales and all models that depend on it downstream.

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