This article is part of the “Jinja in dbt” series. If you’re new to Jinja templating, start with What is Jinja Templating Engine and How to Use It in dbt.

Variables are the building blocks for creating dynamic dbt models with Jinja. They allow you to parameterise your models, making them flexible and reusable across different environments, time periods, and configurations. Understanding how to properly set and use variables is crucial for building maintainable dbt projects.

In this post, I will walk you through everything you need to know about using variables in dbt, including:

Setting variables in dbt

There are several ways to define variables in dbt, each serving different use cases and scopes.

Project-level variables

Define variables in your dbt_project.yml file for project-wide access. This is a good place for setting project-wide defaults, such as start and end dates, conditional logic to limit the volume of data to be processed in development, etc.

-- dbt_project.yml
vars:
  start_date: '1900-01-01'
  end_date: '2200-12-31'
  include_testing_data: false
  default_currency: 'USD'

Command-line variables

If you are testing the variables before adding them to your project or to a model, you can set variables as a part of your dbt command, directly in the terminal. For example:

dbt run –vars ‘{“start_date”: “2024-01-01”, “end_date”: “2024-01-31”}’

Model-specific variables

You can set variables that apply only to specific models by configuring them in the model’s YAML file. For example:

-- models/schema.yml
models:
  - name: monthly_sales
    config:
      vars:
        aggregation_level: 'month'
        include_refunds: true

Using variables in models

Once defined, you can access variables in your models using the var() function either within Jinja expressions or directly in your SQL query.

dbt model:

-- models/sales_report.sql

select
    customer_id,
    order_date,
    amount,
    '{{ var("default_currency") }}' as currency
from {{ ref('raw_sales') }}
where order_date between '{{ var("start_date") }}' and '{{ var("end_date") }}'
{% if not var("include_testing_data") %}
    and customer_id not like 'TEST_%'
{% endif %}

When compiled (assuming we use the same variables from the project-level example above), this becomes the following SQL:

select
    customer_id,
    order_date,
    amount,
    'USD' as currency
from analytics.dbt_staging.raw_sales
where order_date between '2023-01-01' and '2023-12-31'
    and customer_id not like 'TEST_%'

Use variables in calculations

You can also perform calculations with variables at compile time. The var() function takes a user-defined value as it’s first parameter, and a default value (0.08 in the example below).

dbt model:


select
    *,
    amount * {{ var("tax_rate", 0.08) }} as tax_amount,
    amount * (1 + {{ var("tax_rate", 0.08) }}) as total_amount
from {{ ref('orders') }}

Compiled SQL (assuming tax_rate is 0.08):

select
    *,
    amount * 0.08 as tax_amount,
    amount * (1 + 0.08) as total_amount
from analytics.dbt_staging.orders

Complex Variable Structures

You can use dictionaries and lists for more complex configurations.

Here is an example of project-level variables:

-- dbt_project.yml
vars:
  regional_config:
    us:
      currency: 'USD'
      tax_rate: 0.08
    eu:
      currency: 'EUR'
      tax_rate: 0.20
  excluded_categories: ['test', 'internal', 'demo']

These variables can be then accessed using bracket notation and filters.

dbt model:


select
    amount,
    '{{ var("regional_config")["us"]["currency"] }}' as currency,
    amount * {{ var("regional_config")["us"]["tax_rate"] }} as tax
from {{ ref('sales') }}
where category not in (
    {% for cat in var("excluded_categories") %}
        '{{ cat }}'{% if not loop.last %},{% endif %}
    {% endfor %}
)

Compiled SQL (using the variables defined above):

select
    amount,
    'USD' as currency,
    amount * 0.08 as tax
from analytics.dbt_staging.sales
where category not in (
        'test',
        'internal',
        'demo'
)

Environment-specific variables

Variables shine when managing different environments in dbt. You can use the built-in target variable to create environment-specific behavior.

Variables set at the project level:

-- dbt_project.yml
vars:
  schema_prefix: "{{ 'dev_' if target.name == 'dev' else '' }}"
  data_retention_days: "{{ 30 if target.name == 'dev' else 365 }}"

Then use these variables in your models:


select *
from {{ var("schema_prefix") }}customers
where created_date >= current_date - {{ var("data_retention_days") }}

Compiled SQL (in development environment):

select *
from dev_customers
where created_date >= current_date - 30

Compiled SQL (in production environment):

select *
from customers
where created_date >= current_date - 365

This approach ensures your models adapt automatically to different deployment environments without code changes.

Variable best practices

Provide default values

Always specify default values for variables to prevent compilation errors:


select *
from {{ ref('orders') }}
where status = '{{ var("order_status", "completed") }}'

Compiled SQL (when order_status variable is not defined):

select *
from analytics.dbt_staging.orders
where status = 'completed'

Use descriptive names

Choose variable names that clearly indicate their purpose:


-- Good
where created_date >= '{{ var("reporting_start_date") }}'

-- Less clear
where created_date >= '{{ var("date1") }}'

Type consistency

Be consistent with variable types. If a variable should be a boolean, always use true/false, not 1/0 or ‘yes’/‘no’:


{% if var("include_inactive_customers", false) %}
    -- include inactive customers
{% endif %}

Conclusion

Variables are a great way to create flexible, maintainable dbt models. Start with simple string and boolean variables, then gradually incorporate more complex structures as your needs grow. Remember that variables are resolved at compile time, making them perfect for controlling model behavior and structure before the code is executed in the data warehouse.

Want to see more Jinja use cases in dbt? Check out other articles in the series:

Illustration by Storyset

dbt data_engineering jinja