Variables and control flow in dbt - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ Predict Output

intermediate

Output of variable assignment and if condition in dbt

What is the output of the following dbt Jinja code snippet when run inside a model?

dbt

{% set x = 5 %}
{% if x > 3 %}
  {{ x * 2 }}
{% else %}
  {{ x + 2 }}
{% endif %}

A 10

B 7

C 5

D Error: Undefined variable

💡 Hint

Check the value of x and the condition in the if statement.

The variable x is set to 5. Since 5 is greater than 3, the if block runs, outputting 5 * 2 = 10.

❓ data_output

intermediate

Result of loop with variable increment in dbt

What list will be output by this dbt Jinja code?

dbt

{% set result = [] %}
{% set counter = 0 %}
{% for i in range(3) %}
  {% set counter = counter + i %}
  {% do result.append(counter) %}
{% endfor %}
{{ result }}

A [0, 1, 3]

B [0, 0, 0]

C [1, 3, 6]

D Error: 'do' statement not allowed

💡 Hint

Remember that counter starts at 0 and increments by i each loop.

Loop runs with i=0,1,2. counter updates: 0+0=0, 0+1=1, 1+2=3. Result list collects these values.

🔧 Debug

advanced

Identify the error in variable scope within dbt Jinja

Why does the following dbt code raise an error or produce unexpected output?

dbt

{% for i in range(2) %}
  {% set val = i * 2 %}
{% endfor %}
{{ val }}

A val holds the last value from the loop, outputting 2

B val is undefined outside the loop, causing an error

C val is a list of all values from the loop, outputting [0, 2]

D SyntaxError due to missing endfor

💡 Hint

Consider variable scope inside and outside loops in Jinja.

In dbt Jinja, variables set inside loops are local to the loop block and not accessible outside, so val is undefined after the loop.

🧠 Conceptual

advanced

Understanding control flow with nested if-else in dbt

Given the variable 'score' set to 75, what will this dbt Jinja code output?

dbt

{% set score = 75 %}
{% if score >= 90 %}
  Excellent
{% elif score >= 70 %}
  Good
{% else %}
  Needs Improvement
{% endif %}

A Excellent

B Error: Invalid syntax

C Needs Improvement

D Good

💡 Hint

Check the conditions in order and which one matches 75.

75 is not >= 90 but is >= 70, so the elif block runs, outputting 'Good'.

🚀 Application

expert

Predict the output of complex variable manipulation and control flow in dbt

What is the final output of this dbt Jinja code snippet?

dbt

{% set data = [1, 2, 3, 4] %}
{% set total = 0 %}
{% for num in data %}
  {% if num % 2 == 0 %}
    {% set total = total + num * 2 %}
  {% else %}
    {% set total = total + num %}
  {% endif %}
{% endfor %}
{{ total }}

A 15

B 20

C 18

D Error: Cannot modify variable inside loop

💡 Hint

Calculate total step by step, doubling even numbers before adding.

Loop over [1,2,3,4]: 1 (odd) add 1 → total=1 2 (even) add 4 → total=5 3 (odd) add 3 → total=8 4 (even) add 8 → total=16 Final total is 16.

Practice

1. What does the {% set %} statement do in dbt?

easy

A. It deletes a table from the database.

B. It runs a SQL query directly.

C. It defines a new model in dbt.

D. It creates a variable to store a value for reuse.

Solution

1. Step 1: Understand the purpose of {% set %}

   The {% set %} statement in dbt is used to create a variable that holds a value.

2. Step 2: Identify its use in code reuse

   This variable can then be reused multiple times in your dbt project, making code cleaner and easier to maintain.

3. Final Answer:

   It creates a variable to store a value for reuse. -> Option D

4. Quick Check:

   {% set %} creates variables [OK]

Hint: Remember: {% set %} means 'store this value' [OK]

Common Mistakes:

• Thinking {% set %} runs SQL directly
• Confusing it with model creation
• Assuming it deletes data

2. Which of the following is the correct syntax to create a variable named my_var with value 10 in dbt?

easy

A. {% set my_var = 10 %}

B. {% var my_var = 10 %}

C. {{ set my_var = 10 }}

D. {% set 'my_var' = 10 %}

Solution

1. Step 1: Recall dbt variable syntax

   In dbt, variables are created using the Jinja syntax: {% set variable_name = value %}.

2. Step 2: Check each option

   {% set my_var = 10 %} matches the correct syntax exactly. Options B and C use wrong keywords or delimiters. {% set 'my_var' = 10 %} incorrectly quotes the variable name.

3. Final Answer:

   {% set my_var = 10 %} -> Option A

4. Quick Check:

   Correct variable syntax uses {% set %} [OK]

Hint: Use {% set var = value %} without quotes around var [OK]

Common Mistakes:

• Using {{ }} instead of {% %} for setting variables
• Quoting variable names incorrectly
• Using wrong keywords like var

3. Given the following dbt code snippet, what will be the output of {{ greeting }}?

{% set hour = 15 %}
{% if hour < 12 %}
  {% set greeting = 'Good morning' %}
{% elif hour < 18 %}
  {% set greeting = 'Good afternoon' %}
{% else %}
  {% set greeting = 'Good evening' %}
{% endif %}
{{ greeting }}

medium

A. Good morning

B. Good evening

C. Good afternoon

D. No output (error)

Solution

1. Step 1: Evaluate the value of hour

   The variable hour is set to 15.

2. Step 2: Check the if-elif-else conditions

   Since 15 is not less than 12, the first condition fails. It is less than 18, so the second condition is true, setting greeting to 'Good afternoon'.

3. Final Answer:

   Good afternoon -> Option C

4. Quick Check:

   15 < 18 -> 'Good afternoon' [OK]

Hint: Check conditions in order; first true branch runs [OK]

Common Mistakes:

• Assuming hour 15 is morning
• Ignoring elif and jumping to else
• Thinking variable is undefined

4. Identify the error in this dbt snippet:

{% set total = 0 %}
{% for i in range(3) %}
  {% set total = total + i %}
{% endfor %}
{{ total }}

medium

A. The range function is not available in dbt Jinja.

B. Variables cannot be updated inside a loop.

C. The loop syntax is incorrect; missing parentheses.

D. The variable total should be declared with var.

Solution

1. Step 1: Understand Jinja in dbt limitations

   dbt uses Jinja templating, which does not support Python's range() function directly.

2. Step 2: Identify the error cause

   Using range(3) will cause an error because range is undefined in this context.

3. Final Answer:

   The range function is not available in dbt Jinja. -> Option A

4. Quick Check:

   No range() in dbt Jinja [OK]

Hint: Remember: Jinja loops use lists, not Python range() [OK]

Common Mistakes:

• Assuming Python functions work in Jinja
• Trying to update variables inside loops without workarounds
• Using wrong loop syntax

5. You want to create a dbt model that uses a variable env to switch between two schemas: dev_schema if env is 'dev', else prod_schema. Which code snippet correctly implements this control flow?

hard

A. {% set schema = 'dev_schema' if env == 'dev' else 'prod_schema' %}
SELECT * FROM {{ schema }}.my_table

B. {% if env == 'dev' %}
{% set schema = 'dev_schema' %}
{% else %}
{% set schema = 'prod_schema' %}
{% endif %}
SELECT * FROM {{ schema }}.my_table

C. {% set schema = env == 'dev' and 'dev_schema' or 'prod_schema' %}
SELECT * FROM {{ schema }}.my_table

D. {% set schema = 'prod_schema' %}
{% if env == 'dev' %}
{% set schema = 'dev_schema' %}
SELECT * FROM {{ schema }}.my_table

Solution

1. Step 1: Understand conditional variable assignment in dbt

   dbt supports control flow with {% if %} and {% else %} blocks to set variables based on conditions.

2. Step 2: Check each option for correctness

   {% if env == 'dev' %} {% set schema = 'dev_schema' %} {% else %} {% set schema = 'prod_schema' %} {% endif %} SELECT * FROM {{ schema }}.my_table uses proper if-else blocks to set schema correctly before using it in the query. {% set schema = 'dev_schema' if env == 'dev' else 'prod_schema' %} SELECT * FROM {{ schema }}.my_table uses Python-style inline if which is not supported in Jinja. {% set schema = env == 'dev' and 'dev_schema' or 'prod_schema' %} SELECT * FROM {{ schema }}.my_table uses a Python boolean expression which is not valid in Jinja. {% set schema = 'prod_schema' %} {% if env == 'dev' %} {% set schema = 'dev_schema' %} SELECT * FROM {{ schema }}.my_table sets schema first then tries to reset inside if but the query is inside the if block, causing scope issues.

3. Final Answer:

   Option B correctly uses if-else to set schema and then queries it. -> Option B

4. Quick Check:

   Use {% if %}...{% else %}...{% endif %} for control flow [OK]

Hint: Use full if-else blocks to set variables, not inline Python [OK]

Common Mistakes:

• Trying Python inline if syntax in Jinja
• Placing SQL inside if blocks incorrectly
• Not setting variable before using it