Snowflake setup
profiles.yml
file is for CLI users onlyIf you're using dbt Cloud, you don't need to create a profiles.yml
file. This file is only for CLI users. To connect your data platform to dbt Cloud, refer to About data platforms.
Overview of dbt-snowflake
- 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
pip is the easiest way to install the adapter:
pip install dbt-snowflake
Installing dbt-snowflake
will also install dbt-core
and any other dependencies.
Configuring dbt-snowflake
For Snowflake-specifc configuration please refer to Snowflake Configuration
For further info, refer to the GitHub repository: dbt-labs/dbt-snowflake
Authentication Methods
User / Password authentication
Snowflake can be configured using basic user/password authentication as shown below.
my-snowflake-db:
target: dev
outputs:
dev:
type: snowflake
account: [account id]
# User/password auth
user: [username]
password: [password]
role: [user role]
database: [database name]
warehouse: [warehouse name]
schema: [dbt schema]
threads: [1 or more]
client_session_keep_alive: False
query_tag: [anything]
# optional
connect_retries: 0 # default 0
connect_timeout: 10 # default: 10
retry_on_database_errors: False # default: false
retry_all: False # default: false
reuse_connections: False # default: false (available v1.4+)
User / Password + DUO MFA authentication
Snowflake integrates the DUO Mobile app to add 2-Factor authentication to basic user/password as seen below.
my-snowflake-db:
target: dev
outputs:
dev:
type: snowflake
account: [account id]
# User/password auth
user: [username]
password: [password]
authenticator: username_password_mfa
role: [user role]
database: [database name]
warehouse: [warehouse name]
schema: [dbt schema]
threads: [1 or more]
client_session_keep_alive: False
query_tag: [anything]
# optional
connect_retries: 0 # default 0
connect_timeout: 10 # default: 10
retry_on_database_errors: False # default: false
retry_all: False # default: false
reuse_connections: False # default: false (available v1.4+)
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 authenatication 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, omit a password
and instead provide a private_key_path
and, optionally, a private_key_passphrase
in your target. Note: Versions of dbt before 0.16.0 required that private keys were encrypted and a private_key_passphrase
was provided. This behavior was changed in dbt v0.16.0.
Starting from dbt v1.5.0, you have the option to use a private_key
string instead of a private_key_path
. The private_key
string should be in Base64-encoded DER format, representing the key bytes. Refer to Snowflake documentation for more info on how they generate the key.
my-snowflake-db:
target: dev
outputs:
dev:
type: snowflake
account: [account id]
user: [username]
role: [user role]
# Keypair config
private_key_path: [path/to/private.key]
# or private_key instead of private_key_path
private_key_passphrase: [passphrase for the private key, if key is encrypted]
database: [database name]
warehouse: [warehouse name]
schema: [dbt schema]
threads: [1 or more]
client_session_keep_alive: False
query_tag: [anything]
# optional
connect_retries: 0 # default 0
connect_timeout: 10 # default: 10
retry_on_database_errors: False # default: false
retry_all: False # default: false
reuse_connections: False # default: false
SSO Authentication
To use SSO authentication for Snowflake, omit a password
and instead supply an authenticator
config to your target. authenticator
can be one of 'externalbrowser' or a valid Okta URL.
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. See the Snowflake docs for how to enable this feature in your account.
my-snowflake-db:
target: dev
outputs:
dev:
type: snowflake
account: [account id]
user: [username]
role: [user role]
# SSO config
authenticator: externalbrowser
database: [database name]
warehouse: [warehouse name]
schema: [dbt schema]
threads: [between 1 and 8]
client_session_keep_alive: False
query_tag: [anything]
# optional
connect_retries: 0 # default 0
connect_timeout: 10 # default: 10
retry_on_database_errors: False # default: false
retry_all: False # default: false
reuse_connections: False # default: false
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
Config | Required? | Description |
---|---|---|
account | Yes | The account to connect to as per Snowflake's documentation. See notes below |
user | Yes | The user to log in as |
database | Yes | The database that dbt should create models in |
warehouse | Yes | The warehouse to use when building models |
schema | Yes | The schema to build models into by default. Can be overridden with custom schemas |
role | No (but recommended) | The role to assume when running queries as the specified user. |
client_session_keep_alive | No | If 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) |
threads | No | The number of concurrent models dbt should build. Set this to a higher number if using a bigger warehouse. Default=1 |
query_tag | No | A value with which to tag all queries, for later searching in QUERY_HISTORY view |
retry_all | No | A boolean flag indicating whether to retry on all Snowflake connector errors |
retry_on_database_errors | No | A boolean flag indicating whether to retry after encountering errors of type snowflake.connector.errors.DatabaseError |
connect_retries | No | The number of times to retry after an unsuccessful connection |
connect_timeout | No | The number of seconds to sleep between failed connection retries |
reuse_connections | No | A 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" and "Account Identifier 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.
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.