How to write a profiles.yml for Snowflake

profiles.yml tells dbt how to connect: which account, which user, which database and schema to build in. It is kept outside the project because it holds personal settings and credentials. One profile usually has two targets: dev, where you build in your own schema, and prod, used by the scheduler.

Copy and paste

# ~/.dbt/profiles.yml
shop:                                 # must match "profile:" in dbt_project.yml
  target: dev                         # the target used when no --target is given
  outputs:
    dev:
      type: snowflake
      account: myorg-myaccount
      user: NICOLA@EXAMPLE.COM
      authenticator: externalbrowser  # sign in with your company SSO
      role: TRANSFORMER_DEV
      database: ANALYTICS_DEV
      warehouse: TRANSFORMING
      schema: DBT_NICOLA
      threads: 8

    prod:
      type: snowflake
      account: myorg-myaccount
      user: DBT_SERVICE
      role: TRANSFORMER
      private_key_path: /secure/keys/rsa_key.p8
      private_key_passphrase: "{{ env_var('DBT_ENV_SECRET_KEY_PASSPHRASE') }}"
      database: ANALYTICS
      warehouse: TRANSFORMING
      schema: ANALYTICS
      threads: 16
      query_tag: dbt_prod

dbt debug                  # checks the dev target
dbt debug --target prod
dbt build --target prod

The fields

Field	What it is
`account`	the account identifier, in the `orgname-accountname` form shown in Snowsight
`user`	the Snowflake user dbt signs in as
authentication	one of: `authenticator: externalbrowser` (SSO), `private_key_path` or `private_key` (key pair), `authenticator: username_password_mfa` with `password`
`role`	the role dbt uses; it overrides the user’s default role
`database`, `schema`	where models go when they have no custom database or schema
`warehouse`	the warehouse that runs the queries
`threads`	how many models dbt builds at the same time

Optional fields include query_tag (labels dbt’s queries in Snowflake’s query history), client_session_keep_alive, connect_retries, connect_timeout and reuse_connections.

Where dbt looks for it

dbt uses the first profiles.yml it finds, in this order:

the folder given with --profiles-dir;
the folder in the DBT_PROFILES_DIR environment variable;
the current folder, usually the project;
~/.dbt/.

dbt debug prints which file it used, which saves time when two copies exist.

Things to know

One schema per developer. schema: DBT_NICOLA keeps your models apart from your colleagues’ and from production. Custom schemas build on it; see how to change the target schema.
No secrets in the file. Read passwords and passphrases from environment variables; see how to use environment variables in profiles.yml. If the file lives in the project, add it to .gitignore.
Service users and keys. Snowflake is blocking passwords for service users; key-pair authentication explains the production target above.
Threads. More threads mean more queries at once on the same warehouse. Between 4 and 16 is common; beyond the warehouse’s capacity, queries queue instead of running faster.

The profile was validated with dbt Core 1.12 and dbt-snowflake 1.12.4 (dbt debug reports profiles.yml file [OK found and valid]); the SSO target was not run.

How to write a profiles.yml for Snowflake

Copy and paste

The fields

Where dbt looks for it

Things to know

References