Build a data warehouse with DBT and Snowflake on AdventureWorksLT (PART I)

Introduction

In this blogpost, I want to show you a couple of things that you can do with DBT and Snowflake. I've already installed and setup DBT for Snowflake. For the purpose of this blogpost, I've created a AdventureWorksLT database in Snowflake. You can read about this database in the blogpost: "AdventureWorksLT on Snowflake". 

So, in this blogpost I'll describe how to build a simple DBT flow with DBT based on Snowflake with the AdventureWorksLT database. This is a first blogpost in a series of blogpost about DBT and Snowflake.

This blogpost is a blogpost in a serie of blogposts :

• Build a data warehouse with DBT and Snowflake on AdventureWorksLT (PART I)
• Build Staging and Dimensions in DBT for Snowflake (PART II)
• Sources in DBT for Snowflake (PART III)
• Data freshness for DBT in snowflake (Part IV)
• Snapshots in DBT for Snowflake (Part V)
• Generic tests in DBT for Snowflake (PART VI)
• Singular tests in DBT for Snowflake (Part VII)
• Add packages to your DBT project (Part VIII)
• Adding documentation to your DBT project (Part IX)
• Working with hooks in DBT  (Part X)
• Ephemeral tables in DBT (Part XI)

Design of the DBT flow

The AdventureWorksLT database contains a couple of tables (not as much as the normal AdventureWorks database) and I picked a couple of interesting tables for this demo: Customer, Product, ProductCategory, ProductModel, SalesOrderDetail and SalesOrderHeader. Then, I want to stage them into stage views in the Staging layer. The purpose of the staging layer is for some transformations that is sometimes necessary, like conversions or renaming. The last step is to get the data into the dimensions and facts.

And, don't forget to create a calendar dimension that contains the date information. I've already blogged about a way to do this in Snowflake in this blogpost : "Creating a Date dimension with the Generator function in Snowflake"

My first dbt query

Now, to finish this first blogpost with DBT on Snowflake based on the AdventureWorksLT database, I've created a first model : Stg_Customer. Just a simple CTE:

WITH CTE_STG_CUSTOMER AS (
    SELECT * 
    FROM ADVENTUREWORKSLT.SALESLT.CUSTOMER
)
SELECT 
     SUFFIX
    ,MIDDLENAME
    ,EMAILADDRESS
    ,COMPANYNAME
    ,NAMESTYLE
    ,PASSWORDHASH
    ,CUSTOMERID
    ,PHONE
    ,SALESPERSON
    ,TITLE
    ,FIRSTNAME
    ,LASTNAME
    ,MODIFIEDDATE
    ,ROWGUID
    ,PASSWORDSALT
FROM CTE_STG_CUSTOMER;

And lets test this model (that's is awkward name of a SQL Statement name in DBT) with an execute on Snowflake :

The next thing is to test the project in DBT. Open a Terminal and execute dbt run :

I receive an error because of ; at the end of the statement in stg_Customers.sql. When I remove the ; it will run, happily :

It's a bit confusing because when I execute scripts with Snowsql, then there should be a ; at the end of the statements. Anyway, let's go to Snowflake and check if there is a view called STG_Customers?

Final Thoughts

Well, this is the first of a series of blogposts about DBT, VSC and Snowflake based on the AdventureWorksLT database.

Hennie