Skip to main content

The var() Function

In the previous section, we defined global variables in our dbt_project.yml file.

Now, how do we actually use those variables inside our SQL .sql files? We use the Jinja {{ var() }} function.

Topics Covered​

  • Basic Variable Extraction
  • Mathematical Operations with Variables
  • Variables in {% if %} statements
  • Setting Default Values

1. Basic Variable Extraction​

Let's assume our Data Engineer, Arjun, needs to calculate the final price of an order by adding the Indian GST tax rate.

He previously defined india_gst_rate: 0.18 in the dbt_project.yml.

To use it in his fct_orders.sql model, he uses the var() function inside double curly braces:

-- models/marts/fct_orders.sql

SELECT
order_id,
order_amount,

-- Multiply the amount by the global variable!
order_amount * {{ var('india_gst_rate') }} as gst_tax_amount,

order_amount * (1 + {{ var('india_gst_rate') }}) as total_amount

FROM {{ ref('stg_orders') }}

When dbt compiles this code, it looks up the variable and generates the pure SQL:

SELECT 
order_id,
order_amount,
order_amount * 0.18 as gst_tax_amount,
order_amount * (1 + 0.18) as total_amount
FROM BEEMS_PROD.STAGING.stg_orders

2. Variables in {% if %} Statements​

Variables are incredibly powerful for toggling logic on and off.

Remember the include_internal_users variable we defined? Rohan can use it to dynamically alter the WHERE clause of his model!

-- models/marts/fct_user_revenue.sql

SELECT
user_id,
SUM(order_amount) as total_revenue
FROM {{ ref('stg_orders') }}
JOIN {{ ref('stg_users') }} USING (user_id)

-- If the variable is FALSE, filter out the internal Beems employees!
{% if not var('include_internal_users') %}
WHERE email NOT LIKE '%@beems.in'
{% endif %}

GROUP BY 1

If the CFO runs dbt run --vars '{"include_internal_users": true}', dbt will completely remove the WHERE clause from the generated SQL!

3. Setting Default Values​

What happens if you use {{ var('my_custom_var') }} in your SQL, but you forgot to actually define it in your dbt_project.yml?

dbt will throw a fatal error and the pipeline will crash: Compilation Error: Required var 'my_custom_var' not found in config

To prevent this, you can provide a fallback default value directly inside the function. Just add a second argument!

-- models/marts/fct_orders.sql

-- If 'attribution_days' is not defined in the YAML, default to 14 days!
SELECT *
FROM {{ ref('stg_orders') }}
WHERE order_date >= CURRENT_DATE - {{ var('attribution_days', 14) }}

This is highly recommended when building modular packages, as it ensures your code never breaks even if another developer forgets to update the configuration file.

Summary​

  • Use the {{ var('variable_name') }} Jinja function to extract global constants into your SQL.
  • They are extremely useful for math (amount * var('tax_rate')) and conditional logic ({% if var('include_employees') %}).
  • You should always provide a fallback value by passing a second argument: {{ var('name', 'default_value') }}.