For loops for dynamic SQL in dbt - Step-by-Step Execution

Concept Flow - For loops for dynamic SQL

Start loop over list

↓

Get current item

↓

Insert item into SQL template

↓

Add to SQL string

↓

More items?

Yes→Next item

No↓

Execute full SQL

The loop goes through each item, inserts it into the SQL template, builds the full SQL string, then runs it.

Execution Sample

dbt

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

This code dynamically builds a SQL SELECT statement listing columns from a list.

Execution Table

Step	Current item (col)	Action	SQL string built so far
1	'id'	Add 'id' to SQL	SELECT id
2	'name'	Add ', name' to SQL	SELECT id, name
3	'age'	Add ', age' to SQL	SELECT id, name, age
4	No more items	Close SQL with FROM users	SELECT id, name, age FROM users

💡 All columns processed, loop ends, full SQL ready

Variable Tracker

Variable	Start	After 1	After 2	After 3	Final
col	None	'id'	'name'	'age'	None
SQL string	"SELECT\n"	"SELECT\n id"	"SELECT\n id, name"	"SELECT\n id, name, age"	"SELECT\n id, name, age\nFROM users"

Key Moments - 2 Insights

Why do we check if the current item is the last in the loop?

To avoid adding a comma after the last column, so the SQL syntax stays correct (see execution_table rows 2 and 3).

What happens if the list of columns is empty?

The loop body won't run, so the SQL will have no columns listed, which can cause an error when executed.

Visual Quiz - 3 Questions

Look at the execution table, what is the SQL string after step 2?

A SELECT id, name, age

B SELECT id

C SELECT id, name

D SELECT id, name, age FROM users

💡 Hint

Check the 'SQL string built so far' column at step 2 in the execution_table.

At which step does the loop add the last column without a trailing comma?

A Step 3

B Step 1

C Step 2

D Step 4

💡 Hint

Look at the 'Action' column in execution_table rows 2 and 3 to see where the comma is omitted.

If we add a new column 'email' to the list, how many loop iterations will the execution table show?

A 3

B 4

C 5

D 6

💡 Hint

The loop runs once per column; adding 'email' increases iterations by one (see variable_tracker for count).

Concept Snapshot

For loops in dbt let you build SQL dynamically by repeating code for each item in a list.
Use {% for item in list %} ... {% endfor %} to loop.
Inside, insert variables with {{ item }}.
Check loop.last to avoid trailing commas.
Build SQL piece by piece, then run it.

Full Transcript

This visual execution shows how a for loop in dbt builds a dynamic SQL SELECT statement. We start with a list of columns. The loop picks each column one by one, adds it to the SQL string, and adds commas except after the last column. After all columns are added, the SQL string is completed with the FROM clause. Variables like 'col' and the SQL string change step by step. Key points include avoiding a comma after the last column and what happens if the list is empty. The quiz checks understanding of the SQL string at different steps and loop behavior when adding columns.