Handling Environment Variables and Secrets Safely in dbt CI: A Practical Guide to Per-Environment Switching

When running dbt in CI, the two biggest pitfalls are leaking secrets and inconsistent environment switching. This article shows the minimal setup for safely switching between dev, staging, and prod using dbt's built-in features (env_var, profiles.yml, target) and a typical CI secret store.

We also organise the topics that appear frequently on the Analytics Engineer exam. Pay close attention to env_var defaults, Jinja in profiles.yml, target.name usage, CI log masking, and per-environment schema switching.

Foundations: The Roles of env_var, profiles.yml, and target

dbt can read environment variables through Jinja. The form env_var('KEY', 'default') safely pulls in values injected by CI or set locally. Omit the default and any missing variable raises a compile error, preventing accidents.

Connection settings live in profiles.yml, and target switches the destination between dev, stg, prod, and so on. Models can reference target.name or target.schema. The golden rule: secrets (passwords, tokens, connection strings) must be injected as environment variables from a CI or dbt Cloud secret store, never written into the repo.

• env_var can be referenced from dbt_project.yml, profiles.yml, or model Jinja
• Without a default, an undefined variable errors out -> CI fails fast
• Target name is the smallest switching unit. Switch via configuration rather than branching on environment names inside SQL

Basic env_var usage (model Jinja and dbt_project.yml)

-- models/stg_orders.sql
{{
  config(
    schema=env_var('DBT_SCHEMA', target.schema)  # CIで上書き、未設定ならtarget.schema
  )
}}

select *
from {{ source('raw', 'orders') }}

-- dbt_project.yml(抜粋)
name: my_dbt
version: 1.0.0
config-version: 2

models:
  +materialized: view

vars:
  environment: "{{ env_var('DBT_ENVIRONMENT', 'dev') }}"

Per-Environment Switching: Designing profiles.yml and target

The standard practice is to absorb per-environment differences in profiles.yml. Make the target overridable via env_var, and append an environment suffix to schema and database names. This keeps CI and local switching symmetric.

Centralise schema-name branching with the generate_schema_name macro or config(schema=...). Switching via configuration rather than if-statements in SQL is more maintainable and aligns with model exam answers.

• Externalise the profiles.yml target with env_var
• Make schema, database, role, warehouse, and so on overridable via environment variables
• Naming convention: keep them unique, e.g. <base_schema>_<environment>

Snowflake example: externalising environment and secrets in profiles.yml

my_profile:
  target: "{{ env_var('DBT_TARGET_NAME', 'dev') }}"
  outputs:
    dev:
      type: snowflake
      account: "{{ env_var('SNOWFLAKE_ACCOUNT') }}"
      user: "{{ env_var('SNOWFLAKE_USER') }}"
      password: "{{ env_var('SNOWFLAKE_PASSWORD') }}"
      role: "{{ env_var('SNOWFLAKE_ROLE', 'DEV_ROLE') }}"
      warehouse: "{{ env_var('SNOWFLAKE_WAREHOUSE', 'DEV_WH') }}"
      database: "{{ env_var('SNOWFLAKE_DATABASE', 'ANALYTICS_DEV') }}"
      schema: "{{ env_var('DBT_SCHEMA', 'dbt_dev') }}"
    ci:
      type: snowflake
      account: "{{ env_var('SNOWFLAKE_ACCOUNT') }}"
      user: "{{ env_var('SNOWFLAKE_USER') }}"
      password: "{{ env_var('SNOWFLAKE_PASSWORD') }}"
      role: "{{ env_var('SNOWFLAKE_ROLE', 'CI_ROLE') }}"
      warehouse: "{{ env_var('SNOWFLAKE_WAREHOUSE', 'CI_WH') }}"
      database: "{{ env_var('SNOWFLAKE_DATABASE', 'ANALYTICS_CI') }}"
      schema: "{{ env_var('DBT_SCHEMA', 'dbt_ci') }}"
    prod:
      type: snowflake
      account: "{{ env_var('SNOWFLAKE_ACCOUNT') }}"
      user: "{{ env_var('SNOWFLAKE_USER') }}"
      password: "{{ env_var('SNOWFLAKE_PASSWORD') }}"
      role: "{{ env_var('SNOWFLAKE_ROLE', 'PROD_ROLE') }}"
      warehouse: "{{ env_var('SNOWFLAKE_WAREHOUSE', 'PROD_WH') }}"
      database: "{{ env_var('SNOWFLAKE_DATABASE', 'ANALYTICS') }}"
      schema: "{{ env_var('DBT_SCHEMA', 'dbt') }}"

Comparing CI Secret-Management Options and Best Practices

Secret stores built into CI platforms (GitHub Actions Secrets, GitLab CI/CD Variables, CircleCI Contexts, and so on) inject values as environment variables and automatically mask them in logs. dbt simply reads them via env_var, which keeps things simple and safe.

In large-scale or highly regulated environments, teams typically pair CI with a dedicated store like HashiCorp Vault, having CI fetch a short-lived token and inject it. Either way, the rules are the same: never write secrets in the repo or profiles.yml, follow least privilege, and keep them out of logs.

• Never put secrets in the repo (do not commit them)
• Enable CI log masking and protected-branch usage controls
• Prefer easily rotatable auth (key pairs, OAuth, short-lived tokens)

Safe injection on GitHub Actions (bundled into env)

env:
  DBT_TARGET_NAME: ci
  DBT_ENVIRONMENT: ci
  SNOWFLAKE_ACCOUNT: ${{ secrets.SNOWFLAKE_ACCOUNT }}
  SNOWFLAKE_USER: ${{ secrets.SNOWFLAKE_USER }}
  SNOWFLAKE_PASSWORD: ${{ secrets.SNOWFLAKE_PASSWORD }}
  SNOWFLAKE_ROLE: ${{ secrets.SNOWFLAKE_ROLE }}
  SNOWFLAKE_WAREHOUSE: ${{ secrets.SNOWFLAKE_WAREHOUSE }}
  SNOWFLAKE_DATABASE: ${{ secrets.SNOWFLAKE_DATABASE }}

A Standard CI Pipeline Example and Data Flow (ASCII Diagram)

The minimal setup is a serial chain of dependency resolution -> compile/validate -> run -> test. Pin target to ci and use CI-only resources for the schema/database. Logs are masked and SQL contains no embedded secrets.

Below is an example of running dbt-snowflake on GitHub Actions. The same idea applies to any CI's YAML.

• deps -> debug (connection check) -> compile -> run -> test, in order, to fail fast
• Centralise switching with --target ci and env_var
• Treat the CI schema as disposable to reduce collisions and cleanup work

Secret injection and execution flow in CI

GitHub Actions: dbt CI workflow (excerpt)

name: dbt-ci
on:
  pull_request:
    branches: [ main ]

jobs:
  run-dbt:
    runs-on: ubuntu-latest
    env:
      DBT_TARGET_NAME: ci
      DBT_ENVIRONMENT: ci
      SNOWFLAKE_ACCOUNT: ${{ secrets.SNOWFLAKE_ACCOUNT }}
      SNOWFLAKE_USER: ${{ secrets.SNOWFLAKE_USER }}
      SNOWFLAKE_PASSWORD: ${{ secrets.SNOWFLAKE_PASSWORD }}
      SNOWFLAKE_ROLE: ${{ secrets.SNOWFLAKE_ROLE }}
      SNOWFLAKE_WAREHOUSE: ${{ secrets.SNOWFLAKE_WAREHOUSE }}
      SNOWFLAKE_DATABASE: ${{ secrets.SNOWFLAKE_DATABASE }}
      DBT_SCHEMA: dbt_ci_${{ github.actor }}
    steps:
      - uses: actions/checkout@v4
      - uses: actions/setup-python@v5
        with:
          python-version: '3.11'
      - name: Install dbt adapter
        run: |
          python -m pip install --upgrade pip
          pip install dbt-snowflake
      - name: Verify deps and connection
        run: |
          dbt deps
          dbt debug --target ci --no-write-json
      - name: Compile
        run: dbt compile --target ci --warn-error
      - name: Run and test
        run: |
          dbt run  --target ci --fail-fast --select state:modified+
          dbt test --target ci --fail-fast

Log Safety and Pitfalls: Keep Secrets Out of Output

Masking can fail when CI scripts use set -x or echo to print environment variables. The rule: do not reference, log, or display secrets. dbt's own logs print things like connection test results, so enable CI-side masking and minimise debug verbosity.

If a required environment variable is missing in Jinja, raise a compile error early to halt execution. This prevents accidents like running against the wrong target or with anonymous credentials.

• Explicitly set +x in shells to suppress command echo
• Run dbt debug in CI with --no-write-json and an appropriate log level
• Throw an exception when env_var is missing so you fail fast

Practical snippets for keeping secrets out of logs

# シェル: コマンドエコー抑止
set +x

# Jinja: 必須環境変数の検査(任意のmacroやmodel冒頭で)
{% if not env_var('SNOWFLAKE_PASSWORD', none) %}
  {{ exceptions.raise_compiler_error('Missing SNOWFLAKE_PASSWORD') }}
{% endif %}

# dbt実行時は必要最小限の出力
# 例: 失敗時にのみ詳細を参照する運用
dbt run --target ci --fail-fast
# 必要なら --log-level warn などで情報量を絞る

Exam Prep: Key Points and Checklist

The Analytics Engineer exam frequently tests separation of concerns between environment variables and configuration. The keys are: keep environment-specific branching out of SQL by switching via profiles.yml and target, inject secrets from a CI or dbt Cloud secure store, and understand env_var defaults.

Operational judgment questions also come up, such as whether the same pipeline can swap configuration from dev to prod, and whether secrets leak into logs or metadata.

• The default value of env_var('KEY', 'default') and behaviour when unset
• Jinja rendering in profiles.yml and references to target.name
• Avoid environment collisions with schema/database naming conventions
• Mask secrets in CI logs and never write them in the repo

Example commands for switching environments

# CIターゲットで実行
DBT_TARGET_NAME=ci DBT_ENVIRONMENT=ci dbt run --target ci

# ローカル開発(デフォルトのdevにフォールバック)
dbt run  # profiles.ymlのtarget: dev が使われることを想定

# 明示的にprodを指定(本番は保護ブランチ/承認必須が望ましい)
DBT_TARGET_NAME=prod dbt test --target prod

Check Your Understanding

Frequently Asked Questions