Variables and control flow in dbt

Introduction

Variables store values you want to reuse. Control flow helps decide what code runs based on conditions.

You want to reuse a value like a date or a table name in multiple places.

You want to run different SQL code depending on a condition, like environment or user input.

You want to loop over a list of items to create multiple similar models or tests.

You want to make your dbt project flexible and easier to maintain.

Syntax

dbt

{% set variable_name = value %}

{% if condition %}
  -- code to run if true
{% elif other_condition %}
  -- code to run if other condition true
{% else %}
  -- code to run if none above
{% endif %}

{% for item in list %}
  -- code to repeat for each item
{% endfor %}

Variables are set with {% set %} and can hold strings, numbers, or lists.

Control flow uses {% if %}, {% elif %}, {% else %}, and {% for %} blocks.

Examples

Set a variable start_date and use it inside SQL with double curly braces.

dbt

{% set start_date = '2024-01-01' %}

select * from orders where order_date >= '{{ start_date }}'

Run different SQL depending on the environment (prod or dev).

dbt

{% if target.name == 'prod' %}
  select * from prod_table
{% else %}
  select * from dev_table
{% endif %}

Loop over a list of table names and run a count query for each.

dbt

{% set tables = ['customers', 'orders', 'products'] %}

{% for table in tables %}
  select count(*) from {{ table }};
{% endfor %}

Sample Program

This example sets a variable env to the current environment. Then it chooses the table name based on the environment. Finally, it selects data from the chosen table.

dbt

{% set env = target.name %}

{% if env == 'prod' %}
  {% set table_name = 'sales_prod' %}
{% else %}
  {% set table_name = 'sales_dev' %}
{% endif %}

select * from {{ table_name }} where sale_date >= '2024-01-01';

Important Notes

Remember to use double curly braces {{ }} to insert variables inside SQL code.

Control flow blocks must be properly closed with {% endif %} or {% endfor %}.

Variables and control flow help make your dbt models dynamic and reusable.

Summary

Variables store reusable values in dbt using {% set %}.

Control flow lets you run different code with {% if %} and loops with {% for %}.

Use variables and control flow to make your dbt projects flexible and easier to maintain.