How to use if/else in a dbt model (Jinja)

A Jinja if decides which SQL dbt writes into the compiled model. The decision happens once, when the model is compiled, so it depends on things known before the query runs: the target, a variable, whether the run is incremental. A common use is to read less data in development.

Copy and paste

Only the last 30 days in development (Snowflake SQL):

select order_id, order_date, status, amount
from {{ ref('stg_orders') }}
where true
{% if target.name == 'dev' %}
  and order_date >= dateadd(day, -30, current_date)
{% endif %}

A filter switched on by a variable:

{% if var('only_shipped', false) %}
  and status = 'shipped'
{% endif %}

dbt run -s orders_recent --vars '{only_shipped: true}'

if, elif and else:

{% if target.name == 'prod' %}
    {% set days = 3650 %}
{% elif target.name == 'ci' %}
    {% set days = 7 %}
{% else %}
    {% set days = 30 %}
{% endif %}

select * from {{ ref('stg_orders') }}
where order_date >= dateadd(day, -{{ days }}, current_date)

Why where true

Each if adds an and ... line. If the first condition is false and the second true, the query ends up with and straight after from, which is invalid. Starting with where true makes every condition an and, whichever of them are present. The warehouse ignores true.

With target.name == 'dev' and only_shipped: true, the model compiles to:

select order_id, order_date, status, amount
from analytics.dbt_nicola.stg_orders
where true
  and order_date >= dateadd(day, -30, current_date)
  and status = 'shipped'

What you can test

Jinja if or SQL case

Jinja decides once per run, for the whole model. SQL case when decides for every row. Use Jinja for environments, switches and configuration; use case for anything that depends on the data. A Jinja if status == 'shipped' does not work: when the model is compiled there are no rows yet, only SQL text.

The Jinja was tested with dbt Core 1.12; the SQL is written for Snowflake.