Implement SCD type 2 in data build tool (dbt) in 10 mins | Tutorial for beginners

In our next video in this dbt series we look at how easy it is to implement a Type 2 SCD in dbt on top of Snowflake.

In this series I'll be producing a series of short videos which are based are dbt's excellent, free training modules found here:

❄️Want to SUPERCHARGE your career and become an EXPERT in Snowflake??❄️

Mastering Snowflake is accepting applications now to work with us in a small group. Serious inquiries only pls.

❄️Order my book: Mastering Snowflake Solutions HERE: ❄️

❄️Get my Free SnowPro core guide HERE: ❄️

❄️Become a student on my course: ❄️

❄️Connect with me on Linkedin:❄️

[EricLealos]

Normally I think of orders as a fact, and the changing status might be modelled as an accumulating snapshot. This makes querying duration in status very easy for reporting and BI tools. The source you have is a common format for an order history table. You may have just selected this because it was a clear example for this dbt functionality, but it is not a good example for dimensional modelling. Usually with type 2 scd's we are examining certain attributes of a dimension (Customer, product, sales channel for example could be dimensions that apply to orders) to see if they changed and making decisions to capture that change if it is important. Status is not really an attribute of an order dimension, status is a dimension related to an order fact.

[swathicastlehouse]

Hi adam, I have one query facing in my development, there is a duplicate records in snowflake database and when running snapshot in dbt getting error as duplicate records how to overcome that error while running dbt snapshot for model, please revert it help

[FrancisAtoyebi-ephv]

HI Adam,

So I am trying to create an SCD from a table that already contains changes say for example you have on row with the order status as shipped for order AAA and another for the same order where the status is delivered. When I use your method, on the second run, I get the error that I can only update one row per time, how can I create an SCD from this type of table using dbt.

[keepup]

How can we achieve a type 2 SCD in dbt if the data source has a historical data? The historical data source has records for each day.

[pothireddyramanareddy]

you mentioned about course structure which contains 10 modules, how to access that? can you please share ?

[manishramrakhiani]

Thanks Adam.
Would be nice if you could mention how easily the complexity arises when you are to actually join one more such tables to build a dimension.
Will be interesting to hear your thoughts on how you solve that part 🙏

[gopalkrishna-bhgr]

Adam can you please tell me about how to pull the data from mysql and load into snowflake usig dbt tool