DBT Snowflake

Setting Up dbt-Core for Snowflake: A Step-by-Step Guide

by frankruiter@gmail.com

As you embark on your data transformation journey with dbt (Data Build Tool) Core, integrating it with Snowflake opens up possibilities for seamless data modeling and management in the cloud. In this guide, we’ll walk through each essential step to get dbt-Core up and running with Snowflake on your local environment.

Step 1: Install Python

Since dbt-Core relies on Python, start by installing Python 3.10.x. During installation, make sure to check the box to add Python to your PATH. This step ensures that Python commands are accessible in your command prompt or terminal.

To verify your Python installation, open a command prompt or terminal and type:

python --version

You should see the installed version of Python, confirming a successful installation.

Step 2: Create a Virtual Environment

A virtual environment provides an isolated space to manage project-specific dependencies. This is especially helpful in dbt projects, as it prevents conflicts with other Python projects on your system.

Setting Up the Virtual Environment

  1. Create a Project Folder: Choose a location on your file system to store your dbt project files. This will serve as your project’s workspace.
  2. Install virtualenv: Open a command prompt and install the virtualenv package:
pip install virtualenv

Create the Virtual Environment: Navigate to your project folder, then create the virtual environment:

virtualenv venv

This creates a venv folder with your isolated Python environment.

Activate the Virtual Environment: Each time you work on your dbt project, activate the virtual environment:

.\venv\Scripts\Activate  # Windows
source venv/bin/activate  # macOS/Linux
  1. After activation, (venv) should appear in your command prompt, indicating that you’re working in the virtual environment.

Step 3: Install dbt and dbt-Snowflake

Now that your virtual environment is set up, it’s time to install dbt-Core and the dbt-Snowflake adapter.

  1. Verify Virtual Environment Activation: Ensure (venv) is still active in your command prompt. If not, follow the activation steps above.
  2. Install dbt-Snowflake: With your virtual environment active, install the specific version of dbt-snowflake:
pip install dbt-snowflake==1.7.1

This ensures compatibility across projects by specifying a stable dbt-Snowflake version.

Verify the Installation: Confirm that dbt and dbt-Snowflake are correctly installed by checking the version:

dbt --version
  1. You should see version information for both dbt Core and the dbt-Snowflake adapter, indicating a successful installation.

Step 4: Configure Your Connection Profile for Snowflake

To connect dbt to Snowflake, you need a profiles.yml file containing the connection settings.

  1. Create the Profile Directory: Run the following command to create a .dbt directory in your user profile:
mkdir %userprofile%\.dbt  # Windows
mkdir ~/.dbt  # macOS/Linux

Define Your Connection Settings: In the .dbt folder, create or edit the profiles.yml file to include Snowflake-specific credentials. The configuration typically includes:

  • type: Snowflake
  • account: Your Snowflake account URL, e.g., XXXX.west-europe.azure
  • user: Your Snowflake username
  • authentication: SSO
  • authenticator: externalbrowser (for browser-based authentication)
  • role, warehouse, and schema: Your Snowflake access settings
  • threads: 1 (adjustable based on your needs)

Step 5: Initialize Your dbt Project

  1. Run dbt init: With the .dbt directory ready, initialize a new dbt project by running:
dbt init MyProject
  1. This creates a folder named MyProject, with folders for models, analyses, tests, and documentation.
  2. Configure the Project: Inside the MyProject folder, open dbt_project.yml and ensure the profile entry matches the profile name in your profiles.yml file. This links the project to your Snowflake configuration.

Step 6: Test the Snowflake Connection

To confirm that your dbt project connects to Snowflake correctly, use the dbt debug command.

  1. Navigate to the Project Directory: Open a command prompt and go to your project’s folder.
  2. Run dbt debug: With the virtual environment active, run:
dbt debug

dbt will check your profiles.yml, Snowflake credentials, and project setup. If everything is correct, you’ll see a success message. Any issues will display specific errors to help you troubleshoot.

Congratulations! You’ve successfully set up dbt-Core for Snowflake. You’re now ready to develop and execute models directly on Snowflake using dbt.