Building a DAG of models in dbt - Interactive Code Practice

Practice - 5 Tasks

1fill in blank

easy

Complete the code to define a dbt model that selects all columns from the source table.

dbt

select * from [1]

Drag options to blanks, or click blank then click option'

Adbt_source

Bref('source_table')

Cmy_table

Dsource('my_source', 'my_table')

Attempts:

3 left

💡 Hint

Common Mistakes

Using ref() instead of source() for source tables.

Using plain table names without dbt functions.

The source() function is used to refer to a source table in dbt, specifying the source name and table name.

2fill in blank

medium

Complete the code to create a model that depends on another model called 'customers'.

dbt

select * from [1]

Drag options to blanks, or click blank then click option'

A'customers'

Bref('customers')

Csource('raw', 'customers')

Ddbt.ref('customers')

Attempts:

3 left

💡 Hint

Common Mistakes

Using source() instead of ref() for models.

Using string literals without ref().

The ref() function creates a dependency on another dbt model, here 'customers'.

3fill in blank

hard

Fix the error in the model code to correctly reference the 'orders' model.

dbt

select * from [1]

Drag options to blanks, or click blank then click option'

Aref('orders')

B'orders'

Cref('order')

Dsource('orders')

Attempts:

3 left

💡 Hint

Common Mistakes

Misspelling the model name as 'order'.

Using source() instead of ref() for models.

The model name is 'orders', so ref('orders') correctly references it.

4fill in blank

hard

Fill both blanks to create a model that selects customer names and filters orders with amount greater than 100.

dbt

select customer_name, order_amount from [1] where order_amount [2] 100

Drag options to blanks, or click blank then click option'

Aref('orders')

B>

C<

Dsource('sales', 'orders')

Attempts:

3 left

💡 Hint

Common Mistakes

Using source() instead of ref() for models.

Using the wrong comparison operator.

The model depends on 'orders' so ref('orders') is used. The filter uses the greater than operator > to select orders above 100.

5fill in blank

hard

Fill all three blanks to create a dictionary comprehension that maps model names to their SQL code if the model name starts with 'stg_'.

dbt

model_sql = { [1]: [2] for [3] in models if [3].startswith('stg_') }

Drag options to blanks, or click blank then click option'

Amodel.name

Bmodel.sql

Cmodel

Dmodel_name

Attempts:

3 left

💡 Hint

Common Mistakes

Using a string variable instead of the model object in the loop.

Mixing variable names inconsistently.

The comprehension maps model.name to model.sql for each model in models where the name starts with 'stg_'.

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