Creating a Local Development Environment for dbt Core and Snowflake in 5 minutes

I have been using dbt Core a ton lately and it has really grown on me. In this article, I will show you how I have been setting up local development environments for dbt Core and snowflake in just 5 minutes. So, let’s get started.

For this example I will be using a sample project provided by dbt Labs called jaffle_shop, which is a project that transforms and loads data for a fake e-commerce store. The reason that I chose jaffle_shop for this article is that it uses seeds to load the data, so everyone can follow along without having to load raw source data to a warehouse. If you are unfamiliar with seeds, they are basically just .csv files that are mounted within your project. This is one use for seeds, but they can be really helpful for a lot of different use cases. Two that I find myself using pretty often are: when I need to quickly write a static lookup table to my database, and when I am working on a model but need to wait for someone upstream to do something like set up a connector, write an airflow dag, etc. Instead of waiting for the upstream task to be completed, I can load a sample of the table as a .csv to my seeds directory and start working on my model while the upstream task is being completed.

Anyways, back to this project. If you are unfamiliar with jaffle_shop and want to take a look over it before starting, you can view the repo right here:

GitHub - dbt-labs/jaffle_shop: A self-contained dbt project for testing purposes

The first thing that we need to do is download anaconda and start a new virtual environment for this project, then download the dbt Core snowflake package in this environment. We can do this with the following commands:

pip install conda 
conda create -n dbt_env python=3.9.12
conda activate dbt_env
pip install dbt-snowflake

Next, we need to open up snowflake and get the data warehouse ready for incoming data. If you do not already have an instance running, you can start a free trial to follow along right here. First we need to create a database, warehouse, role and user:

create database analytics;

create warehouse transform_wh
    warehouse_size = xsmall
    auto_suspend = 60
    auto_resume = true
    initially_suspended = true;

create role transform_role;

create user dbt_user
    password = 'generate_this'
    default_warehouse = transform_wh
    default_role = transfor_role;

Next we need to grant permissions on our new role and assign it to our new user. To do this, run each of these queries in your worksheet:

grant role transform_role to user dbt_user;
grant all on warehouse transform_wh to role transform_role;
grant select on all tables in database analytics to role transform_role;
grant select on all views in database analytics to role transform_role;
grant create schema on database analytics to role transform_role;
grant usage on database analytics to role transform_role;
grant usage on future schemas in database analytics to role transform_role;
grant select on future tables in database analytics to role transform_role;
grant select on future views in database analytics to role transform_role;

Okay, now that Snowflake is ready to go we need to switch back to the terminal window we were working in earlier. First thing we need to do is setup our profiles.yml file. Run this top command, then open the file location that it returns:

dbt debug --config-dir
open /Users/eric/.dbt

Once you have opened the file, paste in this profile and update it with your Snowflake information:

jaffle_shop:
  target: dev
  outputs:
    dev:
      type: snowflake
      account: xyz123.west-us-2.azure
      user: dbt_user
      password: YourPassword
      role: TRANSFORM_ROLE
      database: analytics
      warehouse: TRANSFORM_WH
      schema: dbt
      threads: 1
      client_session_keep_alive: False

Save this file, and head back to your terminal. Next, we need to clone the github repo for jaffle_shop and change directory to its location:

git clone https://github.com/dbt-labs/jaffle_shop.git
cd your/path/to/this/repo

Lastly, we can run our project. Since this project pulls data from seeds we first need to seed the project, then run using these commands:

dbt seed
dbt run

After running, you should see a successful run in your terminal like this:

Great, you are now setup to develop locally with dbt Core using the ide of your choice. You can either start working from this example project, or start a new project with this command:

dbt init new_project_name

Thanks for reading.