A slowly changing dimension is a common occurrence in data warehousing. In general, this applies to any case where an attribute for a dimension record varies over time.
There are three typical solutions for this. In the first, or type 1, the new record replaces the old record and history is lost. The second, or type 2, a new record is added into the customer dimension table and the customer is treated essentially as two people. The third, type 3, the original record is updated to reflect the change. It is considered and implemented as one of the most critical ETL task in tracking the history of data belonging in the dimension.
The advantage of a type 2 solution is the ability to accurately retain all historical information in the data warehouse.
This blog will focus on how to create a basic type 2 slowly changing dimension with an effective date range in Informatica.