vars and env_var

Concepts covered: Jinja in models

Every value so far was typed inside the project's own files. Some values belong outside them: the first day a model should include, chosen by whoever starts the run, or the schema a raw table sits in, which differs between a laptop and the shared warehouse. dbt reads such values through 2 functions. var() reads a project variable, set in dbt_project.yml or on the command line, and env_var() reads the shell environment dbt was started in.

select
    order_id,
    order_date,
    amount
from {{ ref('stg_orders') }}
where order_date >= date '{{ var("start_date", "2024-01-01") }}'

var() takes the variable's name and an optional default. With nothing set anywhere, the default renders and the model keeps rows from 2024-01-01 on. The project file can set it for every run, and the command can set it for one run.

name: shop
version: '1.0.0'
profile: shop
vars:
  start_date: '2024-06-01'

Running dbt run --vars '{"start_date": "2025-10-01"}' overrides the project file for that run alone; the file keeps its value for the next one. The argument is a small YAML or JSON mapping, so a var can be a number, a list or a date string, and var() hands it back with that type, which is why a list passed this way can be looped over like one a macro returned.

A var under vars: applies to every file in the project; nesting it under the project's own name, or under an installed package's name, scopes it to that one project's files.

env_var

env_var() reads a value the shell set before dbt started, such as export DBT_RAW_SCHEMA=raw_dev. It takes the name and an optional default, and it works in every file dbt renders, including dbt_project.yml, profiles.yml and packages.yml, so a password or a token never has to be written into a file that is committed.

sources:
  - name: raw
    schema: "{{ env_var('DBT_RAW_SCHEMA', 'raw') }}"
    tables:
      - name: orders
      - name: order_items

With the variable unset, source('raw', 'orders') resolves to raw.orders; with it set to raw_dev, every model that reads the source compiles to raw_dev.orders and no model file changes. A name that is missing and has no default stops the run at compile time with Env var required but not provided, before any SQL is sent. Every value comes back as a string, since the shell holds only strings, so a number from the environment is text until the SQL casts it. A variable whose name starts with DBT_ENV_SECRET is treated as a secret: dbt allows it only in profiles.yml and packages.yml and masks its value in logs.

{%- set lookback_days = (env_var('DBT_LOOKBACK_WEEKS', '2') | int) * 7 -%}

select
    order_id,
    order_date
from {{ ref('stg_orders') }}
where order_date >= current_date - interval '{{ lookback_days }}' day

With nothing set, the default '2' becomes the number 2 through the int filter, and the model compiles to interval '14' day. Drop the filter and Jinja multiplies a string instead: '2' * 7 is '2222222', so the model keeps 2,222,222 days of orders, which is every row, and no stage of the run raises an error. The same value passed as --vars '{"lookback_weeks": 2}' arrives as the number 2 and needs no filter.

var() fits

• A parameter of one run, chosen by the person or schedule starting it
• A value with a sensible committed default in dbt_project.yml
• A typed value: a date string, a number, a list of codes
• A value it is fine to see in the command and the logs

env_var() fits

• A property of the place dbt runs: a schema name, a hostname
• A credential or token that must never sit in a committed file
• A value set once by the shell and read by every command run in it
• A value profiles.yml or packages.yml needs before the project is read

Do

• Give every var() and env_var() a default when a missing value has a safe meaning
• Name environment variables with a DBT_ prefix so their purpose is clear in the shell
• Keep the committed default the one the shared warehouse should use

Avoid

• Pass a password through --vars, it lands in the shell history and the logs
• Read the same value through both functions in different models
• Use env_var() for a value that changes run to run on the same machine

About this lesson

This section is part of the Jinja and Macros: Intermediate lesson on DataDriven, the home of data engineering. Lessons cover SQL, Python, Spark, data modeling and pipeline architecture, and each section pairs its explanation with worked examples and practice you run as you go.