Explain Slowly Changing Dimension(SCD) types?

SCD, is a technique used to store the historical value of dimension attributes.

The values of dimensional attributes change as time goes by. When these attribute values change, you can overwrite the old values with the new ones, or you can
preserve the old value. There are two methods of preserving the old attribute values: you can store the old values as rows, or you can store them as columns

three types of SCD:
• SCD type 1 overwrites the old values of the attributes so the old values are not kept.
Ex: In product dimension,we will use SCD type 1, meaning we will overwrite the old attribute values

• SCD type 2 keeps the old values by creating a new row for each change, just like
Table 5-6.
Ex: Customer dimention

• SCD type 3 keeps the old values by putting them in another column, just like Table 5-7.

Ex: store dimension is an ideal case for SCD type 3

SCD type 2 is more flexible for storing the historical value of dimensional
attributes, because you can store as many old versions as you want without altering the table structure. SCD type 3 is using columns to store old values, which is why it is not flexible. It is ideal for situations where you don’t have many old versions (five or fewer) and you know there will be only a certain number of versions. Type 3 is also ideal when the changes in this attribute
affect a large number of rows. In other words, a lot of dimension rows change the value of this attribute at the same time (simultaneously)