There several types of dimensions which can be used in the data warehouse
Type 0 changing dimension:
It is not really slowly changing dimension: We just load the data and keep it as it is. A typical example of it would be a list of postcodes
Type 1 slowly changing dimension:
Overwrite the old value with the new value. This is very useful when dealing with corrections. For example, the dimensions hold a list of countries some of them we misspelt so we want to current it
We don’t care about history in this case because it was incorrect in the first place.
Type 2 slowly changing dimension:
Create a new record in the dimension with a new primary key. For example, when a customer changes the address we will create a new record. Sometimes some additional fields are populated as well such current record flag or effective date.
Type 3 slowly changing dimension:
Overwrite the old value with the new value, and add additional data to the table such as the effective date of the change.
This type of dimensions can be used for loads when the loan is fully paid.
Now let's have a look at practical ways of population dimension tables:
Dimension table Structure
Data is coming in as a huge text file, which holds orders together with customer details.
Customer details are duplicated so we have to deduplicate it first
Type 0 slowly changing dimension
Check if the record exist if not insert a new record
Type 1 slowly changing dimension
For every incoming record
Count number of records matching the key
Than if Record Count <> 0
Update Customers table
or
Insert new record
Note:
We might be performing unnecessary updates plus for large and wide tables it could be very slow. One of the ways of avoiding doing it is using CRC(MD5) for checks. If CRC is the same no changes were made to the record and therefore there is no need to update it
Type 2 slowly changing dimension
Deduplicate the data
Calculate Record CRC
If this CRC exist in the database discard the record
If not create a new record
Type 3 slowly changing dimension:
Deduplicate the data
Calculate Record CRC
If this CRC exist in the database then do nothing
If not update the record with new data
