Using Jinja loops for flexible Aggregations in dbt

📘Introduction

In this hands-on dbt tutorial, you’ll learn how to make your aggregations dynamic and flexible using Jinja loops inside your SQL models. Instead of writing multiple aggregation functions, you’ll see how to dynamically generate aggregation logic — saving time and reducing repetitive SQL code.

✅ Prerequisites

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

🧠 Practical Example

Let’s walk through an example. In our dbt project, the table dim_student contains the following data:

We want to create a new dbt model that calculates the number of students per major, but instead of manually writing multiple COUNT() functions, we’ll make it dynamic using a Jinja loop.

🗂️1️⃣ 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_analytics.

⚙️2️⃣ Use loop with Aggregation within your SQL code

Now, let’s use a Jinja loop to dynamically count students for each major: