In this blog post, we will explore how to use dbt in conjunction with Snowflake. We will set up accounts for dbt and Snowflake and link the two using the dbt cloud UI, Snowflake's Partner connect, and the command line interface.

What does dbt do?

dbt (data build tool) is an open-source data transformation tool that allows you to execute SQL transformations on raw data to produce clean, analytical-ready datasets. dbt uses a modular approach to organize SQL transformations, enabling you to manage, test, and deploy code more effectively.

By connecting dbt to Snowflake, you can leverage Snowflake's scalable, cloud-based data warehouse to store and process your transformed data.

Source: dbt documentation

Connecting dbt to Snowflake

Let's walk through the steps to integrate dbt with Snowflake.

Prerequisites

To start things off, you must set up both dbt and Snowflake.

1. Have a Snowflake account.
2. Have a dbt cloud account if you are connecting via the dbt Cloud.
3. Have the dbt CLI installed if you are connecting via the CLI.

Connecting dbt to Snowflake

Using the Snowflake Partner Connect

The easiest way to connect dbt to Snowflake is by using Snowflake’s partner connect option.

1. Access the partner connect tab under Admin -> Partner Connect and search “dbt” in the search bar.

Snowflake Partner Connect

1. Click on the dbt card, add additional grants for your test database and click connect.

Connect to dbt

1. Click Activate on the next prompt.
2. You will be taken to the dbt cloud login page. If you do not have a dbt cloud account, you will be prompted to create one. Otherwise, you can simply log in using your existing Account Name.
3. On the dbt webpage, go to Account Settings -> Partner Connection Trial.
4. Click on “Snowflake” under *Connection to view the connection details.

1. Edit Warehouse and Database details to ‘TEST_DBT_WAREHOUSE’ and ‘TEST_DBT_DATABASE’.

1. The connection has been established, and you are ready to create your data models.

Using the dbt Cloud

To connect dbt to Snowflake via dbt Cloud, follow these steps:

1. Log in to your dbt Cloud account and create a new project. Give your project an appropriate name

Setup a new project

1. In the project settings, click “next” to navigate to the "Connection" tab.
2. Choose "Snowflake" as your data warehouse.

Choose Snowflake as a data source

1. Fill in the necessary connection information, including account, user, password, role, database, schema, and warehouse.

Insert Credentials

1. Scroll to the bottom and click on “Test Connection” to ensure it's working correctly.

Test Connection

1. Configure additional settings, such as query tags or custom connection parameters, as needed and attach a git repository to work with the project.

Connect Repository

And just like that, you have linked dbt cloud to Snowflake. You can now manage all your models and runs on the project dashboard.

Using the Command Line Interface (CLI)

You will need dbt-core and dbt-snowflake installed using PIP or Homebrew. Once you have them installed, you can continue the connection setup using the command line.

Make sure to create a role TRANSFORMER in Snowflake for dbt.

Initialize a project using the command dbt init <project_name>. The following prompts will ask you to input the authentication parameters described below.

1. Database Adapter: Snowflake
2. Account Name: <Your Account Name>
3. Username: <Your Username>
4. Password: <Your Password>
5. Role: ACCOUNTADMIN
6. Warehouse: DBT_TEST_WAREHOUSE
7. Database: DBT_TEST_DATABASE
8. Schema: PUBLIC

This will create a ‘profiles.yml’ file. This file will be under the different paths on different operating systems.

Linux: <span class="code-exp">~/.dbt/profiles.yml</span>

Windows: <span class="code-exp">c:/users/<your_user>/.dbt/profiles.yml</span>

MacOS: <span class="code-exp">~/.dbt/profiles.yml</span>

If you wish to edit any of your authentication details, you can edit them in this YAML file.

dbt_project:
  outputs:
    dev:
      account: UJ12275.central-india.azure
      database: TEST_DBT_DATABASE
      password: 
      role: ACCOUNTADMIN
      schema: PUBLIC
      threads: 1
      type: snowflake
      user: 
      warehouse: TEST_DBT_WAREHOUSE
  target: dev

Moreover, instead of using a password, you can also authenticate using a key/pair authentication file and an SSO authenticator configuration file.

Once all the details are in place, you can use the dbt debug command in the terminal to test the connection.

run  <span class="code-exp">dbt debug</span>

And with that, our connection is established and ready for data modeling.

Troubleshooting

While going through the setup procedure, you may face the following common errors.

‘dbt’ command is not recognized

This error occurs when your operating system can not find the installed dbt package. The easy fix is to create a virtual environment and install all dbt-related packages in the isolated environment.

dbt_project.yml not found

By default, dbt looks for the dbt_project.yml file to load project configurations. If it cannot find the required file, you are likely not inside the project directory. Simply move into the project directory where dbt expects the .yml file, and all will be good.

Username or Password Incorrect

Make sure that the User and Password entered in the profiles.yml file are the ones created for Snowflake, not dbt.

1. Review dbt logs for any errors and warnings

Final Thoughts

Connecting dbt and Snowflake is a crucial step in streamlining your data transformation and analytics workflows. By integrating these tools, you can efficiently manage, test, and deploy SQL transformations while leveraging Snowflake's scalable, cloud-based data warehouse. This enables your team to access clean, transformed data for insightful reporting and decision-making.

Further reading

• How to build a Snowflake API
• Introducing the new Metric Report API: Powerful reports for any app with a single GraphQL request‍
• How to build in-product analytics with Snowflake and GraphQL

If you don’t have a Propel account yet, you can try Propel for free and start building data apps.