How dbt works (SQL + Jinja + YAML) - Practice Exercises

Challenge - 5 Problems

❓ Predict Output

intermediate

What is the output of this Jinja expression in a dbt model?

Given the following Jinja code snippet inside a dbt SQL model, what will be the output SQL after rendering?

dbt

{% set threshold = 10 %}
SELECT * FROM sales WHERE amount > {{ threshold }}

A SELECT * FROM sales WHERE amount > 10

B SELECT * FROM sales WHERE amount > 'threshold'

C SELECT * FROM sales WHERE amount > {{ threshold }}

D SELECT * FROM sales WHERE amount > threshold

💡 Hint

Remember that Jinja variables inside {{ }} are replaced with their values during rendering.

The variable 'threshold' is set to 10, so {{ threshold }} is replaced by 10 in the final SQL.

❓ data_output

intermediate

What data does this dbt model produce?

Consider this dbt model SQL code using Jinja: {% raw %} SELECT user_id, COUNT(*) AS order_count FROM {{ ref('orders') }} GROUP BY user_id HAVING COUNT(*) > 5 {% endraw %} What does this model output?

dbt

SELECT user_id, COUNT(*) AS order_count
FROM orders
GROUP BY user_id
HAVING COUNT(*) > 5

A An error because HAVING cannot be used without GROUP BY

B A table with all users and their total orders including those with 5 or fewer

C A table with orders filtered to only those with order_count > 5

D A table with users who have more than 5 orders and their order counts

💡 Hint

HAVING filters groups after aggregation.

The query groups orders by user_id and keeps only those with more than 5 orders.

🔧 Debug

advanced

Why does this dbt YAML config cause an error?

Given this dbt model configuration in YAML: models: my_project: +materialized: table my_model: +tags: ['finance', 'monthly'] What is the cause of the error when running dbt?

dbt

models:
  my_project:
    +materialized: table
    my_model:
      +tags: ['finance', 'monthly']

A YAML indentation is incorrect; 'my_model' should be at the same level as '+materialized'

B The '+materialized' config should be under 'my_model', not directly under 'my_project'

C The '+tags' value must be a string, not a list

D The '+' prefix is invalid in dbt YAML config keys

💡 Hint

Model-specific configs must be nested under the model name.

The '+materialized' config applies to models and should be nested under the model name, not the project.

🚀 Application

advanced

How does dbt handle model dependencies with ref()?

In dbt, what is the effect of using {{ ref('model_b') }} inside model_a.sql?

A It tells dbt that model_a depends on model_b, so model_b runs first and its table/view is referenced

B It runs model_b's SQL code inside model_a's SQL as a subquery

C It creates a copy of model_b's data inside model_a's table

D It causes an error if model_b does not exist in the same folder

💡 Hint

Think about how dbt builds the DAG of models.

The ref() function creates a dependency so dbt runs model_b before model_a and references its output.

🧠 Conceptual

expert

What is the role of Jinja templating in dbt models?

Which statement best describes how Jinja templating works in dbt?

A Jinja is used only for formatting SQL code for readability in dbt models

B Jinja runs inside the database engine to optimize SQL queries at runtime

C Jinja allows dynamic SQL generation by inserting variables, control flow, and macros before SQL runs in the warehouse

D Jinja replaces YAML configurations with SQL code automatically

💡 Hint

Jinja runs before SQL is sent to the database.

dbt uses Jinja to generate SQL dynamically by processing variables and logic before execution.

Practice

1. What is the main role of Jinja in dbt projects?

easy

A. To add logic and dynamic behavior to SQL queries

B. To write raw SQL queries without any modification

C. To manage configuration and documentation files

D. To execute the SQL queries on the database

Solution

1. Step 1: Understand Jinja's purpose in dbt

   Jinja is a templating language that allows adding logic like loops and conditions inside SQL files.

2. Step 2: Differentiate roles of SQL, Jinja, and YAML

   SQL writes queries, YAML manages configs/docs, and Jinja adds dynamic logic to SQL.

3. Final Answer:

   To add logic and dynamic behavior to SQL queries -> Option A

4. Quick Check:

   Jinja = logic in SQL [OK]

Hint: Jinja = logic inside SQL, YAML = configs/docs [OK]

Common Mistakes:

• Confusing Jinja with YAML for configs
• Thinking Jinja executes SQL queries
• Assuming Jinja writes raw SQL without changes

2. Which of the following is the correct way to use a Jinja variable inside a dbt SQL model?

easy

A. SELECT * FROM var('table_name')

B. SELECT * FROM {{ var('table_name') }}

C. SELECT * FROM {% var('table_name') %}

D. SELECT * FROM [[ var('table_name') ]]

Solution

1. Step 1: Recall Jinja syntax for variables

   Jinja variables are inserted using double curly braces {{ }} around expressions.

2. Step 2: Identify correct syntax for var function

   The correct syntax is {{ var('variable_name') }} to access a variable in dbt.

3. Final Answer:

   SELECT * FROM {{ var('table_name') }} -> Option B

4. Quick Check:

   Jinja variables use {{ }} [OK]

Hint: Use {{ var('name') }} to insert variables in SQL [OK]

Common Mistakes:

• Using single curly braces or wrong brackets
• Confusing Jinja tags {% %} with variable insertion {{ }}
• Using square brackets instead of curly braces

3. Given this dbt model SQL code, what will be the output SQL after rendering?

SELECT
  user_id,
  {% if var('include_email', false) %}
    email,
  {% endif %}
  created_at
FROM users

Assuming the variable include_email is set to true in dbt_project.yml.

medium

A. SELECT user_id, true, created_at FROM users

B. SELECT user_id, created_at FROM users

C. Syntax error due to misplaced Jinja

D. SELECT user_id, email, created_at FROM users

Solution

1. Step 1: Check the value of the variable include_email

   The variable include_email is true, so the if condition passes and the email column is included.

2. Step 2: Render the SQL with the if block included

   The SQL will have user_id, email, and created_at columns selected from users.

3. Final Answer:

   SELECT user_id, email, created_at FROM users -> Option D

4. Quick Check:

   include_email true means email included [OK]

Hint: If var true, include block inside {% if %} [OK]

Common Mistakes:

• Ignoring the variable value and excluding email
• Thinking Jinja syntax causes SQL errors
• Confusing variable default values

4. You wrote this YAML config in your dbt project:

models:
  my_project:
    +materialized: table
      users:
        +tags: ['important']

Why does dbt raise an error when running?

medium

A. Because the indentation for 'users' is incorrect under 'my_project'

B. Because '+materialized' cannot be set in YAML

C. Because tags must be a string, not a list

D. Because 'models' key is missing

Solution

1. Step 1: Check YAML indentation rules for dbt configs

   In dbt, model configs under a project must be indented properly; 'users' should be at the same level as '+materialized'.

2. Step 2: Identify the indentation error

   'users' is indented too far, making it a child of '+materialized' which is invalid.

3. Final Answer:

   Because the indentation for 'users' is incorrect under 'my_project' -> Option A

4. Quick Check:

   YAML indentation matters for nested configs [OK]

Hint: Check YAML indentation carefully for nested configs [OK]

Common Mistakes:

• Ignoring YAML indentation importance
• Thinking '+materialized' is invalid syntax
• Assuming tags cannot be lists

5. You want to create a dbt model that selects only active users from a table, but the 'active' flag is stored in a YAML config. Which approach correctly combines SQL, Jinja, and YAML to achieve this?

hard

A. Use Jinja to read YAML directly inside SQL without defining variables

B. Write WHERE active = true directly in SQL without YAML or Jinja

C. Define 'active_flag: true' in YAML, then use WHERE active = {{ var('active_flag') }} in SQL with Jinja

D. Set 'active_flag' in YAML but forget to use Jinja in SQL, so filter is missing

Solution

1. Step 1: Store the filter value in YAML as a variable

   Define 'active_flag: true' in YAML under vars or config to make it accessible.

2. Step 2: Use Jinja to insert the variable in SQL WHERE clause

   Use WHERE active = {{ var('active_flag') }} so the SQL filters active users dynamically.

3. Final Answer:

   Define 'active_flag: true' in YAML, then use WHERE active = {{ var('active_flag') }} in SQL with Jinja -> Option C

4. Quick Check:

   YAML vars + Jinja in SQL = dynamic filters [OK]

Hint: Use YAML vars + Jinja {{ var() }} in SQL WHERE [OK]

Common Mistakes:

• Hardcoding filter in SQL ignoring YAML
• Not using Jinja to insert YAML vars
• Trying to read YAML directly in SQL without var()