dbt profiles.yml: The Connection File That Powers Everything

Every time dbt connects to your data warehouse — to run models, execute tests, or generate docs — it reads profiles.yml to know where to connect and how. Understanding this file well means you can move seamlessly between development and production, manage credentials securely, and avoid the configuration headaches that slow teams down.

Where profiles.yml Lives

By default, dbt looks for profiles.yml at:

~/.dbt/profiles.yml

This is intentional. Keeping it in your home directory (not inside the project) means it never accidentally gets committed to source control. Credentials stay on your machine, not in Git.

You can override this location with the --profiles-dir flag:

dbt run --profiles-dir /path/to/custom/dir

This is useful in CI pipelines where you want to write the file to a specific location during the build step.

The Basic Structure

A minimal profiles.yml looks like this:

my_project:
  target: dev
  outputs:
    dev:
      type: postgres
      host: localhost
      port: 5432
      user: analyst
      password: secretpassword
      dbname: analytics
      schema: dbt_dev
      threads: 4

Breaking it down:

• my_project — must match the profile: value in your dbt_project.yml
• target — the default output to use when you run dbt run without specifying one
• outputs — a named map of connection configurations
• dev — the name of this specific output (you can have as many as you need)
• threads — how many models dbt runs in parallel

Multiple Environments: dev, staging, prod

Most teams need at least two environments: a development environment where individual engineers experiment, and a production environment where scheduled jobs run against real data.

my_project:
  target: dev
  outputs:

    dev:
      type: postgres
      host: localhost
      port: 5432
      user: "{{ env_var('DBT_USER') }}"
      password: "{{ env_var('DBT_PASSWORD') }}"
      dbname: analytics
      schema: "dbt_{{ env_var('DBT_USER') }}"   # each dev gets their own schema
      threads: 4

    prod:
      type: postgres
      host: prod-db.internal
      port: 5432
      user: dbt_service_account
      password: "{{ env_var('DBT_PROD_PASSWORD') }}"
      dbname: analytics
      schema: public
      threads: 8

To run against production explicitly:

dbt run --target prod

In CI, you typically set the DBT_TARGET environment variable or pass --target prod in the pipeline step.

Never Hardcode Credentials

Hardcoding passwords directly in profiles.yml is a security risk — even if the file isn’t committed to Git, plain-text credentials in config files get copied, shared, and leaked.

dbt supports Jinja templating in profiles.yml, which means you can use env_var() to pull in values from the environment at runtime:

password: "{{ env_var('DBT_PASSWORD') }}"

Set the variable in your shell or CI environment:

export DBT_PASSWORD="your-actual-password"

For production, use a secrets manager (AWS Secrets Manager, GCP Secret Manager, HashiCorp Vault) to inject environment variables into your CI runner or orchestrator, rather than storing passwords anywhere in plaintext.

Adapter-Specific Configuration Examples

The type field determines which adapter is used. Each adapter has its own required and optional fields.

Snowflake:

snowflake_project:
  target: dev
  outputs:
    dev:
      type: snowflake
      account: "{{ env_var('SNOWFLAKE_ACCOUNT') }}"
      user: "{{ env_var('SNOWFLAKE_USER') }}"
      password: "{{ env_var('SNOWFLAKE_PASSWORD') }}"
      role: TRANSFORMER
      database: ANALYTICS
      warehouse: TRANSFORMING
      schema: DBT_DEV
      threads: 8
      client_session_keep_alive: false

BigQuery (using service account key):

bigquery_project:
  target: dev
  outputs:
    dev:
      type: bigquery
      method: service-account
      project: my-gcp-project-id
      dataset: dbt_dev
      keyfile: /path/to/service-account.json
      threads: 4
      timeout_seconds: 300

BigQuery (using OAuth, for local dev):

    dev:
      type: bigquery
      method: oauth
      project: my-gcp-project-id
      dataset: dbt_dev
      threads: 4

DuckDB (great for local development without a cloud warehouse):

local_project:
  target: dev
  outputs:
    dev:
      type: duckdb
      path: ./local.duckdb
      threads: 4

Per-Developer Schemas

A common pattern on teams is to give each developer their own schema, preventing conflicts when multiple people run models against the same database:

schema: "dbt_{{ env_var('DBT_USER') }}"

Each developer sets DBT_USER to their own name in their shell profile:

export DBT_USER=alice

When Alice runs dbt run, models land in dbt_alice. When Bob runs it, they land in dbt_bob. The production target uses a fixed schema that only the CI pipeline writes to.

Verifying Your Configuration

After writing your profiles.yml, always run:

dbt debug

This prints a checklist showing:

• Whether the profiles.yml file was found and parsed correctly
• Whether dbt_project.yml is valid
• Whether the profile name matches
• Whether the database connection was successfully established

All checks should show OK. If you see a connection failure, dbt debug prints the specific error from the database driver, which is much more useful for troubleshooting than generic error messages.

profiles.yml in CI/CD

In CI pipelines, you typically don’t have a pre-existing profiles.yml. Instead, you write one at the start of the pipeline using secret environment variables, or you use dbt Cloud (which manages connections entirely in its UI).

For a GitHub Actions example:

- name: Write profiles.yml
  run: |
    mkdir -p ~/.dbt
    cat > ~/.dbt/profiles.yml << EOF
    my_project:
      target: prod
      outputs:
        prod:
          type: postgres
          host: ${{ secrets.DB_HOST }}
          user: ${{ secrets.DB_USER }}
          password: ${{ secrets.DB_PASSWORD }}
          dbname: analytics
          schema: public
          threads: 4
    EOF

- name: Run dbt
  run: dbt build --target prod

GitHub Actions secrets are injected as environment variables and are never written to logs or stored in the repository.

Quick Reference

profiles.yml Structure

~/.dbt/profiles.yml
│
└── <profile_name>              ← matches profile: in dbt_project.yml
    ├── target: dev             ← default target
    └── outputs:
        ├── dev:                ← development connection
        │   ├── type: postgres
        │   ├── host: ...
        │   ├── schema: dbt_dev
        │   └── threads: 4
        └── prod:               ← production connection
            ├── type: postgres
            ├── host: ...
            ├── schema: public
            └── threads: 8

The profiles.yml file is small but critical. Getting it right — with proper environment separation and no hardcoded secrets — is the foundation that everything else in your dbt workflow depends on.