Skip to main content

Slowly Changing Dimensions

Descriptive attributes change. A Type 1 slowly changing dimension overwrites the old value and answers with current context. A Type 2 dimension inserts a new version with its own surrogate key and effective interval, preserving the context that applied when a fact occurred. Some attributes need no history; others are legally or analytically essential.

Prerequisites​

Complete Star Schemas and Late-Arriving Data and Watermarks.

Learning goals​

  • Choose Type 1 or Type 2 per attribute and question.
  • Model non-overlapping half-open validity intervals.
  • Resolve facts to the correct historical surrogate key.
  • Plan for late-arriving facts and dimension corrections.

Worked example​

The history uses [valid_from, valid_to) intervals. Joining through the natural customer identity demonstrates an as-of lookup; a production fact would normally store the resolved historical surrogate key at load time.

SELECT h.region, SUM(f.amount_cents)::INTEGER AS amount_cents
FROM fct_event AS f
JOIN dim_customer AS current_customer USING (customer_key)
JOIN dim_customer_history AS h
  ON h.customer_id = current_customer.customer_id
 AND f.occurred_at >= h.valid_from
 AND (f.occurred_at < h.valid_to OR h.valid_to IS NULL)
GROUP BY h.region
ORDER BY h.region;

The result assigns 2,050 cents to North, 1,750 to South, and 750 to West. Customer c-1 changed region at the start of October 2, so earlier events remain North.

For Type 2, enforce one current row per natural key, non-overlapping intervals, and exactly one match for every resolved fact. A late-arriving fact may need an older dimension version. A late-arriving dimension correction can require re-keying affected facts; define the correction window and audit trail rather than rewriting history invisibly.

Limitations and common mistakes​

  • Type 1 destroys prior analytical context and should be intentional.
  • Type 2 on volatile attributes creates excessive versions and expensive joins.
  • Inclusive interval ends can match a boundary timestamp twice.
  • is_current is convenient but does not replace interval validation.
  • Source-system history may be incomplete; a warehouse cannot reconstruct changes it never received.

Practice​

For customer name, risk tier, email, and region, choose Type 1, Type 2, or no warehouse history. Explain the question each choice supports.

Next steps​

Continue with dbt Models and Sources to encode model dependencies and incremental behavior.

Primary sources​

Checked 2026-10-15: Kimball Group slowly changing dimension techniques, Type 2 guidance, and late-arriving dimensions.