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_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