Have or not to have?--That is the question.?? Demystifying SCD dimensions in a short and direct way!
Don't worry, this isn't exactly a Shakespearean dilemma ??
In a Data Warehouse project, we always come across creating dimensions SCD1 (without history), SCD2 (with history in different rows), etc.
Just to refresh your memory, I'll give you a classic and simple example:
Suppose a model with a Sales fact table and a Customer dimension.
Suppose a model with a Sales fact table and a Customer dimension. In a common situation, let's say that the customer "Contoso, ID 130039" was from Department A and on 01/14/2025 moved to department B.
We have several ways to deal with this, but I'll simplify it into 3 ways (If you're anxious and want my opinion, skip to item 3):
1. SCD Type1: Without history
In a classic SCD Type1 approach, we don't keep history and only keep the latest version of the attributes in the customer dimension.
2. SCD Type2: Keep history of everything, creating surrogate Key (SK) in each dimension, every time a column/attribute changes
3. History in facts (where we should always keep it)
In a nutsheel, we must be very careful when maintaining history in dimensions.
If you really need to, always try to break it down into a min-dimension and use the history in the fact.
If you are going to implement SCD type2, be aware that the model and reports will be more complex to maintain.
It was easy to see that the latter approach is the most coherent.