Skip to main content

About env_var function

The env_var function can be used to incorporate environment variables from the system into your dbt project. You can use the env_var function in your profiles.yml file, the dbt_project.yml file, the sources.yml file, your schema.yml files, and in model .sql files. Essentially, env_var is available anywhere dbt processes Jinja code.

When used in a profiles.yml file (to avoid putting credentials on a server), it can be used like this:

profiles.yml
profile:
  target: prod
  outputs:
    prod:
      type: postgres
      host: 127.0.0.1
      # IMPORTANT: Make sure to quote the entire Jinja string here
      user: "{{ env_var('DBT_USER') }}"
      password: "{{ env_var('DBT_PASSWORD') }}"
      ....

If the DBT_USER and DBT_ENV_SECRET_PASSWORD environment variables are present when dbt is invoked, dbt will use these variables in your connection configuration — for example, in profiles.yml when running locally, or in connection profiles if you have a dbt platform project. If your project references environment variables that aren't set, dbt will raise a compilation error.

(Applies to dbt v1.12 and later)

Using the .env file​

When running dbt locally (the dbt CLI, dbt VS Code extension, and dbt Core v1.12), dbt automatically loads environment variables from a .env file in your current working directory (where you run the dbt command). Shell environment variables take precedence over values in .env and will not be overridden by the file.

Create a .env file (typically at the root of your dbt project) and define variables using KEY=value syntax. For example:

.env
DBT_USER=user
DBT_PASSWORD=password
DBT_SCHEMA=dbt_schema

Reference them in your profiles.yml using env_var():

~/.dbt/profiles.yml
my_profile:
  target: dev
  outputs:
    dev:
      type: postgres
      host: localhost
      user: "{{ env_var('DBT_USER') }}"
      password: "{{ env_var('DBT_PASSWORD') }}"
      schema: "{{ env_var('DBT_SCHEMA') }}"
      port: 5432
      threads: 4

Here are some considerations when defining environment variables in the .env file:

  • The .env file provides a convenient way to set environment variables that work across both the CLI and the VS Code extension.
  • We recommend placing your .env file in the project root and running dbt commands from that location because the file is loaded only from your current working directory. It doesn't support the --project-dir flag or (Applies to dbt v1.11 and later) DBT_ENGINE_PROJECT_DIR environment variable, and dbt won't search your project root if you're running commands from a different directory location.
  • Add .env to your .gitignore file to prevent sensitive credentials from being committed to your repository.
  • Environment variables set directly in your shell (such as export DBT_ENV_VAR=value) take precedence over values defined in the .env file.

Converting env_vars​

Environment variables are always strings. When using them for configurations that expect integers or booleans, you must explicitly convert the value to the correct type.

Use a Jinja filter to convert the string to the correct type:

  • Integers — Convert the string to a number using the int or as_number filter to avoid errors like '1' is not of type 'integer'. For example, "{{ env_var('DBT_THREADS') | int }}" or "{{ env_var('DB_PORT') | as_number }}".

  • Booleans — Convert the string to a boolean explicitly using the as_bool filter. For example, "{{ env_var('DBT_PERSIST_DOCS_RELATION', False) | as_bool }}".

For boolean defaults, use capitalized True or False. Using lowercase true or false will be treated as a string and can result in unexpected results.

For example, to disable persist_docs using environment variables:

dbt_project.yml
+persist_docs:
  relation: "{{ env_var('DBT_PERSIST_DOCS_RELATION', False) | as_bool }}"
  columns: "{{ env_var('DBT_PERSIST_DOCS_COLUMNS', False) | as_bool }}"
Quoting, curly brackets, & you

Be sure to quote the entire Jinja string. Otherwise, the YAML parser will be confused by the Jinja curly brackets.

Default values​

You can also provide a default value as a second argument:

dbt_project.yml
...
models:
  jaffle_shop:
    +materialized: "{{ env_var('DBT_MATERIALIZATION', 'view') }}"

This can be useful to avoid compilation errors when the environment variable isn't available.

Secrets​

For certain configurations, you can use "secret" env vars. Any env var named with the prefix DBT_ENV_SECRET will be:

  • Available for use in profiles.yml + packages.yml, via the same env_var() function
  • Disallowed everywhere else, including dbt_project.yml and model SQL, to prevent accidentally writing these secret values to the data warehouse or metadata artifacts
  • Scrubbed from dbt logs and replaced with *****, any time its value appears in those logs (even if the env var was not called directly)

The primary use case of secret env vars is git access tokens for private packages.

Note: When dbt is loading profile credentials and package configuration, secret env vars will be replaced with the string value of the environment variable. You cannot modify secrets using Jinja filters, including type-casting filters such as as_number or as_bool, or pass them as arguments into other Jinja macros. You can only use one secret per configuration:

# 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') }}"

Custom metadata​

Any env var named with the prefix DBT_ENV_CUSTOM_ENV_ will be included in two places, with its prefix-stripped name as the key:

A dictionary of these prefixed env vars will also be available in a dbt_metadata_envs context variable:

-- {{ dbt_metadata_envs }}

select 1 as id
$ DBT_ENV_CUSTOM_ENV_MY_FAVORITE_COLOR=indigo DBT_ENV_CUSTOM_ENV_MY_FAVORITE_NUMBER=6 dbt compile

Compiles to:

-- {'MY_FAVORITE_COLOR': 'indigo', 'MY_FAVORITE_NUMBER': '6'}

select 1 as id

dbt platform usage​

If you're using dbt platform, environment variables must be:

  • Prefixed with DBT_ (including DBT_ENV_CUSTOM_ENV_ or DBT_ENV_SECRET)
  • Uppercase
  • Case-sensitive

When referencing {{env_var('DBT_KEY')}} in your project's code, the key must exactly match the variable defined in the dbt platform user interface.

Was this page helpful?

This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

0
Loading