zaterdag 16 december 2023

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


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.

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:

    SELECT * 

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.


Geen opmerkingen:

Een reactie posten