How to Create Slowly Changing Dimensions with dbt Snapshots

A tutorial on the different types of SCDs and configuring a snapshot

As a new analytics engineer, I had no idea what nuances to look for in the available data. Rather than starting with the source data, I explored the data models that our reporting and dashboards were built on.

I based my understanding of the data on business logic that was poorly written and wrongly coded. A rookie mistake.

Months into that role, I finally began digging into the source data, realizing that many of the models built out would not help us achieve what we wanted.

This is when I discovered that some of our data tables were type 1 slowly changing dimensions and others were type 4 slowly changing dimensions, adding to the confusion.

The historical data models didn’t consider these differences because whoever wrote them didn’t understand how changes in the values were being handled.

Now, as an analytics engineer with a few years of experience under my belt, I know to take the time to explore my data to understand how changes are being tracked, before embarking on data modeling.

Understanding the types of slowly changing dimensions that make up your tables will allow you to prepare ahead, ensuring all changes are tracked as needed.

What are slowly changing dimensions?

A slowly changing dimension is a data record whose values change slowly over time.

A customer’s account data like email, address, and phone number is an example of a slowly changing dimension. The customer’s address won’t change every day, but it may change every two years.

The type of slowly changing dimension depends on how the changes are tracked in your database.

For example, Type 1 Slowly Changing Dimensions overwrite old values in your data. The new value will replace the old value, without any record of what the old value was. I find this is the method used in most databases.