For loops for dynamic SQL in dbt - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ Predict Output

intermediate

Output of a dbt for loop generating SQL columns

What is the output SQL snippet generated by this dbt Jinja for loop?

{% raw %}{% for col in ['id', 'name', 'age'] %}
  {{ col }} AS {{ col }}_alias{% if not loop.last %}, {% endif %}
{% endfor %}{% endraw %}

dbt

{% for col in ['id', 'name', 'age'] %}
  {{ col }} AS {{ col }}_alias{% if not loop.last %}, {% endif %}
{% endfor %}

A id AS id_alias, name AS name_alias, age AS age_alias

B id AS id_alias, name AS name_alias age AS age_alias

C id AS id_alias; name AS name_alias; age AS age_alias;

D id AS id_alias name AS name_alias age AS age_alias

💡 Hint

Look at how commas are added only between items, not after the last one.

The for loop iterates over the list and adds a comma only if the current item is not the last. This creates a comma-separated list of column aliases.

❓ data_output

intermediate

Number of columns generated by a dynamic for loop in dbt

Given this dbt for loop generating SQL columns from a list of 5 column names, how many columns will be generated?

{% raw %}{% for col in ['a', 'b', 'c', 'd', 'e'] %}
  {{ col }} AS {{ col }}_alias{% if not loop.last %}, {% endif %}
{% endfor %}{% endraw %}

dbt

{% for col in ['a', 'b', 'c', 'd', 'e'] %}
  {{ col }} AS {{ col }}_alias{% if not loop.last %}, {% endif %}
{% endfor %}

A 0

B 4

C 6

D 5

💡 Hint

Count the items in the list.

The loop runs once for each item in the list, so it generates 5 columns.

🔧 Debug

advanced

Identify the error in this dbt for loop for dynamic SQL

What error will this dbt Jinja code produce when rendering?

{% raw %}{% for col in columns %}
  {{ col }} AS {{ col }}_alias,
{% endfor %}{% endraw %}

Assume columns = ['x', 'y', 'z'].

dbt

{% for col in columns %}
  {{ col }} AS {{ col }}_alias,
{% endfor %}

A Extra comma after last column causing SQL syntax error

B Undefined variable error for 'columns'

C SyntaxError in Jinja due to missing endif

D No error, renders correctly

💡 Hint

Check how commas are placed after each column.

The loop adds a comma after every column including the last one, which causes a trailing comma in SQL and leads to a syntax error.

🚀 Application

advanced

Generate dynamic WHERE clause with dbt for loop

You want to generate a dynamic WHERE clause in dbt SQL using a list of filters: filters = ['age > 30', "country = 'US'", 'active = true'].
Which dbt Jinja code snippet correctly generates:

WHERE age > 30 AND country = 'US' AND active = true

A {% raw %}WHERE {% for f in filters %}{{ f }} OR {% endfor %}{% endraw %}

B {% raw %}WHERE {% for f in filters %}{{ f }}{% if not loop.last %} AND {% endif %}{% endfor %}{% endraw %}

C {% raw %}WHERE {% for f in filters %}AND {{ f }}{% if not loop.last %} {% endif %}{% endfor %}{% endraw %}

D {% raw %}WHERE {{ filters | join(' AND ') }}{% endraw %}

💡 Hint

Look for correct use of AND and no trailing AND.

Option B correctly loops over filters, adding AND only between items, producing the exact desired WHERE clause.

🧠 Conceptual

expert

Why use for loops for dynamic SQL in dbt?

Which of the following is the best reason to use for loops in dbt to generate dynamic SQL code?

A To make SQL queries run faster by using loops

B To replace all SQL queries with Python code inside dbt models

C To automate repetitive SQL code generation based on variable lists, reducing manual errors and improving maintainability

D To avoid writing any SQL and rely only on Jinja templating

💡 Hint

Think about the purpose of templating and loops in code generation.

For loops in dbt help automate repetitive SQL code generation, making code easier to maintain and less error-prone.

Practice

1. What is the main purpose of using {% for %} loops in dbt SQL models?

easy

A. To create permanent tables in the database

B. To stop SQL queries from running

C. To write Python code inside SQL models

D. To repeat SQL code patterns dynamically and reduce manual repetition

Solution

1. Step 1: Understand the role of {% for %} in dbt

   The {% for %} loop is used to repeat code blocks dynamically in dbt SQL models.

2. Step 2: Identify the benefit of repeating code

   Repeating code patterns saves time and reduces errors by avoiding manual copy-pasting.

3. Final Answer:

   To repeat SQL code patterns dynamically and reduce manual repetition -> Option D

4. Quick Check:

   For loops = dynamic repetition [OK]

Hint: For loops repeat code blocks automatically in dbt [OK]

Common Mistakes:

• Thinking for loops create tables
• Confusing dbt loops with Python loops
• Believing loops stop queries

2. Which of the following is the correct syntax to start a for loop in a dbt SQL model?

easy

A. {% loop item in list %}

B. {{ for item in list }}

C. {% for item in list %}

D. <% for item in list %>

Solution

1. Step 1: Recall dbt Jinja syntax for loops

   dbt uses Jinja templating where loops start with {% for variable in sequence %}.

2. Step 2: Compare options to correct syntax

   Only {% for item in list %} matches the correct Jinja for loop syntax.

3. Final Answer:

   {% for item in list %} -> Option C

4. Quick Check:

   dbt for loop syntax = {% for %} [OK]

Hint: dbt loops start with {% for variable in sequence %} [OK]

Common Mistakes:

• Using {{ }} instead of {% %} for loops
• Using wrong keywords like 'loop'
• Using angle brackets instead of curly braces

3. Given the following dbt code snippet, what will be the output SQL?

{% for col in ['id', 'name', 'age'] %}SELECT {{ col }} FROM users{% if not loop.last %} UNION ALL {% endif %}{% endfor %}

medium

A. SELECT id FROM users UNION ALL SELECT name FROM users UNION ALL SELECT age FROM users

B. SELECT id, name, age FROM users

C. SELECT id FROM users SELECT name FROM users SELECT age FROM users

D. SyntaxError due to missing commas

Solution

1. Step 1: Understand the loop iteration

   The loop runs over ['id', 'name', 'age'], producing three SELECT statements.

2. Step 2: Check the UNION ALL insertion

   Between each SELECT except the last, 'UNION ALL' is added to combine results.

3. Final Answer:

   SELECT id FROM users UNION ALL SELECT name FROM users UNION ALL SELECT age FROM users -> Option A

4. Quick Check:

   Loop + UNION ALL = combined selects [OK]

Hint: Look for loop.last to add UNION ALL except last [OK]

Common Mistakes:

• Ignoring the UNION ALL between selects
• Assuming all columns selected in one query
• Thinking commas separate selects

4. Identify the error in this dbt for loop snippet:

{% for col in columns %}SELECT {{ col }} FROM users{% endfor %}

Assuming columns = ['id', 'name'].

medium

A. Wrong loop syntax with {{ }} instead of {% %}

B. Missing UNION ALL or separator between SELECT statements

C. Incorrect variable name inside the loop

D. Loop variable should be capitalized

Solution

1. Step 1: Analyze the loop output

   The loop outputs two SELECT statements back to back without any separator.

2. Step 2: Identify missing SQL syntax

   Without UNION ALL or a separator, the SQL is invalid and will cause errors.

3. Final Answer:

   Missing UNION ALL or separator between SELECT statements -> Option B

4. Quick Check:

   Multiple SELECTs need UNION ALL [OK]

Hint: Add UNION ALL between selects in loops [OK]

Common Mistakes:

• Forgetting to add UNION ALL
• Using wrong loop syntax
• Misnaming variables inside loop

5. You want to create a dynamic SQL query in dbt that selects multiple columns from a table, but only if the column name is not empty or None. Which for loop code snippet correctly achieves this?

hard

A. {% for col in columns if col %}SELECT {{ col }} FROM users{% if not loop.last %} UNION ALL {% endif %}{% endfor %}

B. {% for col in columns %}SELECT {{ col }} FROM users{% if col != '' or col != None %} UNION ALL {% endif %}{% endfor %}

C. {% for col in columns %}{% if col %}SELECT {{ col }} FROM users UNION ALL {% endif %}{% endfor %}

D. {% for col in columns %}SELECT {{ col }} FROM users UNION ALL {% endfor %}

Solution

1. Step 1: Filter columns inside the for loop

   {% for col in columns if col %}SELECT {{ col }} FROM users{% if not loop.last %} UNION ALL {% endif %}{% endfor %} uses for col in columns if col to skip empty or None values correctly.

2. Step 2: Add UNION ALL only between valid selects

   {% for col in columns if col %}SELECT {{ col }} FROM users{% if not loop.last %} UNION ALL {% endif %}{% endfor %} adds UNION ALL only if not the last valid column, avoiding trailing UNION ALL.

3. Final Answer:

   {% for col in columns if col %}SELECT {{ col }} FROM users{% if not loop.last %} UNION ALL {% endif %}{% endfor %} -> Option A

4. Quick Check:

   Filter in loop + conditional UNION ALL = correct [OK]

Hint: Use 'if col' in loop to skip empty values [OK]

Common Mistakes:

• Using incorrect if condition syntax
• Adding UNION ALL after last select
• Not filtering out empty or None columns