You are designing a dimension table for a data warehouse. The table will track the value of the dimension attributes over time and preserve the…

Type 2 - Creating a new additional record. In this methodology all history of dimension changes is

kept in the database. You capture attribute change by adding a new row with a new surrogate key to

the dimension table. Both the prior and new rows contain as attributes the natural key(or other

durable identifier). Also 'effective date' and 'current indicator' columns are used in this method.

There could be only one record with current indicator set to 'Y'. For 'effective date' columns, i.e.

start_date and end_date, the end_date for current record usually is set to value 9999-12-31.

Introducing changes to the dimensional model in type 2 could be very expensive database operation

so it is not recommended to use it in dimensions where a new attribute could be added in the future.

https://www.datawarehouse4u.info/SCD-Slowly-Changing-Dimensions.html