Environment variables in dbt: precedence, secrets and built-ins

1.Referencing environment variables with env_var

An environment variable lets one dbt project behave differently depending on where it runs. The same model code can build into a developer's schema in the Studio IDE, a staging database in a pre-release job and production tables on the nightly schedule. Nothing in the repository changes between those runs. The code asks for a value by name, and dbt supplies that value at run time.

The env_var Jinja function reads the value. It takes a key and an optional default: {{ env_var('DBT_KEY', 'OPTIONAL_DEFAULT') }}. You can call it anywhere dbt processes Jinja, including profiles.yml, dbt_project.yml, source and schema files, and model .sql files. Inside YAML, put quotes around the whole Jinja string, or the curly brackets will confuse the YAML parser. A default keeps compilation working when the variable isn't available. In this materialization setting, the default is view:

+materialized: "{{ env_var('DBT_MATERIALIZATION', 'view') }}"

The dbt platform enforces strict naming. Every key must start with DBT_, DBT_ENV_SECRET_ or DBT_ENV_CUSTOM_ENV_. Keys are uppercase and case sensitive. The key passed to env_var() must exactly match the variable defined in the dbt UI, so env_var('DBT_Warehouse') will not find a variable saved as DBT_WAREHOUSE. When a self-hosted project moves to the platform, any variable names without the prefix need a find-and-replace across the project.

Sources12

2.The four levels of precedence

A value can be set at four levels, and dbt uses the most specific one that exists. From lowest to highest precedence, the levels are the default argument in the env_var() call, the project default, the environment value, and a job override or a developer's personal override. A lower level only applies when no level above it sets a value.

Project and environment values are set on the same screen: Orchestration > Environments > Environment variables. A value in the Project default column applies wherever the code runs, which makes it the place for a catch-all fallback or a project-wide token or secret. Each environment has its own column to the right. That is where you give staging and production different values.

A job inherits the values of the deployment environment it runs in. Several jobs often share one environment. When one of them needs a different value, its settings let you override any variable defined at the environment or project level. That setting is covered with the other [optional job settings](lesson:4.6).

Developers can override values too, in the Studio IDE. Without a personal override, the IDE uses the values set in the project's development environment. A developer's override applies to both the Results and the Compiled SQL tabs.

A gap in this chain causes a hard failure. Suppose a variable has no project default, nothing sets it in the context where the code is running, and the env_var() call has no default. Compilation then stops with Env var required but not provided. Setting a project default for every variable means dbt always has a value to use in every context.

Sources1

3.Making jobs and connections environment-aware

A common use of a job override is matching compute to the workload. Say a full-refresh job needs an XL Snowflake warehouse, but the incremental job in the same environment runs fine on a medium one. Instead of naming the warehouse in the connection, set it to {{env_var('DBT_WAREHOUSE')}} and give each job its own DBT_WAREHOUSE value. The same approach works with a Databricks SQL Warehouse.

A value is fixed for the whole run. Each env_var has a single value for the entire run, so you can't switch warehouses between models in one run. That needs two jobs.

Connections are where variables and authentication meet. Env vars work with username/password and key pair credentials, including in scheduled jobs. With Snowflake OAuth, they can set database, warehouse and role, but not the account/host field. Extended attributes have their own limit. When a deployment environment uses profiles, any env_var reference in its extended attributes must point to a project-scoped variable. Profiles don't belong to any one environment, so a connection test can't see a value set only in an environment's column. The test fails even though real job runs succeed. Setting the value in the Project default column fixes this.

Variables are also the recommended way to tell environments apart in code. You can set custom target.name values in development credentials or orchestration settings. Environment variables are the preferred method because dbt's partial parsing handles them better than {{ target }} logic.

In the Studio IDE, a value changed mid-session may not take effect until the IDE restarts. Click the status message in the bottom right corner and choose Restart IDE. If compiled output still shows old values, delete target/partial_parse.msgpack to force dbt to re-compile the whole project.

Sources134

4.Secret and custom metadata prefixes

All environment variables are encrypted at rest. The DBT_ENV_SECRET_ prefix adds more protection. A secret's value is removed from logs and error messages and replaced with *****. It is hidden in the UI and in artifacts, and it is decrypted only when a flow needs it, such as a job run. The main use is a git access token for cloning private packages.

Secrets stay protected because dbt limits where they can be used. They are available only in profiles.yml and packages.yml. Inside those files, dbt inserts the plain string value and nothing more. You can't apply Jinja filters to a secret, including as_number or as_bool, and you can't pass it into other macros. Each configuration value can hold only one secret:

# works
host: "{{ env_var('DBT_ENV_SECRET_HOST') }}"

# does not work
host: "www.{{ env_var('DBT_ENV_SECRET_HOST_DOMAIN') }}.com/{{ env_var('DBT_ENV_SECRET_HOST_PATH') }}"

A git token stored as a variable should still have limited permissions. Make it read-only, and prefer a PAT from a machine account or service user with limited repo access over a person's own token.

DBT_ENV_CUSTOM_ENV_ does the opposite of the secret prefix: it publishes values. dbt strips the prefix from each of these variables and adds them under env in the artifact metadata and under info -> extra in structured logs. The dbt_metadata_envs context variable also exposes them as a dictionary. Run with DBT_ENV_CUSTOM_ENV_MY_FAVORITE_COLOR=indigo, and {{ dbt_metadata_envs }} compiles to a dictionary with the key MY_FAVORITE_COLOR.

Sources21

5.Built-in variables dbt sets for you

dbt sets some variables itself. You can't change them, and the order of precedence doesn't apply to them. No project default, environment value or job override replaces them. They tell your code which environment, job, run or branch it is running in.

DBT_CLOUD_GIT_BRANCH is a Studio IDE variable. It follows the developer's branch as it changes, with no IDE restart, so you can use the branch name as a prefix for a development schema. It is not available in the dbt CLI. Code that calls it without a default fails there.

DBT_CLOUD_ENVIRONMENT_TYPE is empty in a General deployment environment, so always give it a default:

{{ env_var('DBT_CLOUD_ENVIRONMENT_TYPE', '') }}

DBT_CLOUD_INVOCATION_CONTEXT is the variable that tells CI apart from production, because one of its values is ci. Use it in the generate_schema_name() macro to make CI runs use only the default schema, with the dbt_cloud_pr prefix. This holds even when CI jobs run in the same environment as production jobs. The macro itself belongs with [default schemas for environments](lesson:3.6).

Sources1

6.String values and local runs

Every environment variable value is a string. That doesn't matter for a schema name, but it breaks settings such as threads or persist_docs that expect a number or a boolean. An unconverted '1' fails with '1' is not of type 'integer'. Convert the value with a filter: int or as_number for integers, as_bool for booleans. Write boolean defaults as capitalized True or False. Lowercase true is treated as a string and can give unexpected results.

"{{ env_var('DBT_THREADS') |  ?  }}"

Outside the platform, variables come from your own machine. Local dbt and the dbt VS Code extension read them from your shell profile (~/.zshrc or ~/.bashrc) or from a .env file. If both set the same variable, the shell value wins over .env. dbt loads .env only from the directory you run commands from. It ignores --project-dir, so keep the file at the project root, run dbt from there and add .env to .gitignore. The extension's menu actions don't pick up variables exported in a terminal. Set those in the dbt.environmentVariables user setting or in .env. When you copy values over from the platform's Environment variables tab, masked secrets stay hidden, so get those values from an admin.

Sources2

Exam traps

Each one states something that sounds right. Open it to see what is actually true.

1. 1.A DBT_ENV_SECRET_ variable can be used in model SQL or dbt_project.yml, and dbt will just mask it in the logs.Why is that wrong?

   Secret variables are available only in profiles.yml and packages.yml. Everywhere else they are rejected, so they can't leak into the warehouse or artifacts.

   Covered in Secret and custom metadata prefixes

2. 2.DBT_CLOUD_GIT_BRANCH can prefix development schemas for developers using either the Studio IDE or the dbt CLI.Why is that wrong?

   dbt sets DBT_CLOUD_GIT_BRANCH automatically in the Studio IDE only. It is not available in the dbt CLI.

   Covered in Built-in variables dbt sets for you

3. 3.If jobs in a deployment environment succeed, an env_var in its extended attributes is configured correctly, so the connection test should pass too.Why is that wrong?

   Profiles don't belong to any one environment, so a connection test only sees project-scoped values. A variable set only at the environment level makes the test fail while jobs still run.

   Covered in Making jobs and connections environment-aware

4. 4.Writing env_var('DBT_FLAG', false) | as_bool with a lowercase default gives a proper boolean default.Why is that wrong?

   Boolean defaults must be capitalized True or False. Lowercase is treated as a string.

   Covered in String values and local runs