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
.