How do you handle updates or changes to Dimension attributes that are already linked to historical Fact table records?
or How to handle (Slowly Changing Dimensions)
Answer:
We handle this using Slowly Changing Dimension (SCD) Type 2.
- If a customer changes their home address or risk category,
- we do not overwrite the old row in Dim_Customer.
- Instead, we expire the old row by setting an end-date and status flag,
- and we insert a brand-new row with the updated details and a new surrogate key.
- This way, past claims and policy sales linked to that customer will always point to the customer's address and details as they looked at the time the transaction happened.
