Type-0: (retain original)
The Type 0 dimension attributes never change,
For Example: Date of Birth
Type 0 is usually applied to mostly date dimension attributes
Type-1: (Overwrite) i.e. FULL-REFRESH Mode
This method overwrites old with new data, and therefore does not track historical data,
when supplier_state changes, it will override the existing value w/o maintaing the history,
so its' easy to manage but losing the history
Type-2: (add new row)
This method tracks historical data by creating multiple records for a given natural key:
Approach-1:
Approach-2:
Approach-3:
Type-3: (add new attribute)
This method tracks changes using separate columns and preserves limited history.
Type-4: (add history table) i.e. FULL-REFRESH-KEEP-HISTORY Mode
The Type 4 method is usually referred to as using "history tables", where one table keeps the current data,
and an additional table is used to keep a record of some or all changes.
Type-5
combination of type-4 and type-1, hence it is named type-5
Type-6 (combined approach)
The Type 6 method combines the approaches of types 1, 2 and 3 (1 + 2 + 3 = 6)
Type-7 (hybrid)
This method places both the surrogate key and the natural key into the fact table.
We could apply the above types, to the attributes of the dimension tables.
and not on the fact tables, as fact tables are mostly static in nature.
Reference: