Slowly Changing Dimension (SCD) Type 2

This diagram shows how a slowly changing dimension type 2 table is implemented.

customer_number is the business key of the customer table while customer_id is a surrogate key. Customer 10001 is changing first_name from Kontext to Context.

SCD Type 2 will track history of changes. It is usually implemented to use effective_date to indicate the start date when the change becomes effective and is_current flag to indicate the current active records.

It can also be implemented using effective_from_date and effective_to_date. When effective_to_date is NULL or equals to a high date like (9999-12-31), the record is the latest version.

For performance and other considerations, it can also be implemented with a combination of effective_from_date, effective_to_date and is_current.