Why For loops for dynamic SQL in dbt? - Purpose & Use Cases

The Big Idea

What if you could write one query that magically creates many for you?

The Scenario

Imagine you have to write SQL queries for each month of the year manually. You copy and paste the same query 12 times, changing only the month value each time.

The Problem

This manual method is slow and boring. It's easy to make mistakes like typos or forgetting to update a month. If you want to add more months or change the query, you must edit every copy again.

The Solution

Using for loops for dynamic SQL lets you write one loop that automatically creates all the monthly queries. This saves time, reduces errors, and makes your code easy to update.

Before vs After

✗ Before

SELECT * FROM sales WHERE month = 'January';
SELECT * FROM sales WHERE month = 'February';
-- repeated for each month

✓ After

{% set months = ['January', 'February', 'March', 'April', 'May', 'June', 'July', 'August', 'September', 'October', 'November', 'December'] %}
{% for month in months %}
SELECT * FROM sales WHERE month = '{{ month }}';
{% endfor %}

What It Enables

You can generate many SQL queries dynamically with just a few lines of code, making your work faster and more reliable.

Real Life Example

A data analyst uses a for loop in dbt to create monthly sales reports automatically, instead of writing separate queries for each month.

Key Takeaways

Manual SQL repetition is slow and error-prone.

For loops automate query generation dynamically.

This approach saves time and reduces mistakes.