Skip to content

DBT Development and Production:

In this tutorial, we will guide you through setting up dbt Cloud with Snowflake. Along the way, we'll demonstrate how to configure both your development and production environments.

Video

Requirement

Snowflake

First lets start in Snowflake by setting up some resources. We'll create one database, two warehouses and one new users using sysadmin role.

If your user is using MFA - Please enable token caching before uploading

If your planned development user is using MFA. Please enable token caching or else it will ask you for authentication everytime you run anything in dbt.

alter account set allow_client_mfa_caching = TRUE;

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
use role sysadmin;

-- Create a database to store our schemas.
create database if not exists dbt;

-- We'll remove the default schema to keep things clean.
drop schema dbt.public;

/*
    Warehouses are synonymous with the idea of compute
    resources in other systems.
*/
create warehouse if not exists development 
    warehouse_size = xsmall
    auto_suspend = 30
    initially_suspended = true;

create warehouse if not exists production 
    warehouse_size = xsmall
    auto_suspend = 30
    initially_suspended = true;

-- User/login used during our production prcoess.
use role accountadmin;

create user service_dbt password = '<USER PASSWORD>';

-- Lets keep the RBAC simple and use sysadmin for everything. 
grant role sysadmin to user service_dbt;

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
use role sysadmin;

-- Create a database to store our schemas.
create database if not exists dbt;

-- We'll remove the default schema to keep things clean.
drop schema dbt.public;

/*
    Warehouses are synonymous with the idea of compute
    resources in other systems.
*/
create warehouse if not exists development 
    warehouse_size = xsmall
    auto_suspend = 30
    initially_suspended = true;

create warehouse if not exists production 
    warehouse_size = xsmall
    auto_suspend = 30
    initially_suspended = true;

-- User/login used during our production prcoess.
use role accountadmin;

create user service_dbt password = 'Password1234';

-- Lets keep the RBAC simple and use sysadmin for everything. 
grant role sysadmin to user service_dbt;

1
Statement executed successfully.

Development

Lets walk through the development environment setup in dbt.

Project

As soon on you login you'll be asked to start a project and give it a name. project

We'll want to add our development environment connection to Snowflake. add connection

Snowflake

To add our development environment we'll want to get our account identifier. Once copied we'll make sure we replace the . with a - otherwise it won't find the account. account identifier

We'll select snowflake and give the connection a name. For the connection settings you'll want to add your account identifier as the account. Please remember to swap . with a - otherwise it won't find the account. For the database we'll use DBT, warehouse = development and role = SYSADMIN. Connection setup

Once saved, dbt will not redirect you to the setup so please click Credentials and then the project. This will prompt you to click a link that will take you back to the setup page. Back to setup

Select your connection. select connection

If your user is using MFA

If your planned development user is using MFA. This connection test will ask for MFA authentication.

Now we'll want to enter our own development credentials and then select test connection to validate everything. dev credentials

Once tested you will see a complete status. From there we'll click save. testing dev creds

Git

Next we'll want to add our git reposity. If you have not created one yet, this would be a great time before we connect to it. I named mine dbt. select git

Once you authenticate with your git provider, a list of git repos will show. Select the one you want to use. select repo

Run first models

Great our development environment connection is setup. Lets build our first models and see them in Snowflake. start dev

Lets initialize the project code. initialize project

We can see a sample project has been added to our branch. show code

Before we build these models lets remove some code so that everything builds successfully. DBT has setup up this code for learning but we are more intrested in getting everything working. Remove code

If your user is using MFA - Please enable token caching before uploading

If your development user is using MFA. Please enable token caching or else it will ask you for authentication everytime you run each dbt model. In this case will we ask at least 6-7 times.

alter account set allow_client_mfa_caching = TRUE;

Now lets build our code by going to the bottom of the page and entering dbt build and hitting enter. This will build all our models. build

We can see now that all our current branches models have built successfully. look at build

We can view those built models in our Snowflake account. Models in Snowflake.

Finally we will commit those changes to our branch. commit to branch

Production

Great now that we have our development environment setup we can start adding new users and all they will have to do is add thier development credientials. Lets setup our product environment that will curate our models on a regular bases based on our main branch.

Environment

Lets start by committing our changes to the branch. commit

Once commit DBT will give us the option to create a pull request. Lets commit our branch to main branch. start pull request

Click create pull request. click create PR

Give the PR a description and click "create pull request". create the PR

After it's created lets merge the code changes. merge

Confirm the merge. confirm merge

After it's merged lets delete the branch to keep our git clean. delete branch

Next lets go back to DBT to setup the environment. select environment

Lets create a new environment. new environment

Lets give our environment a name, select deplyment type of production and connection of Snowflake we created earlier. environment setup 1

Lets add our connection variables. The image shows what everything should be if you used our setup code. The password comes from that setup code. If your want to use key-pair instead of username/password, check our key-pair tutorial. environment setup 2

Once finished head back to the top and click save. save

Job

Jobs run dbt code in our environment on a regular bases. Lets use the left navbar to go to deploy and then jobs. navigate jobs

Create a new deploy job. create job

We'll give our job a name. The environment should be production and we want to run the command dbt build similar to when we were in development. Next we'll select to generate docs and run on a schedule. Click save when finished. job setup

Now that we created the job, lets run it. Run job

We can see the job is now running, lets click in and see what it's doing. view running job

You can see the steps dbt will take to run this job. It may take 20 seconds for it to finish. job complete

Result

Once the job is completed we can go to Snowflake to see our production jobs result in our production schema. Snowflake results

Now we can go back to the dashboard to see all the metadata of our job and run history. dashboard

Documentation

Now that our production job has run lets see our documentation. This has a little setup to know which job the docs should look at when displaying results.

Lets start by going to account settings in the bottom left of the page. account settings

We'll go to project, select the project and finally edit the project. navigate to edit project

We'll want to update the description, and the job we want to associate the documentation to. Finally click save. Update to selected job

Great, lets click documentation on the left side navbar. navigate to docs

Now we can see our documentation. see documentation