Skip to content
Contact us Go to Customer Portal
  • There are no suggestions because the search field is empty.

Snowflake dbt - Target environment

Applies to
Platform: Snowflake dbt

Prepare a Snowflake account and a dbt installation to run the dbt project that the Snowflake dbt generator creates. Do this once per environment, before the first run. Afterwards dbt debug connects from the artifacts folder to your account.

A Snowflake dbt project has no deployment scripts and no load control: dbt creates and loads the tables itself. The native Snowflake generators use the environment described in Snowflake - Target environment instead.

Prerequisites

  • A Snowflake account and a user for dbt that signs in with key-pair authentication. Password-only sign-ins are blocked by the Snowflake strong-authentication rules.
  • A target database. dbt creates the schemas in it, but not the database.
  • The source tables in a database and schema of the account. To create the source model objects from them, see Discover a Snowflake database.
  • Python with pip on the machine that runs dbt.
  • With Apply Sensitivity Level Tags on: Snowflake Enterprise Edition or higher. The macro that creates the tag fails on Standard Edition.

Prepare Snowflake

  1. Create the target database, for example DWH for all layers.
  2. Choose a warehouse for each layer, or one warehouse for all of them, for example COMPUTE_WH. You enter it in the profile and in dbt_project.yml.
  3. Give a role the privileges to create and load the objects, and grant the role to the dbt user. For example, the owner of the target database can create the schemas, tables and views:
GRANT OWNERSHIP ON DATABASE DWH TO ROLE <role>;
GRANT USAGE ON WAREHOUSE COMPUTE_WH TO ROLE <role>;
GRANT ROLE <role> TO USER <dbt user>;

Here <role> is the role of the dbt user and <dbt user> its user name. The role also needs read access to the source tables and usage on every warehouse that dbt_project.yml names. With Apply Sensitivity Level Tags on, the same role creates the tag and sets it on the columns; the privileges are listed in Apply Sensitivity Level Tags (Project).

Install dbt

  1. Run pip install dbt-snowflake. pip installs the adapter dbt-snowflake together with dbt Core.
  2. Run dbt --version. The output lists the version of Core and, under Plugins, the version of snowflake.

Verified with dbt Core 1.12.6 and dbt-snowflake 1.12.5. dbt 2 is not supported with Snowflake dbt.

Create the connection profile

  1. Open profiles.yml in the .dbt folder of your user profile, or create it there.
  2. Add a profile with type: snowflake and the values below. The Snowflake setup page of the dbt documentation lists the other authentication methods.
  3. Enter the profile name as profile in dbt_project.yml of the artifacts, together with the other values of dbt - Placeholder values.

The values of the profile:

  • account: the account identifier, for example <organization>-<account name>.
  • user and role: the dbt user and its role.
  • private_key_path and private_key_passphrase: the private key file of the user and its passphrase. Keep the passphrase out of the file, for example with env_var().
  • database: the target database.
  • warehouse: the warehouse of the connection. A layer whose +snowflake_warehouse in dbt_project.yml names another warehouse runs on that one.
  • schema: the first part of every target schema. dbt creates the models of a layer in the schema <profile schema>_<layer schema>, for example ANALYTICS_RAWVAULT.
  • threads: the number of models that dbt runs at the same time.

Example

A profile dwh for the target database DWH. The values in angle brackets are those of your account; SNOWFLAKE_KEY_PASSPHRASE is an environment variable that holds the passphrase:

dwh:
  target: dev
  outputs:
    dev:
      type: snowflake
      account: <account identifier>
      user: <dbt user>
      role: <role>
      private_key_path: <path to rsa_key.p8>
      private_key_passphrase: "{{ env_var('SNOWFLAKE_KEY_PASSPHRASE') }}"
      database: DWH
      warehouse: COMPUTE_WH
      schema: ANALYTICS
      threads: 4

With profile: 'dwh' and the layer schema rawvault in dbt_project.yml, dbt writes the Hub common_customer to DWH.ANALYTICS_RAWVAULT.RDV_HUB_COMMON_CUSTOMER. Snowflake stores the unquoted names in upper case.

Check the result

This check needs the downloaded artifacts with profile set, see dbt - Placeholder values. Open a terminal in the artifacts folder, the folder that contains dbt_project.yml, and run dbt debug. dbt checks profiles.yml, dbt_project.yml and the connection. The output ends with:

All checks passed!

Failure symptoms and recovery

dbt does not find the profile

Message: Could not find profile named '<profile name>'
Cause: profile in dbt_project.yml names a profile that profiles.yml does not contain. In downloaded artifacts it is <placeholder>.
Fix: Enter the name of your profile as profile in dbt_project.yml and run dbt debug again.

The tag cannot be created

Message: 001420 (22023): SQL compilation error: invalid property 'PROPAGATE' for 'TAG'
Cause: dbt run-operation create_governance_tags ran in an account with Standard Edition. The tag uses a parameter that needs Enterprise Edition or higher.
Fix: Run the project in an account with Enterprise Edition or higher, or switch Apply Sensitivity Level Tags off.

For failures in other steps, see Troubleshoot generation, deployment and load failures.

Related articles