Using dbt env_var() Safely: Secret Management Pitfalls and Practical Patterns

env_var() is convenient, but misuse can leave passwords in compiled SQL or logs. For both the exam and real work, it is essential to understand where it is evaluated and where the values end up.

This article walks through safe usage and anti-patterns based on the official dbt-core behavior, how to combine it with CI/CD and Vault, and how to think about audit.

env_var() Basics and Evaluation Timing

env_var(name, default=None) reads OS environment variables at the moment Jinja is evaluated. In dbt, the main evaluation points for Jinja are when models and macros are compiled, and when dbt_project.yml and profiles.yml are loaded.

Crucially, if you use env_var() inside a SQL file, the value can end up baked into the compiled SQL under target/compiled. The rule is: never bake secrets into SQL — reference them only from connection info (profiles.yml).

profiles.yml is not included in run artifacts (manifest.json, run_results.json, catalog.json). On the other hand, values supplied via dbt_project.yml vars or model config can show up in artifacts, so avoid placing secrets there.

• env_var() returns a string. If you need a number, convert explicitly with a Jinja filter (e.g., | int).
• Casually passing a default risks silently connecting with the wrong default credentials. For secrets, omit the default and fail explicitly when unset.
• Even in local development, inject environment variables temporarily via export rather than leaving them in plaintext files like .env.

Safe reference example in profiles.yml (Snowflake)

snowflake:
  target: prod
  outputs:
    prod:
      type: snowflake
      account: "{{ env_var('SNOWFLAKE_ACCOUNT') }}"
      user: "{{ env_var('SNOWFLAKE_USER') }}"
      password: "{{ env_var('SNOWFLAKE_PASSWORD') }}"
      role: "{{ env_var('SNOWFLAKE_ROLE') }}"  # Even non-secret values: be cautious with default
      warehouse: "{{ env_var('SNOWFLAKE_WAREHOUSE') }}"
      database: ANALYTICS
      schema: CORE

Where Secrets Leak: Distribution Paths and Threat Model

The places where secrets most often leak are: compiled SQL, run logs, dbt artifacts (such as manifest.json), and CI/CD job logs or environment dumps. env_var() itself simply returns a value, so where you reference it is what matters most.

dbt does mask some sensitive data in places, but not everything is guaranteed. On the operations side, enforce a strict don't-emit, don't-retain design.

• Compiled SQL: Using env_var() inside a model can leave the value in plaintext.
• Artifacts: Values in dbt_project.yml vars and parts of model config can end up in manifest.json. Never put secrets in vars.
• Logs: Do not accidentally print values via debug output (--debug) or custom log() calls. CI environment dumps are also off-limits.
• Repository: Plaintext .env or profiles.yml, and accidental commits of them, are strictly forbidden.

Dangerous example (printing or embedding in SQL inside a model)

-- model.sql (do NOT do this)
{{ log(env_var('SNOWFLAKE_PASSWORD'), info=True) }}  -- ends up in logs
select '{{ env_var('SNOWFLAKE_PASSWORD') }}' as leaked; -- left in compiled SQL

Recommended Patterns: Sources and How dbt Consumes Them

The principle: store secrets in an external secret manager, inject them temporarily as environment variables during CI/CD execution, and let dbt reference them only via env_var() in profiles.yml. This keeps secrets out of SQL and artifacts and makes rotation easy.

If you use dbt Cloud, prefer provider integrations and managed connection settings, and avoid handling secrets via arbitrary env_var values. For OSS execution, combine env_var with your CI platform's masking features (GitHub Actions secrets, GitLab CI variables, etc.).

• External storage: store and rotate secrets in HashiCorp Vault or a cloud Secret Manager.
• Short-lived tokens: prefer OAuth and short-lived tokens. Avoid long-lived passwords.
• Inject at runtime: only inject into the environment within the CI/CD job. Locally, export within a temporary shell session.
• Reference only from profiles.yml: do not flow them into dbt_project.yml vars, model SQL, or macro output.

Temporary injection example with GitHub Actions (OSS execution)

jobs:
  run-dbt:
    runs-on: ubuntu-latest
    steps:
      - uses: actions/checkout@v4
      - uses: actions/setup-python@v5
        with:
          python-version: '3.11'
      - run: pip install dbt-snowflake
      - name: Run dbt
        env:
          SNOWFLAKE_ACCOUNT: ${{ secrets.SNOWFLAKE_ACCOUNT }}
          SNOWFLAKE_USER: ${{ secrets.SNOWFLAKE_USER }}
          SNOWFLAKE_PASSWORD: ${{ secrets.SNOWFLAKE_PASSWORD }}
          SNOWFLAKE_WAREHOUSE: ${{ secrets.SNOWFLAKE_WAREHOUSE }}
        run: |
          dbt deps
          dbt build --target prod

Anti-Patterns and Countermeasures

Patterns such as putting secrets in vars, printing them via log() inside models or macros, or interpolating them into SQL via Jinja all increase long-term leak risk. vars in particular ends up in artifacts, so it is forbidden.

Silently passing through with a default when a variable is unset is another common source of incidents. Provide a helper macro to check env_var() presence and fail explicitly when missing.

• Storing secrets in vars → may end up in manifest.json.
• Embedding into SQL via Jinja → plaintext remains under target/compiled.
• Logging output or dumping the environment → spreads via CI log retention and external sharing.
• Overuse of default → connects with unintended credentials and delays detection.

Required env var validation macro (fails on missing)

{% macro required_env_var(name) -%}
  {% set val = env_var(name) %}
  {% if not val %}
    {{ exceptions.raise_compiler_error('Missing required env var: ' ~ name) }}
  {% endif %}
  {{ return(val) }}
{%- endmacro %}

# Usage in profiles.yml (Jinja allowed)
password: "{{ required_env_var('SNOWFLAKE_PASSWORD') }}"

Comparison: How to Handle Secrets in dbt

The correct answer for both real work and the exam is the same configuration: store externally, inject as environment variables at runtime, and reference only from profiles.yml via env_var(). The table below compares the typical options.

• Focus on artifact-free, ease of rotation, and exam-recommended.
• If dbt Cloud is available, first consider the platform's management features.

Databricks (token) example: profiles.yml

databricks:
  target: prod
  outputs:
    prod:
      type: databricks
      host: "{{ env_var('DATABRICKS_HOST') }}"
      http_path: "{{ env_var('DATABRICKS_HTTP_PATH') }}"
      token: "{{ required_env_var('DATABRICKS_TOKEN') }}"
      catalog: main
      schema: core

Operations, Audit Points, and Exam Prep Checklist

From an audit perspective, the essentials are: automated scans of artifacts, compiled outputs, and logs for secrets; CI masking configuration and privilege separation; and periodic checks of token expiry and rotation. On the dbt side, stick to two rules: reference secrets only via env_var() in profiles.yml, and never let them flow into vars or SQL.

For exam prep, lock in these points: when env_var() is evaluated, the fact that secrets placed in vars end up in manifest.json, the danger of silently passing through with a default when unset, and the preference for platform management in dbt Cloud.

• Do not dump env in CI (no set -x or printenv).
• Minimize the permissions of the dbt execution user. Separate roles and workspaces as well.
• Scan artifacts, the target directory, and logs automatically for secret patterns (e.g., acct_ or token=).

Safe secret flow (conceptual diagram)

 [Secret Manager]
         |
         v   (masked, short-lived)
     [CI/CD Runner] --(env export)--> [Process Env]
                                      |
                                      v
                               [dbt env_var()]
                                      |
                                      v
                               [Adapter/Driver]
                                      |
                                      v
                               [Warehouse/BI]

Guide comment example for not using env_var() inside models or macros

{#
Notes:
- Do NOT pass env_var() into model SQL or log().
- Reference secrets only from profiles.yml.
- When unset, fail immediately with required_env_var().
#}

Check Your Understanding

Frequently Asked Questions