Implementing Control Flow Logic in Models

In this lesson, you will learn how to use Jinja control flow—specifically if statements and for loops—to write dynamic, environment-aware SQL inside your dbt models.

Why Control Flow Matters

Standard SQL is static, meaning the query you write is the query that executes every time. However, in modern data pipelines, you often need to change your SQL behavior based on where you are running the code (like development versus production) or based on a specific configuration set in your dbt_project.yml file. Control flow logic allows you to "program" your SQL to adapt to these conditions.

Using If-Statements

The if statement is the most common tool for environment-specific logic. For example, you might want to limit the amount of data processed while you are developing, but process the full dataset in production.

SELECT *
FROM {{ ref('stg_orders') }}

{% if target.name == 'dev' %}
  WHERE order_date >= dateadd('day', -7, current_date)
{% endif %}

In this example, if your environment is set to dev, dbt injects the WHERE clause. If you are in prod, the WHERE clause is omitted entirely, allowing the full history to load.

Using For-Loops

for loops allow you to iterate over a list or configuration to generate repetitive SQL code dynamically. Imagine you have a list of columns you want to aggregate dynamically without typing them out manually:

SELECT
  customer_id,
  {% for column in ['total_amount', 'tax_amount', 'shipping_cost'] %}
    sum({{ column }}) as total_{{ column }}{% if not loop.last %},{% endif %}
  {% endfor %}
FROM {{ ref('orders') }}
GROUP BY 1

In this snippet, the loop generates three separate sum() calculations. Notice the if not loop.last check—this is a handy Jinja feature that ensures you don’t end up with a trailing comma, which would cause a SQL syntax error.

By mastering these blocks, you can reduce repetitive code and create highly flexible models that adapt to your specific pipeline requirements without needing to maintain separate versions of the same query.

Key Takeaway: Control flow logic transforms static SQL into dynamic code that intelligently adjusts its behavior based on environment variables or automated configuration loops.