dbt profiles.yml and Connection Management: Best Practices per Environment

profiles.yml is the core file that defines dbt's runtime connections. Designing it so you can switch environments safely and reproducibly is critical for both developer productivity and incident prevention.

This article walks through the points the Analytics Engineer exam loves to test — profile lookup order, target resolution priority, adapter-specific keys, and env_var usage — with practical examples you can drop into real work.

profiles.yml Basics and Lookup Order

dbt reads the entry in profiles.yml whose key matches the profile name in dbt_project.yml, then connects using the outputs of the selected target. The standard path for profiles.yml is under your user directory, but DBT_PROFILES_DIR can point dbt to a different location — useful when you want CI to read profiles.yml from the repo root.

The structure has target (the default execution environment name) and outputs (per-environment connection definitions) under the profile name. outputs holds adapter-specific keys such as Snowflake's account and warehouse, or Databricks' host and http_path. Jinja and env_var are stable features of profiles.yml, so avoid writing secrets directly.

• Lookup order in short: if DBT_PROFILES_DIR is set, the profiles.yml under it wins; otherwise ~/.dbt/profiles.yml
• Default OS paths: ~/.dbt/profiles.yml on macOS/Linux, %USERPROFILE%\.dbt\profiles.yml on Windows
• Profile name must match: the profile: value in dbt_project.yml has to match the top-level key in profiles.yml
• target is the default environment used when none is specified. You can override it with --target or DBT_TARGET

Minimal profiles.yml skeleton (Snowflake example)

ja_lab:
  target: dev
  outputs:
    dev:
      type: snowflake
      account: {{ env_var('SF_ACCOUNT') }}
      user: {{ env_var('SF_USER') }}
      password: {{ env_var('SF_PASSWORD') }}
      role: ANALYST
      database: ANALYTICS
      warehouse: WH_XS
      schema: dev
      threads: 4
    stg:
      type: snowflake
      account: {{ env_var('SF_ACCOUNT') }}
      user: {{ env_var('SF_USER') }}
      password: {{ env_var('SF_PASSWORD') }}
      role: ANALYST
      database: ANALYTICS
      warehouse: WH_S
      schema: stg
      threads: 6
    prod:
      type: snowflake
      account: {{ env_var('SF_ACCOUNT') }}
      user: {{ env_var('SF_USER') }}
      password: {{ env_var('SF_PASSWORD') }}
      role: PROD_ROLE
      database: ANALYTICS
      warehouse: WH_M
      schema: prod
      threads: 8

Strategy for Switching Targets per Environment

Target resolution has a priority order: the CLI's --target wins, then the DBT_TARGET environment variable, and finally the target in profiles.yml. The easiest workflow is to set --target explicitly in CI and temporarily set DBT_TARGET for local development.

Use short, collision-free environment names like dev/stg/prod. Combined with --profiles-dir, you can safely point at a project-specific profiles.yml even inside a monorepo.

• Priority: --target > DBT_TARGET > target in profiles.yml
• For temporary switches use DBT_TARGET=..., and for deterministic runs pass --target ... explicitly
• An undefined-target error means the name does not exist under outputs in profiles.yml

Target resolution flow

Sample switching commands

# Run against stg temporarily
export DBT_TARGET=stg
 dbt run

# Specify explicitly in CI
 dbt build --target prod --profiles-dir .

# Verify (connection check)
 dbt debug --target dev

Inject Credentials Safely

Design profiles.yml assuming it will live under version control, and reference every secret through env_var. dbt evaluates Jinja and substitutes env_var('KEY') values at runtime, so passwords and tokens stay out of the code while environment differences remain expressive.

In CI/CD, inject environment variables via each runner's secret feature. Databricks uses personal access tokens; Snowflake supports key-pair auth and OAuth in addition to passwords. Pick the method that fits your operational requirements and assign least-privilege roles per environment.

• Never hard-code secrets in profiles.yml — use env_var
• If you use .env, do not commit it to the repository (add it to .gitignore)
• In CI, use the runner's secret store so values are injected without an explicit export
• Snowflake commonly uses key/token/password methods; Databricks typically uses a token

Excerpts of a safe profile written with env_var, plus environment variable setup

# profiles.yml (excerpt)
ja_lab:
  target: dev
  outputs:
    dev:
      type: snowflake
      account: {{ env_var('SF_ACCOUNT') }}
      user: {{ env_var('SF_USER') }}
      password: {{ env_var('SF_PASSWORD') }}
      role: {{ env_var('SF_ROLE', 'ANALYST') }}
      database: ANALYTICS
      warehouse: {{ env_var('SF_WAREHOUSE', 'WH_XS') }}
      schema: dev

# Databricks example (token auth)
    dev_dw:
      type: databricks
      catalog: hive_metastore
      schema: dev
      host: {{ env_var('DB_HOST') }}
      http_path: {{ env_var('DB_HTTP_PATH') }}
      token: {{ env_var('DB_TOKEN') }}
      threads: 4

# Local environment variable setup (example)
export SF_ACCOUNT="xy12345.ap-southeast-1"
export SF_USER="analyst"
export SF_PASSWORD="..."
export DB_HOST="adb-123456789.11.azuredatabricks.net"
export DB_HTTP_PATH="/sql/1.0/warehouses/xxxx"
export DB_TOKEN="dapi..."

Adapter-by-Adapter Connection Parameter Comparison

Required keys and recommended auth methods differ by adapter. Deciding up front where to put environment differences — warehouse/role for Snowflake, http_path or catalog/schema for Databricks, host or database for Postgres — keeps profiles.yml lean.

On the exam, you are most often tested on each adapter's type name, its typical required keys, and how to switch environments.

• Common: type, schema, and threads appear across most adapters
• Watch consistency of role and warehouse on Snowflake, token and http_path on Databricks, and user/password/port on Postgres
• Manage environment diffs through items that directly affect cost and permissions — warehouse size, role, target database

Minimal Databricks SQL Warehouse configuration

ja_lab:
  target: dev_dw
  outputs:
    dev_dw:
      type: databricks
      schema: dev
      host: {{ env_var('DB_HOST') }}
      http_path: {{ env_var('DB_HTTP_PATH') }}
      token: {{ env_var('DB_TOKEN') }}
      threads: 4

Reusable profiles.yml Patterns

Combining YAML anchors with Jinja keeps environment differences to a minimum. Bundle the base definition under &base and let dev/stg/prod override only what changes. Driving schema, role, and warehouse from target.name or environment variables makes the file easier to manage.

Controlling execution parameters like threads and retry behavior through environment variables makes it easier to share the same profiles.yml between CI and local development.

• Define the base output as &base and inherit with <<: *base
• Make schema dynamic, e.g. analytics_{{ target.name }}
• Push highly sensitive logic into the application layer and keep profiles.yml expressions minimal

Minimize environment diffs with anchors and Jinja

ja_lab:
  target: dev
  outputs:
    base: &base
      type: snowflake
      account: {{ env_var('SF_ACCOUNT') }}
      user: {{ env_var('SF_USER') }}
      password: {{ env_var('SF_PASSWORD') }}
      database: ANALYTICS
      role: {{ env_var('SF_ROLE', 'ANALYST') }}
      warehouse: {{ env_var('SF_WH', 'WH_S') }}
      schema: analytics_{{ target.name }}
      threads: {{ env_var('DBT_THREADS', 4) | int }}
    dev:
      <<: *base
    stg:
      <<: *base
      warehouse: {{ env_var('SF_WH_STG', 'WH_M') }}
    prod:
      <<: *base
      role: PROD_ROLE
      warehouse: {{ env_var('SF_WH_PROD', 'WH_L') }}
      threads: {{ env_var('DBT_THREADS_PROD', 8) | int }}

Troubleshooting and Exam Perspectives

Errors around profiles.yml are usually easy to root-cause but easy to introduce. Start with dbt debug and isolate the issue in this order: profile discovery, credentials, then permissions. On the exam, profile lookup, target resolution, and permission design are common topics.

Typical symptoms and remedies:

• profile 'xxx' does not exist: the profile name in dbt_project.yml does not match the key in profiles.yml, or DBT_PROFILES_DIR points to the wrong place
• target 'stg' is not defined: outputs has no stg entry
• permission denied: connection succeeded but the role/schema lacks privileges. Review per-environment role assignments
• Use dbt debug to validate the connection, and dbt debug -t <target> to confirm the target

Checklist commands

# Profile discovery and connection check
 dbt debug --profiles-dir . --target dev

# Specify the execution target explicitly
 dbt run --target stg

# Run everything including tests
 dbt build --target prod

Check Your Understanding

Frequently Asked Questions