Dbt to Snowflake Connection Failure via Profiles.yml

  • How-Tos FAQs
  • March 16, 2021

Let’s explore the Dbt to Snowflake connection failure via Profiles.yml. When getting started developing with dbt CLI, you will need to configure a profiles.yml file to develop locally. This locally stored configuration file stores database connection profiles. This file is a virtual keychain with each key being a profile that grants access to a target. Many profiles can be stored in this file.

Important: A profiles.yml file contains sensitive information that could be used to access the target data warehouses within this file. A profiles.yml file should never be shared with anyone. It should be stored outside of dbt projects and should not be included in a git repository. A profiles.yml file should remain secured so that the owner of the file is the only person that can access the file.

DBT Profiles

A profiles.yml file is simply a text file storing one or more “profiles”. A profile contains database connections (including credentials) to connect to a data warehouse and one or more targets within the database.

A profiles.yml file allows dbt developers to keep credentials for all the dbt projects they work on in one file. It also allows individual developers to use different targets for development so that each developer has their own space to develop.

DBT Targets

A dbt target for Snowflake is the specific database, schema, and warehouse in Snowflake that will be used to run dbt models and where analytics tables and views will be written to.

If multiple developers are working on the same dbt project, each of them will most likely have a different profile for that project. In addition to different credentials, each developer should have a different target, so that each developer has their own schema to develop in.

DBT Projects and DBT Profiles

Each dbt project references a profile within that dbt project’s dbt_project.yml file. When a dbt project is initialized on the command line, dbt will check for profiles.yml in the default location ~\.dbt\. If there is a profile in profiles.yml with a name that matches the profile configured in dbt_project.yml, that profile will be used.

Common Issues and Failure Reasons

Data warehouse adapter is not installed

DBT requires an adapter for each type of database technology it supports. To use Snowflake with dbt, the Snowflake adapter needs to be installed along with dbt core. Adapters are a separate installation from dbt core. If you have not installed the Snowflake dbt adapter Snowflake profiles will fail.

Profiles.yml in the wrong location

DBT looks for profiles.yml in ~\.dbt\. If profiles.yml is in a different location, DBT needs to be told where the file is located using the –profiles-dir flag.

Profile name doesn’t match DBT Project profile

The profile name in profiles.yml needs to match the profile specified in a dbt projects dbt_project.yml file. By default, dbt projects have a profile name that matches the name of the project in dbt_project.yml.

The last line in this dbt_project.yml file:

# Name your project! Project names should contain only lowercase characters 
# and underscores. A good package name should reflect your organization's 
# name or the intended use of these models
name: 'cli_test'
version: '1.0.0'
config-version: 2
# This setting configures which "profile" dbt uses for this project.
profile: 'cli_test'

Needs to match the profile name in profiles.yml:

cli_test:  #### profile name
  target: dev_[username]
      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

Debug your profile

To debug a profile, use dbt debug on the command line. This will validate profiles.yml and return any errors if there are issues.

That’s about it for setting up and debugging Dbt to Snowflake connection failures stemming from profiles.yml!

Continue Reading

Check out: Querying with dbt from an external source in Snowflake