env_var() для секретов и configs

В предыдущем уроке мы использовали target.name для conditional logic. Но target не подходит для секретов — нельзя hard-code API ключ в profiles.yml. И не для environment-specific values, которые меняются между deploys (database host, port, credentials).

Решение — env_var(). Это Jinja-функция, которая читает environment variable из shell. Secrets хранятся вне репозитория (в .env локально, в GitHub secrets / Vault в CI/CD), и dbt подхватывает через env_var.

Этот урок — про env_var syntax, defaults, security best practices, и common patterns для multi-env.

Базовый syntax

{{ env_var('VAR_NAME') }}                    # требуется — error если не set
{{ env_var('VAR_NAME', 'default_value') }}   # с дефолтом

В profiles.yml:

prod:
  type: snowflake
  account: "{{ env_var('SNOWFLAKE_ACCOUNT') }}"
  user: "{{ env_var('SNOWFLAKE_USER') }}"
  password: "{{ env_var('SNOWFLAKE_PASSWORD') }}"
  database: "{{ env_var('SNOWFLAKE_DATABASE', 'ANALYTICS_PROD') }}"
  warehouse: "{{ env_var('SNOWFLAKE_WAREHOUSE', 'COMPUTE_WH') }}"
  schema: "{{ env_var('SNOWFLAKE_SCHEMA', 'marts') }}"

Запуск:

export SNOWFLAKE_USER=alice
export SNOWFLAKE_PASSWORD=secret123
export SNOWFLAKE_ACCOUNT=xy12345.us-east-1
dbt run --target prod

dbt подставит values.

env_var в SQL моделях

-- models/marts/customer_metrics.sql
SELECT
    customer_id,
    revenue
FROM {{ ref('stg_orders') }}
{% if env_var('DBT_ENVIRONMENT', 'dev') == 'prod' %}
  WHERE order_total > 0
{% endif %}

В prod — фильтр по positive revenue. В dev — без фильтра (data exploration).

env_var в dbt_project.yml

vars:
  reference_date: "{{ env_var('REFERENCE_DATE', '2026-05-01') }}"
  sample_size: "{{ env_var('SAMPLE_SIZE', '1000') | int }}"

В CI:

export REFERENCE_DATE='2026-05-15'
export SAMPLE_SIZE='5000'
dbt run --target ci

dbt подставит vars.

Defaults: required vs optional

Required (no default):

{{ env_var('PROD_PASSWORD') }}

Если env var не set -> dbt fails:

Env var 'PROD_PASSWORD' was not found

Useful для critical vars — fail fast если missing.

Optional (with default):

{{ env_var('DBT_THREADS', '4') }}

Если не set -> uses ‘4’. Useful для optional configurations.

Production strategy:

• Secrets (passwords, API keys): required (no default). Fail loudly если missing.
• Configs (threads, sample size, environment flag): with safe default. Continue if not set.

env_var для secrets handling

Secrets никогда не commit в repo. Pattern:

Local .env (in .gitignore):

# .env
SNOWFLAKE_USER=alice
SNOWFLAKE_PASSWORD=mySecret123
SNOWFLAKE_ACCOUNT=xy12345.us-east-1

Source перед dbt run:

source .env
dbt run --target prod

Или через dotenv package:

dotenv -f .env run -- dbt run --target prod

CI/CD (GitHub Actions):

- name: Run dbt
  run: dbt run --target prod
  env:
    SNOWFLAKE_USER: ${{ secrets.SNOWFLAKE_USER }}
    SNOWFLAKE_PASSWORD: ${{ secrets.SNOWFLAKE_PASSWORD }}
    SNOWFLAKE_ACCOUNT: ${{ secrets.SNOWFLAKE_ACCOUNT }}

secrets.* — encrypted в GitHub. Не visible в logs.

Production secrets manager:

• HashiCorp Vault
• AWS Secrets Manager
• GCP Secret Manager
• Azure Key Vault

Pattern: deploy script reads secrets -> exports as env vars -> dbt run.

DBT-prefixed env vars

dbt автоматически парсит:

DBT_PROFILES_DIR: location of profiles.yml.

export DBT_PROFILES_DIR=./profiles
dbt run   # читает ./profiles/profiles.yml

Полезно для CI:

# .github/workflows/ci.yml
env:
  DBT_PROFILES_DIR: ./profiles

DBT_VAR_<varname>: override var через env:

export DBT_VAR_REFERENCE_DATE='2026-05-15'
dbt run
# == dbt run --vars '{reference_date: 2026-05-15}'

Useful для CI/CD configs.

DBT_TARGET: override target:

export DBT_TARGET=prod
dbt run   # == dbt run --target prod

DBT_DEFER_TO_PROD: env-флаг для --defer режима. Полная механика Slim CI / --defer / state:modified+ — в модуле 13-ci-cd-github. Здесь только показано, что флаг конфигурируется через env_var.

Type casting env vars

env_var всегда возвращает string. Если нужен int / boolean — explicit cast:

{{ env_var('THREADS', '4') | int }}              # integer
{{ env_var('ENABLE_PERSIST', 'false') | as_bool }} # boolean (1.x+)
{{ env_var('TIMEOUT_SECONDS', '60') | int }}      # integer

| as_bool parses ‘true’, ‘True’, ‘1’, ‘yes’ -> True. Others -> False.

Common mistake:

threads: "{{ env_var('DBT_THREADS', '4') }}"
# threads is string '4', not int 4 — dbt may fail or behave unexpectedly

Correct:

threads: "{{ env_var('DBT_THREADS', '4') | int }}"

Security best practices

1. Never commit secrets

.gitignore must contain:

.env
.env.local
.env.*.local
profiles.yml   # if contains secrets directly

Use .env.example для template:

# .env.example (commits!)
SNOWFLAKE_USER=
SNOWFLAKE_PASSWORD=
SNOWFLAKE_ACCOUNT=

Developer copies to .env and fills.

2. Check git history

git log --all -p | grep -i 'password\|secret\|api_key'

If secrets leaked в history — rotate them. Git history is permanent даже если remove file.

3. Use external secrets manager в production

For team production deploys — Vault / AWS Secrets Manager / GCP Secret Manager. NOT environment files на servers.

Deployment script:

# Pseudo-code
SNOWFLAKE_PASSWORD=$(vault kv get -field=password secret/snowflake/prod)
SNOWFLAKE_USER=$(vault kv get -field=user secret/snowflake/prod)
dbt run --target prod

4. Rotate secrets periodically

• Database passwords: every 90 days
• API keys: every 180 days
• Service account keys: every 365 days

Automate через secrets manager rotation.

5. Audit logs

Track когда secrets accessed. Vault / AWS Secrets Manager provide audit logs. Detect anomalous access.

6. Least privilege

dbt prod user should have only permissions для:

• READ from sources
• READ / WRITE to dbt-managed schemas
• NOT DROP / ALTER outside dbt schemas

NOT admin / superuser — limit blast radius если credentials compromised.

Common patterns

Pattern 1: Same code, different envs

# profiles.yml — все environment vars
prod:
  type: snowflake
  account: "{{ env_var('SF_ACCOUNT') }}"
  user: "{{ env_var('SF_USER') }}"
  password: "{{ env_var('SF_PASSWORD') }}"
  database: "{{ env_var('SF_DATABASE') }}"
  warehouse: "{{ env_var('SF_WAREHOUSE') }}"

Deploy:

# Production
export SF_DATABASE=ANALYTICS_PROD
export SF_WAREHOUSE=PROD_WH
dbt run --target prod

# Staging
export SF_DATABASE=ANALYTICS_STAGING
export SF_WAREHOUSE=STAGING_WH
dbt run --target prod   # same target name, different env vars

# DR (disaster recovery)
export SF_DATABASE=ANALYTICS_DR
dbt run --target prod

Same code, multiple deploys через env vars.

Pattern 2: Feature flags

{% if env_var('ENABLE_NEW_REVENUE_FORMULA', 'false') | as_bool %}
  -- New formula
  SUM(order_total - refund_amount) AS revenue
{% else %}
  -- Old formula
  SUM(order_total) AS revenue
{% endif %}

Toggle через env var без redeploy:

export ENABLE_NEW_REVENUE_FORMULA=true
dbt run --select revenue_metrics

Pattern 3: Per-deploy customization

vars:
  log_level: "{{ env_var('LOG_LEVEL', 'INFO') }}"
  enable_partition: "{{ env_var('ENABLE_PARTITION', 'false') | as_bool }}"

Different deploys (CI, production, DR) use different vars без YAML changes.

Pattern 4: env var в seeds

# seeds.yml
seeds:
  my_project:
    +column_types:
      api_endpoint: "{{ env_var('API_ENDPOINT', 'https://api.prod.com') }}"

(Edge case — usually seeds are static.)

Antipatterns

1. Secrets in committed profiles.yml

# profiles.yml — COMMITTED!
prod:
  password: "actualSecret123"

Never. Use env_var.

2. Hardcoded default for secret

password: "{{ env_var('PROD_PASSWORD', 'fallback123') }}"

Fallback = leaked secret. No default для secrets — must fail if missing.

3. Same env var for multiple environments

prod:
  password: "{{ env_var('DB_PASSWORD') }}"
staging:
  password: "{{ env_var('DB_PASSWORD') }}"   # same!

If prod password compromised -> staging also compromised. Use separate:

prod:
  password: "{{ env_var('PROD_DB_PASSWORD') }}"
staging:
  password: "{{ env_var('STAGING_DB_PASSWORD') }}"

4. Forgot type casting

threads: "{{ env_var('DBT_THREADS', '4') }}"   # string '4', not int!

Bug surfaces at runtime — confusing. Always | int, | as_bool.

5. env_var в test data

# unit test
overrides:
  env_vars:
    PROD_PASSWORD: 'test123'   # mock secret

OK для testing — но don’t commit real secrets in tests, even if mocked.

6. No documentation для required env vars

Developer clones repo, runs dbt, falls с unclear error ‘env var X not found’. README must list ALL required env vars:

# Setup

Required env vars:
- `SNOWFLAKE_USER` — Snowflake username
- `SNOWFLAKE_PASSWORD` — Snowflake password
- `SNOWFLAKE_ACCOUNT` — Snowflake account identifier
- `DBT_PROFILES_DIR` — path to profiles.yml

Copy `.env.example` to `.env` and fill values.

CI/CD: GitHub Actions complete example

# .github/workflows/dbt-ci.yml
name: dbt CI

on: [pull_request, push]

env:
  DBT_PROFILES_DIR: ./profiles

jobs:
  unit-tests:
    runs-on: ubuntu-latest
    steps:
      - uses: actions/checkout@v4
      - uses: actions/setup-python@v5
        with: { python-version: '3.11' }
      - run: pip install dbt-core==1.10.21 dbt-duckdb==1.10.1
      - run: dbt deps
      - run: dbt parse
      - run: dbt test --select test_type:unit

  ci-build:
    runs-on: ubuntu-latest
    needs: unit-tests   # only if unit tests pass
    steps:
      - uses: actions/checkout@v4
      - uses: actions/setup-python@v5
        with: { python-version: '3.11' }
      - run: pip install dbt-core==1.10.21 dbt-duckdb==1.10.1
      - run: dbt deps
      - name: Build with CI target
        run: dbt build --target ci
        env:
          # CI uses ephemeral DuckDB, no secrets needed
          DBT_VAR_REFERENCE_DATE: '2026-05-19'
          DBT_VAR_SAMPLE_SIZE: '10000'

  prod-deploy:
    runs-on: ubuntu-latest
    needs: ci-build
    if: github.ref == 'refs/heads/main'
    steps:
      - uses: actions/checkout@v4
      - uses: actions/setup-python@v5
      - run: pip install dbt-core==1.10.21 dbt-snowflake==1.10.1
      - run: dbt deps
      - name: Build prod
        run: dbt build --target prod
        env:
          SF_ACCOUNT: ${{ secrets.SNOWFLAKE_ACCOUNT }}
          SF_USER: ${{ secrets.SNOWFLAKE_USER }}
          SF_PASSWORD: ${{ secrets.SNOWFLAKE_PASSWORD }}
          SF_DATABASE: 'ANALYTICS_PROD'
          SF_WAREHOUSE: 'PROD_WH'

Pattern:

• Secrets через GitHub Secrets
• Configs через DBT_VAR_*
• Same workflow file для всех envs (different secrets only)

Попробуй сам

1. Setup .env locally:

   # .env (in .gitignore)
   SNOWFLAKE_USER=test_user
   SNOWFLAKE_PASSWORD=test_password
   SNOWFLAKE_ACCOUNT=test_account
   DBT_PROFILES_DIR=./profiles

2. Profiles.yml использует env_var:

   prod:
     type: snowflake
     user: "`{{ env_var('SNOWFLAKE_USER') }}`"
     password: "`{{ env_var('SNOWFLAKE_PASSWORD') }}`"

3. Test: dbt parse — должен pass с env vars. unset одну var -> fails.

4. Add to gitignore:

   .env
   .env.*
   profiles.yml

5. Document:

   # README.md
   Required env vars: SNOWFLAKE_USER, SNOWFLAKE_PASSWORD, ...
   Copy .env.example to .env and fill.

6. CI integration: GitHub Secrets, workflow with env vars.

Ключевые выводы

1. env_var() — Jinja function для reading environment variables. Used для secrets и multi-env configs.
2. Syntax: {{ env_var('VAR_NAME') }} (required) или {{ env_var('VAR_NAME', 'default') }} (optional).
3. Secrets without defaults — fail loudly если не set. Configs with defaults — graceful fallback.
4. DBT_PROFILES_DIR / DBT_TARGET / DBT_VAR_* — special env vars dbt automatically reads.
5. Type casting через filters: | int, | as_bool, | float. env_var always returns string.
6. Security: never commit secrets (gitignore), no fallback для passwords, separate secrets per env, external secrets manager в production, audit logs, least privilege.
7. Common patterns: same code different envs (production vs staging vs DR), feature flags, per-deploy customization.
8. Antipatterns: committed secrets, fallback для critical vars, same env_var across environments, forgotten type casting, undocumented required vars.