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.