What is a Type 2 slowly changing dimension?

WHAT THIS TESTS This assesses whether you can model time-varying descriptive data so that historical reports remain accurate, a core data warehousing skill.

A GOOD ANSWER COVERS A slowly changing dimension is a dimension whose attribute values change infrequently and unpredictably over time, like a customer's address, marital status, or sales region. The challenge is whether to keep history. Type 1 simply overwrites the old value, keeping no history. Type 2 preserves full history by versioning rows. To implement Type 2, give the dimension a surrogate key separate from the natural business key. When a tracked attribute changes, you do not update the existing row's value. Instead you expire the current row by setting its effective end date to now and its current-flag to false, then insert a brand-new row with a new surrogate key, the updated attribute, an effective start date of now, a null or far-future end date, and current-flag true. Fact rows store the surrogate key of whichever version was current at the time of the event, so aggregations naturally attribute each fact to the correct historical version.

COMMON WRONG ANSWERS Describing overwriting the value, which is Type 1 and loses history despite the question asking for Type 2. Using the natural key as the fact foreign key, which breaks versioning since multiple versions share one natural key. Forgetting the validity dates or current flag, leaving no way to identify the active version. Confusing Type 2 with Type 3, which keeps only a single prior value in an extra column.

LIKELY FOLLOW-UPS How do facts pick the correct version at load time? What are the trade-offs of Type 1 versus Type 2 versus Type 3? How does dimension table growth affect performance? How do you handle a late-arriving dimension or fact?

ONE CONCRETE EXAMPLE A customer moves cities. The old row gets end date today and current false; a new row with a new surrogate key carries the new city, start date today, end date null, and current true. A sales report by city for last year still credits the old city because those fact rows point to the prior surrogate key.