In this post I’m going to cover the different types of Slowly Changing Dimensions (SCD).
What is a Slowly Changing Dimension?
In a Kimball structured star schema you use Slowly Changing Dimensions to associate facts with their original value. This is because sometimes associating a fact with it’s changed dimensional value will look odd.
For example let’s imagine we have a wages fact table and an employee dimension. What happens to the wages paid to an employee when they move department? In your eventual report this will look wrong, but if you associate the employees department at the time the wage was paid you can report accurately on how much was paid by department.
Type 0 – Never Changing
A type 0 SCD is a table that never changes, for example a date table.
Type 1 – Overwrite / No History
A type 1 SCD is where the data is overwritten when changed, inserted when new, and deleted when removed. No history of the data changing is retained.
Type 2 – Add a New Row
In a type 2 changes to rows are inserted as new rows. The old row is marked as no longer the most current. In this scenario you will add meta data columns to your table to track the date and time the row was valid from and to. I like to also add another boolean flag to mark whether the row is current or not.
I’ve covered how to do this using T-SQL in a previous post.
Type 3 – Original and Current Columns
With a type 3 you retain the original value in one column and write the new value into a current column. You might also add a timestamp for the current value.
Type 4 – History Table
A type 4 is where you have two tables. One table contains the current record and the other table contains the history of changes.
Type 5 – ?
There is no agreed solution for 5. Either that or someone was just having a little fun getting to Type 6.
Type 6 – 1 + 2 + 3 = 6
Type 6 is a combination of Types 1, 2, and 3. I guess this is because things aren’t complicated enough, but essentially this has the benefit of quickly seeing the old and new value whilst also maintaining history.