Slowly Changing Dimension (SCD) Type 2

Kontext Kontext
visibility 541 access_time 2 years ago language English

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_dateeffective_to_date and is_current.

copyright This page is subject to Site terms.
comment Comments
No comments yet.

Please log in or register to comment.

account_circle Log in person_add Register

Log in with external accounts

tag Tags
info Info
Image URL