SCD Type 2

Type 2: Add a New Versioned Row

Type 2 keeps history by inserting a new row, with a new surrogate key, whenever a tracked attribute changes. The old row is closed: its valid_to date is set to the change date and its is_current flag to false. Each fact keeps the key of the version that was valid when the event happened, so old sales stay with the old country and new sales go to the new one:

Type 2 versions of one customer, and which facts point to each
Type 2 versions of one customer, and which facts point to each

The half-open range [valid_from, valid_to) means a date belongs to exactly one version. A far-future valid_to such as 9999-12-31 avoids NULL checks in range predicates. Type 2 is why dimensions need surrogate keys: customer 1 now has two rows. The cost is growth, since a customer with frequently changing attributes adds a row per change, and queries that want "the customer as of today" must filter is_current or group by the natural key.