Type 0
Ignore any changes and audit the changes.
Type 1
This type will overwrite data, if the source of dimension value changed. It doesn’t require preserve the data This type should be used, when We need to preserve history of fact table and dimension.
Type 2
This type will insert a new row, If the source of dimension value change. This type ensure that the history of fact table preserved.
Example
To understand how the type 2 change preserves history, look at the fact table in the lower half of Figure 3-8. After the type 2 change has occurred, the order placed before Sue moved remains associated with the “old” version of Sue. You can use the foreign key value in the fact table to verify this. Tracing customer_key 1499 to the customer table leads to a row that identifies customer 9900011 as Sue Johnson, living in Arizona. The fact table now contains a new row, this one for an order placed after the move. The new row carries the surrogate key 2507, which refers to the “new” version of Sue, living in California. Any reports that group orders by state will continue to group Sue’s old order with “AZ,” while her new order will be grouped with “CA.”
We should add a flag to avoid the end user query will look duplicate data.
INFO
Type 2 changes preserve the dimensional detail surrounding facts. They may confuse users, however, by appearing to duplicate information in dimension tables. Avoid this confusion by issuing browse queries that select distinct values, and by offering a flag to indicate whether each row represents the current version for its natural key value
Type 3
History will be added as a new column. Typically, this would be better suited to implement name changes of an employee. In some cases, female employees will change their names after their marriage. In such situations, you can use Type 3 SCD since these types of changes will not occur rapidly.
Type 4
A new dimension will be added. We maintain the history by adding a different version of the row to the dimension. However, if the changes are rapid in nature Type 2 SCD will not be scalable. In this technique, a rapidly changing column is moved out of the dimension and is moved to a new dimension table. This new dimension is linked to the fact table as shown in the below diagram.
Type 6
Combination of Type 2 and Type 3. Add column to mark current value.