Data warehouse · dimension history

SCD Type 2 example: effective dates and historical rows

Slowly changing dimension Type 2 keeps one row per version of a business entity. Each row carries an effective date range and a current flag, so a historical fact can join to the attributes that were true on its own business date.

Quick answer

On an attribute change, close the old row and insert a new one. Use the half-open range[effective_from, effective_to): the start date belongs to the new version, the end date does not. Keep a stable business key (customer_id) plus a surrogate key (customer_sk) that identifies one version, and a current flag for “today”.

Before and after: customer C-100 moves from Standard to Premium

Customer C-100 is Standard / Austin in 2025. On 2026-03-01 the level becomes Premium and the branch becomes Denver. Loan LN-77 was disbursed on 2025-11-15. Move the date cursor, switch between overwrite and version rows, and watch which attributes the historical loan reads.

Business date cursor2025-11-15 · change boundary 2026-03-01 (inclusive)

customer dimension2 rows

Customer dimension versions
customer_sk	customer_id	level	branch	effective_from	effective_to	is_current
1001	C-100	Standard	Austin	2025-01-01	2026-03-01	false
2002	C-100	Premium	Denver	2026-03-01	open	true

historical query at the cursor2025-11-15

Matched version 1001: Standard · Austin

Effective range [2025-01-01, 2026-03-01) — the start date is included, the end date is not.

Historical loan joinLN-77 · 2025-11-15 · $250,000

The loan joins to 1001 and reads Standard · Austin. Correct: the attributes are the ones that were effective on the disbursement date.

The rows an SCD Type 2 update writes

The update touches two rows. The old row is closed, and a new row is inserted. Nothing is deleted, so the history remains queryable:

Customer dimension after the 2026-03-01 change
customer_sk	customer_id	level	branch	effective_from	effective_to	is_current
1001	C-100	Standard	Austin	2025-01-01	2026-03-01	false
2002	C-100	Premium	Denver	2026-03-01	open	true

customer_id stays the same because it identifies the customer.customer_sk changes because it identifies one version of that customer.

Boundary semantics: why the range is half-open

2026-02-28 is before effective_from, so it matches the old version: Standard / Austin.
2026-03-01 is the change date. Because the start is inclusive, it matches the new version: Premium / Denver.
The old row ends at 2026-03-01, but the end is exclusive, so the two versions never overlap.

Pick one convention and enforce it

Mixing inclusive and exclusive end dates creates either gaps or overlaps. Overlaps are worse: a historical fact can join to two dimension rows at once and duplicate the fact.

The historical query pattern

The fact table stores the business date (or timestamp) that determines the version. Join the fact to the dimension row whose range contains that date:

SELECT l.note_id,
       l.disbursed_date,
       c.customer_sk,
       c.level,
       c.branch
FROM loan_note AS l
JOIN customer AS c
  ON c.customer_id = l.customer_id
 AND l.disbursed_date >= c.effective_from
 AND l.disbursed_date <  c.effective_to;

-- customer_sk 1001 → Standard / Austin for a 2025-11-15 disbursement

The current row is simply the one with is_current = true (or the row whoseeffective_to is the open sentinel). Do not use the current flag for historical analysis — it answers “what is true today”, not “what was true then”.

Type 1, Type 2 and when each fits

Dimension history strategies
Strategy	What it keeps	Use when
Type 1 — overwrite	One row, current attributes only	History does not matter (for example fixing a typo)
Type 2 — version rows	Every version with effective dates	Historical facts must be interpreted with the attributes of their time
Type 3 — previous value column	Current and one previous value	Only the last change is ever needed

Edge cases and trade-offs

Several changes on the same day. The range must still be unambiguous. Decide whether same-day changes collapse into one version or use timestamps instead of dates.
Late corrections to history. A backdated change may require splitting an existing version. Treat it as a new version with explicit effective dates, not an in-place edit.
Only version what needs history. Every versioned attribute adds rows and join complexity. Keep attributes that nobody queries historically in the current row only.
Query cost. Range joins benefit from an index on(customer_id, effective_from). The current flag alone is not enough for historical queries.
Unknown values. Facts can reference a customer that did not exist yet at the business date. Decide whether to use an “unknown” dimension row or reject the fact.