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') }}.