10 Powerful Jinja Loop Patterns for dbt (incl. 3 Bonus Patterns)

Jinja loops in dbt are a game-changer for automating SQL generation and reducing repetitive code. By leveraging Python-like syntax, you can dynamically build queries, aggregate data, and streamline your dbt models for scalability and maintainability.

Whether you’re mapping values, generating unions, or building dynamic aggregations, these patterns will help you write cleaner, more efficient dbt code.

TL;DR

• Problem: Writing repetitive code is boring, ugly and inefficient.
• Solution: Use Jinja patterns inside dbt transformations.
• In Sum: We discuss 10 Jinja loop patterns to help you write cleaner, more efficient and beautiful dbt transformations.

Just like well-written python is pythonic, well-written dbt code is dbtonic.
- dbt

Below, we’ll explore 10 practical loop patterns, each with multiple code examples, to help you write cleaner, more efficient dbt models. They include:

1. Dynamic CASE Statements
2. Generating UNION Statements
3. Automating Similar Import CTEs
4. Flexible Aggregation
5. Function Parameter Generation
6. Dynamic Column Selection
7. Generating Filters or WHERE Clauses
8. Dynamic Pivoting (Column to Row Transformation) (For Pro Readers Only)
9. Generating Dynamic Update Statements (For Pro Readers Only)
10. Building Dynamic Window Functions (For Pro Readers Only)
11. BONUS: DBT Jinja Functions and Loop Properties Cheat Sheet (For Pro Readers Only)

1. Dynamic CASE Statements

Map values to categories or labels without writing out every condition.

{% set type_mapping = {
  "shirt": "clothing",
  "truck": "vehicle",
  "hat": "clothing",
  "jeans": "clothing",
  "car": "vehicle"
} %}

select
  id,
  product_name,
  case
    {% for key, value in type_mapping.items() %}
      when product_name = '{{ key }}' then '{{ value }}'
    {% endfor %}
    else 'other'
  end as product_type
from {{ ref('seed_products') }}

Suppose you want to map both product type and size:

{% set type_mapping = {
  "shirt": {"category": "clothing", "size": "M"},
  "truck": {"category": "vehicle", "size": "L"},
  "hat": {"category": "clothing", "size": "S"}
} %}

select
  id,
  product_name,
  case
    {% for key, value in type_mapping.items() %}
      when product_name = '{{ key }}' then '{{ value["category"] }}'
    {% endfor %}
    else 'other'
  end as product_type,
  case
    {% for key, value in type_mapping.items() %}
      when product_name = '{{ key }}' then '{{ value["size"] }}'
    {% endfor %}
    else 'unknown'
  end as product_size
from {{ ref('seed_products') }}

2. Generating UNION Statements

Combine data from multiple tables or sources without writing each SELECT manually.

{% set products = ["pants", "shirts", "shoes"] %}

{% for product in products %}
  select
    id,
    product_name,
    product_price
  from {{ ref('seed_' + product) }}
  {% if not loop.last %}union all{% endif %}
{% endfor %}

3. Automating Similar Import CTEs

Create multiple CTEs for different sources, then join them together.

{% set sources = [
  {"name": "customers", "filter": "active = true"},
  {"name": "orders", "filter": "order_date >= '2024-01-01'"},
  {"name": "inventories", "filter": "stock_quantity > 0"}
] %}

with
{% for source in sources %}
  {{ source.name }}_cte as (
    select * from {{ ref('seed_' + source.name) }}
    where {{ source.filter }}
  ){% if not loop.last %},{% endif %}
{% endfor %}

select * from customers_cte

4. Flexible Aggregation

Aggregate across multiple columns or dimensions without repeating code.

{% set regions = ["north", "south", "east", "west"] %}

select
  {% for region in regions %}
    sum({{ region }}_sales) as total_{{ region }}_sales
    {% if not loop.last %}, {% endif %}
  {% endfor %}
from {{ ref('seed_sales') }}

An extended version to demonstrate multiple aggregations:

{% set regions = ["north", "south", "east", "west"] %}
{% set metrics = ["sales", "profit"] %}

select
  {% for region in regions %}
    {% for metric in metrics %}
      sum({{ region }}_{{ metric }}) as total_{{ region }}_{{ metric }}
      {% if not (loop.last and loop.parent.last) %}, {% endif %}
    {% endfor %}
  {% endfor %}
from {{ ref('seed_sales') }}

5. Function Parameter Generation

Dynamically generate function parameters, such as percentiles or thresholds.

{% set percentiles = range(25, 100, 25) %}

select
  {% for p in percentiles %}
    percentile_cont({{ p }} / 100) within group (order by price) as price_p{{ p }},
    percentile_cont({{ p }} / 100) within group (order by rating) as rating_p{{ p }}
    {% if not loop.last %}, {% endif %}
  {% endfor %}
from {{ ref('seed_product_sales') }}

6. Dynamic Column Selection

Select a dynamic set of columns, e.g., for wide tables or when columns change frequently.

{% set columns = ["id", "name", "created_at", "updated_at"] %}

select
  {% for col in columns %}
    {{ col }}{% if not loop.last %}, {% endif %}
  {% endfor %}
from {{ ref('my_table') }}

7. Generating Filters or WHERE Clauses

Build dynamic filters based on a list of values.

{% set countries = ["US", "CA", "GB"] %}

select *
from {{ ref('users') }}
where country in (
  {% for country in countries %}
    '{{ country }}'{% if not loop.last %}, {% endif %}
  {% endfor %}
)

Jinja loops are a secret weapon for anyone looking to streamline their dbt projects. By automating repetitive SQL tasks, you not only save time but also reduce the risk of errors and make your models easier to maintain. The patterns covered in this post, from dynamic CASE statements to window functions and pivoting, demonstrate just how flexible and powerful Jinja can be within dbt.

As you continue to develop your data models, keep these techniques in mind to build robust, scalable, and DRY (Don’t Repeat Yourself) analytics pipelines. Happy modeling!

FAQs

Further Reading

• dbt's Guide on using Jinja
• dbt Jinja functions - In addition to the standard Jinja library, dbt has added additional functions and variables to the Jinja context that are useful when working with a dbt project.

EOF

(Where I tend to share unrelated things).

If you've come across or used other Jinja loop patterns, I would love to hear about them in the comments 😃

In the below section (exclusively for our Pro readers), we discuss 3 more difficult yet useful patterns such as dynamic pivoting, generating update statements, and building dynamic window functions, and the bonus cheat sheet of Jinja functions and loop properties and examples.

BONUS: Cheat Sheet for DBT Jinja Functions

This cheat sheet is a gold mine of dbt-jinja functions, custom-built for dbt developers, making our lives easy. Download it below 👇

...