Does it make sense to have different SCD types for different columns at single dimension? Or it's always better to split a dimension table into two tables?
For example, the operational systems sends me the following data:
ID | CHANNEL_CODE | NAME | TYPE
1 | A | X | 0
2 | B | Y | 1
Where CHANNEL_CODE
= natural key, TYPE
should be SCD type 2, while NAME
could be SCD type 1. Let say NAME
is frequently changed.
I always keep Type 0, 1 and 2 in one table.
I also treat Type 0-s and Type 1-s the same, and Type 2-s the other way.
With this type of staging data:
... my SCD table looks like so: