How to use environment variables in profiles.yml (env_var)

A profiles.yml with passwords in it cannot be shared or committed. env_var() reads values from environment variables instead, so the file can be the same on every laptop and in CI, while each environment supplies its own credentials.

Copy and paste

# profiles.yml
shop:
  target: "{{ env_var('DBT_TARGET', 'dev') }}"
  outputs:
    dev:
      type: snowflake
      account: "{{ env_var('SNOWFLAKE_ACCOUNT') }}"
      user: "{{ env_var('SNOWFLAKE_USER') }}"
      private_key_path: "{{ env_var('SNOWFLAKE_PRIVATE_KEY_PATH') }}"
      private_key_passphrase: "{{ env_var('DBT_ENV_SECRET_KEY_PASSPHRASE') }}"
      role: TRANSFORMER
      database: ANALYTICS_DEV
      warehouse: TRANSFORMING
      schema: "{{ env_var('DBT_SCHEMA', 'DBT_DEV') }}"
      threads: "{{ env_var('DBT_THREADS', '8') | as_number }}"
      client_session_keep_alive: "{{ env_var('DBT_KEEP_ALIVE', 'False') | as_bool }}"

Set the variables, for example from a .env file that stays out of Git:

# .env
SNOWFLAKE_ACCOUNT=myorg-myaccount
SNOWFLAKE_USER=DBT_SERVICE
SNOWFLAKE_PRIVATE_KEY_PATH=/secure/keys/rsa_key.p8
DBT_ENV_SECRET_KEY_PASSPHRASE=change-me
DBT_SCHEMA=DBT_NICOLA

set -a; source .env; set +a
dbt debug

How env_var behaves

Quote the Jinja. In YAML, {{ at the start of a value is read as a mapping. Always wrap it in quotes: "{{ env_var('X') }}".
A missing variable stops dbt, unless you give a default:

Parsing Error
  Env var required but not provided: 'SNOWFLAKE_ACCOUNT'

Everything is a string. A field that expects a number fails with profiles.yml file [ERROR invalid] until you convert it with | as_number (or | int).
Booleans need True or False. | as_bool converts the Python spellings only. With the variable set to true in lowercase, the profile fails with Could not convert value 'true' into type 'bool'.

Secret variables

Variables whose name starts with DBT_ENV_SECRET_ are masked in dbt’s logs. dbt allows them only in profiles.yml and packages.yml. Used in a model, they stop the run:

Secret env vars are allowed only in profiles.yml or packages.yml. Found 'DBT_ENV_SECRET_TOKEN' referenced elsewhere.

Use the prefix for passwords, passphrases, private keys and tokens; use ordinary names for the rest.

Elsewhere in the project

env_var() also works in dbt_project.yml and in models, with ordinary variables:

select '{{ env_var('DBT_REGION', 'eu') }}' as region

For values that change from run to run, such as a date, prefer –vars: they appear in the command and in the run’s artifacts, so it is clear which value a run used.

Tested with dbt Core 1.12 (DuckDB and Snowflake adapters).

References

dbt env_var function