if/else logic in models in dbt

Introduction

We use if/else logic in dbt models to make decisions in our data queries. This helps us change results based on conditions, like sorting or grouping data differently.

You want to assign categories to data based on values, like labeling sales as 'high' or 'low'.

You need to handle missing or special values differently in your data.

You want to create new columns that depend on conditions, like calculating discounts only for certain customers.

You want to filter or transform data differently depending on some criteria.

Syntax

dbt

case
  when condition then result
  else other_result
end

This is SQL CASE syntax used inside dbt models.

Conditions are checked in order, and the first true condition's result is returned.

Examples

This example labels customers as 'High' or 'Low' based on their total sales.

dbt

select
  customer_id,
  case
    when total_sales > 1000 then 'High'
    else 'Low'
  end as sales_category
from sales_table

This example assigns readable order status labels based on the status code.

dbt

select
  order_id,
  case
    when status = 'shipped' then 'Complete'
    when status = 'pending' then 'In Progress'
    else 'Unknown'
  end as order_status
from orders

Sample Program

This dbt model example creates a small sales table and uses if/else logic with CASE to categorize sales as 'High' or 'Low'.

dbt

with sales as (
  select 1 as customer_id, 1200 as total_sales union all
  select 2, 800 union all
  select 3, 1500
)

select
  customer_id,
  total_sales,
  case
    when total_sales > 1000 then 'High'
    else 'Low'
  end as sales_category
from sales
order by customer_id

Important Notes

dbt models use SQL, so if/else logic is done with CASE statements.

Always test your conditions carefully to avoid unexpected results.

Summary

Use CASE statements in dbt models to add if/else logic.

This helps create new columns or transform data based on conditions.

Check conditions in order; the first true one applies.