Slowly Changing Dimensions (SCDs) describe ways to manage changing descriptive attributes in a dimensional model. Type 1 and Type 2 are common patterns; the right choice depends on whether historical values are needed for analysis or audit.

Type 1 overwrites the current value

SCD Type 1 updates an existing dimension row in place. It is useful when the old value is not needed, such as correcting a misspelled label. Historical reports will then use the corrected value unless history is retained elsewhere.

Type 2 creates a new version

SCD Type 2 keeps history by closing the prior row and inserting a new version. Designs commonly store effective start and end timestamps plus a current flag. The business key identifies the entity; a separate surrogate key identifies each dimension version.

sql
UPDATE dim_customer
SET valid_to = :change_time,
    is_current = FALSE
WHERE customer_id = :customer_id
  AND is_current = TRUE;

INSERT INTO dim_customer (
  customer_key, customer_id, segment,
  valid_from, valid_to, is_current
) VALUES (
  :new_key, :customer_id, :new_segment,
  :change_time, NULL, TRUE
);

Choose with the business question

  • Use Type 1 when corrected/current attributes are sufficient and prior values do not need to be reported.
  • Use Type 2 when reports must answer what an attribute was at a historical point in time.
  • Interview prompt: explain how a fact joins to the correct dimension version for its event date.
  • Test that each business key has at most one current row and that version intervals do not overlap unexpectedly.