Slowly Changing Dimension (SCD) Type 2

Kontext Kontext event 2022-07-23 visibility 842 comment 0
more_vert

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.

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
SVG URL