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.