For loops for dynamic SQL in dbt

Introduction

For loops help you create SQL code that changes based on your data. This saves time and avoids mistakes when writing similar SQL many times.

You want to create multiple similar SQL queries for different tables or columns.

You need to build a report that shows data for many categories without writing each query by hand.

You want to automate SQL code generation to handle changing data structures.

You need to repeat a SQL pattern for a list of values or dates.

You want to keep your SQL code clean and easy to update.

Syntax

dbt

{% for item in list %}
  -- your SQL code using {{ item }}
{% endfor %}

Use {% for %} and {% endfor %} to start and end the loop.

Inside the loop, use {{ item }} to insert the current value.

Examples

This creates three SELECT statements, one for each column name.

dbt

{% for col in ['sales', 'profit', 'cost'] %}
  SELECT {{ col }} FROM my_table
{% endfor %}

This loops over days 1 to 3 and creates WHERE clauses for each date.

dbt

{% for day in range(1, 4) %}
  WHERE date = '2024-06-0{{ day }}'
{% endfor %}

Sample Program

This code sums up sales, profit, and cost columns in one query. The loop creates each SUM part and adds commas except after the last one.

dbt

{% set columns = ['sales', 'profit', 'cost'] %}

SELECT
{% for col in columns %}
  SUM({{ col }}) AS total_{{ col }}{% if not loop.last %},{% endif %}
{% endfor %}
FROM my_table;

Important Notes

Remember to handle commas or other separators carefully inside loops to avoid syntax errors.

You can loop over lists, ranges, or any iterable data in dbt Jinja templates.

Loops help keep your SQL DRY (Don't Repeat Yourself) and easier to maintain.

Summary

For loops let you write SQL that repeats patterns automatically.

Use {% for %} and {{ }} to insert dynamic parts.

Loops save time and reduce errors when working with many similar SQL pieces.