7 (or 8) Types of Slowly Changing Dimensions:

Here are several approaches to doing slowly changing dimensions for your next data project:

What is a Slowly Changing Dimension?

A Slowly Changing Dimension (SCD) is a concept used in data warehousing to manage and track changes over time in dimensional data. Dimension data aka descriptive information like product details, customer information, or geographic locations, sometimes changes slowly rather than on a regular, predictable schedule. The SCD approach enables a database to accurately reflect and keep a history of these changes without losing the context of historical data.

What are examples of each of the slowly changing dimension? 

Which slowly changing dimension is best?

Of course the answer - is it depends, but I tend to like 7 for a lot of use cases with modern databases like Snowflake and Big Query.

Here is an example of #7 Imagine you had these three entries:

The client HQ moved 3 times between 2000 and and 2010 and is currently in CA. Note - 2099 is just a date far in the future to help with joins later.

Notice the client key changes each time (it stays unique) but the client code and names stays the same. Also notice the start and end date to indicate the timing that record was valid as well as the current_flag to indicate the currently accurate record.

‍

Here is some example SQL to the current record:

SELECT
 sale.sale_amount,
 client.client_name,
 client.client_state
FROM sale
INNER JOIN client
 ON sale.client_code = client.client_code
WHERE client.current_flag = 'Y';

Notice the use of the handy current_flag!

‍

Here is a example of getting all the records (not all that useful):

SELECT
 sale.sale_amount,
 client.client_name,
 client.client_state
FROM sale
INNER JOIN client
 ON sale.client_key = client.client_key;

‍

Here is an example of getting the records you want for when they were active ( you could pull in client state by date the sale was made for example)

SELECT
 sale.sale_amount,
 client.client_name,
 client.client_state
FROM sale
INNER JOIN client
 ON sale.client_code = client.client_code
 AND sale.sale_date BETWEEN client.Start_Date AND client.End_Date;

‍

There you have it! 7 Slowly changing dimensions (although number 0-1 barley count).

This wikipedia article was the inspiration for this post and has even more info it!

‍

Thanks for reading!

‍