Skip to main content

Snowflake setup

profiles.yml file is for dbt Core users only

If you're using dbt Cloud, you don't need to create a profiles.yml file. This file is only for dbt Core users. To connect your data platform to dbt Cloud, refer to About data platforms.

  • Maintained by: dbt Labs
  • Authors: core dbt maintainers
  • GitHub repo: dbt-labs/dbt-snowflake
  • PyPI package: dbt-snowflake
  • Slack channel: #db-snowflake
  • Supported dbt Core version: v0.8.0 and newer
  • dbt Cloud support: Supported
  • Minimum data platform version: n/a

Installing dbt-snowflake

Use pip to install the adapter. Before 1.8, installing the adapter would automatically install dbt-core and any additional dependencies. Beginning in 1.8, installing an adapter does not automatically install dbt-core. This is because adapters and dbt Core versions have been decoupled from each other so we no longer want to overwrite existing dbt-core installations. Use the following command for installation:

Configuring dbt-snowflake

For Snowflake-specific configuration, please refer to Snowflake configs.

Authentication Methods​

User / Password authentication​

Snowflake can be configured using basic user/password authentication as shown below.

Along with adding the authenticator parameter, be sure to run alter account set allow_client_mfa_caching = true; in your Snowflake warehouse. Together, these will allow you to easily verify authentication with the DUO Mobile app (skipping this results in push notifications for every model built on every dbt run).

Key pair authentication​

To use key pair authentication, specify the private_key_path in your configuration, avoiding the use of a password. If needed, you can add a private_key_passphrase. Note: Unencrypted private keys are accepted, so add a passphrase only if necessary. However, for dbt Core versions 1.5 and 1.6, configurations using a private key in PEM format (for example, keys enclosed with BEGIN and END tags) are not supported. In these versions, you must use the private_key_path to reference the location of your private key file.

Starting from dbt version 1.7, dbt introduced the ability to specify a private_key directly as a string instead of a private_key_path. This private_key string can be in either Base64-encoded DER format, representing the key bytes, or in plain-text PEM format. Refer to Snowflake documentation for more info on how they generate the key.

Note: By default, every connection that dbt opens will require you to re-authenticate in a browser. The Snowflake connector package supports caching your session token, but it currently only supports Windows and Mac OS.

Refer to the Snowflake docs for info on how to enable this feature in your account.

OAuth authorization​

To learn how to configure OAuth in Snowflake, refer to their documentation. Your Snowflake admin needs to generate an OAuth token for your configuration to work.

Provide the OAUTH_REDIRECT_URI in Snowflake:http://localhost:PORT_NUMBER. For example, http://localhost:8080.

Once your Snowflake admin has configured OAuth, add the following to your profiles.yml file:


my-snowflake-db:
  target: dev
  outputs:
    dev:
      type: snowflake
      account: [account id]
      
      # The following fields are retrieved from the Snowflake configuration
      authenticator: oauth
      oauth_client_id: [OAuth client id]
      oauth_client_secret: [OAuth client secret]
      token: [OAuth refresh token]

Configurations​

The "base" configs for Snowflake targets are shown below. Note that you should also specify auth-related configs specific to the authentication method you are using as described above.

All configurations​

ConfigRequired?Description
accountYesThe account to connect to as per Snowflake's documentation. See notes below
userYesThe user to log in as
databaseYesThe database that dbt should create models in
warehouseYesThe warehouse to use when building models
schemaYesThe schema to build models into by default. Can be overridden with custom schemas
roleNo (but recommended)The role to assume when running queries as the specified user.
client_session_keep_aliveNoIf True, the snowflake client will keep connections for longer than the default 4 hours. This is helpful when particularly long-running queries are executing (> 4 hours). Default: False (see note below)
threadsNoThe number of concurrent models dbt should build. Set this to a higher number if using a bigger warehouse. Default=1
query_tagNoA value with which to tag all queries, for later searching in QUERY_HISTORY view
retry_allNoA boolean flag indicating whether to retry on all Snowflake connector errors
retry_on_database_errorsNoA boolean flag indicating whether to retry after encountering errors of type snowflake.connector.errors.DatabaseError
connect_retriesNoThe number of times to retry after an unsuccessful connection
connect_timeoutNoThe number of seconds to sleep between failed connection retries
reuse_connectionsNoA boolean flag indicating whether to reuse idle connections to help reduce total connections opened. Default is False.

account​

For AWS accounts in the US West default region, you can use abc123 (without any other segments). For some AWS accounts you will have to append the region and/or cloud platform. For example, abc123.eu-west-1 or abc123.eu-west-2.aws.

For GCP and Azure-based accounts, you have to append the region and cloud platform, such as gcp or azure, respectively. For example, abc123.us-central1.gcp. For details, see Snowflake's documentation: "Specifying Region Information in Your Account Hostname".

Please also note that the Snowflake account name should only be the account_name without the prefixed organization_name. To determine if the region and/or cloud platform needs to be appended to the account locator in the legacy format, see Snowflake's documentation on "Non-VPS account locator formats by cloud platform and region".

client_session_keep_alive​

The client_session_keep_alive feature is intended to keep Snowflake sessions alive beyond the typical 4 hour timeout limit. The snowflake-connector-python implementation of this feature can prevent processes that use it (read: dbt) from exiting in specific scenarios. If you encounter this in your deployment of dbt, please let us know in the GitHub issue, and work around it by disabling the keepalive.

query_tag​

Query tags are a Snowflake parameter that can be quite useful later on when searching in the QUERY_HISTORY view.

reuse_connections​

During node execution (such as model and test), dbt opens connections against a Snowflake warehouse. Setting this configuration to True reduces execution time by verifying credentials only once for each thread.

retry_on_database_errors​

The retry_on_database_errors flag along with the connect_retries count specification is intended to make retries configurable after the snowflake connector encounters errors of type snowflake.connector.errors.DatabaseError. These retries can be helpful for handling errors of type "JWT token is invalid" when using key pair authentication.

retry_all​

The retry_all flag along with the connect_retries count specification is intended to make retries configurable after the snowflake connector encounters any error.

0
Edit this page