Using For Loops in your dbt models with Jinja

📘Introduction

In this hands-on dbt tutorial, you'll learn how to use Jinja for loops inside your dbt models to make your SQL code more dynamic and automated. Instead of manually repeating similar SQL logic for multiple columns, tables, or conditions — you can use for loops with Jinja to generate repetitive SQL blocks programmatically. This helps you write cleaner, scalable, and maintainable transformations.

✅ Prerequisites

☑️ A dbt project set up
☑️ Source data loaded into your data warehouse
☑️ Source configurations defined in sources.yml

💡 What are For Loops in dbt?

In dbt, for loops come from the Jinja templating language and allow you to iterate over lists or dictionaries to generate SQL dynamically.

This is extremely useful when you need to create repetitive SQL logic — like selecting multiple columns, applying similar transformations, or building UNION queries across multiple tables — without copying and pasting code.

🧠 Practical Example

Let’s walk through an example. We have three tables — each containing student data from different programs:

student_data_analytics

student_data_engineering

student_data_science

Our goal is to create a single dbt model that combines all these datasets into one unified table called student.

🗂️ Create new .sql file inside the models/ folder

Inside your dbt project, navigate to the models/ folder and create a new SQL file named student.

✍️ SQL code without a For Loop

First, let’s look at how the SQL code would look like without using a loop — manually writing out each SELECT statement for the three tables.

SELECT
    id,
    first_name,
    last_name,
    email,
    major,
    number
FROM {{ ref("student_data_analytics") }}

UNION ALL

SELECT
    id,
    first_name,
    last_name,
    email,
    major,
    number
FROM {{ ref("student_data_engineering") }}

UNION ALL

SELECT
    id,
    first_name,
    last_name,
    email,
    major,
    number
FROM {{ ref("student_data_science") }}

⚙️ SQL code with a For Loop

Now let’s simplify this using a Jinja for loop, which dynamically generates the same SQL logic for all tables.