Chapter 07 · Slowly Changing Dimensions: Types 0–7, History, and Effective Dating
Type 2 Mechanics: Effective/Expiration Dates, Current Flag, Surrogate Keys, and Non-Overlapping Windows
Implement Type 2 effective windows, surrogate keys, current-row flags, temporal joins, and integrity tests that prove every customer has deterministic non-overlapping history.
Learning outcomes
Type 2 is easy to describe and easy to implement incorrectly. AtlasMart needs a temporal contract that gives every customer exactly one applicable version for any supported business time, at most one current row, deterministic boundary behavior, and a surrogate key that facts can store permanently.
Implement half-open Type 2 intervals and explain their boundary semantics.
Enforce one current row per durable customer and detect overlapping effective windows.
Resolve sales facts to the customer version valid at order date.
Demonstrate why inclusive end dates and ambiguous midnight boundaries create double matches or gaps.
Reconcile historical segment attribution to unchanged sales controls.
Chapter 07 preserves the accepted AtlasMart fact controls from Chapters 01–06: paid sales remain 7 order lines, 4 paid orders, 9 sold units, 625 paid GMV, 380 cost-at-sale, and 245 gross profit. The September 20 inventory snapshot remains 137 units. Chapter 07 changes only the customer-history representation: prior chapters treated customer attributes as a current-state dimension; this chapter migrates that customer domain to versioned Type 2 rows so historical facts can bind to the customer version that was valid at business event time. The migration is explicit and must reconcile all previously accepted fact totals.
The mandatory lab uses Python's standard-library
sqlite3 module and synthetic local data. Record
your actual Python and SQLite versions before running it. The
lab uses business-effective dates and half-open intervals
[effective_from, effective_to). Those are
explicit course conventions, not universal database syntax or
legal-retention policy. Performance, cloud cost, CDC delivery
guarantees, and production concurrency behavior are outside
what this small fixture proves.
The lab uses date precision because the synthetic source contract changes customer attributes by business date. Real systems may require timestamp precision, time zones, or sequence ordering; those choices must be defined before creating interval boundaries.
PRAGMA foreign_keys = ON;DROP TABLE IF EXISTS fact_sales_history;DROP TABLE IF EXISTS fact_sales_stage;DROP TABLE IF EXISTS applied_change_event;DROP TABLE IF EXISTS dim_customer_history;CREATE TABLE dim_customer_history ( customer_sk INTEGER PRIMARY KEY AUTOINCREMENT, durable_customer_id TEXT NOT NULL, source_customer_id TEXT NOT NULL, customer_name TEXT NOT NULL, segment TEXT NOT NULL, geography_code TEXT NOT NULL, lifecycle_status TEXT NOT NULL, original_signup_channel TEXT NOT NULL, effective_from TEXT NOT NULL, effective_to TEXT NOT NULL, is_current INTEGER NOT NULL CHECK (is_current IN (0,1)), change_hash TEXT NOT NULL, CHECK (effective_from < effective_to), UNIQUE (durable_customer_id, effective_from));CREATE UNIQUE INDEX ux_customer_one_current ON dim_customer_history(durable_customer_id) WHERE is_current = 1;CREATE TABLE applied_change_event ( event_id TEXT PRIMARY KEY, durable_customer_id TEXT NOT NULL, business_effective_from TEXT NOT NULL, applied_at TEXT NOT NULL);CREATE TABLE fact_sales_stage ( order_id TEXT NOT NULL, line_no INTEGER NOT NULL, event_date TEXT NOT NULL, durable_customer_id TEXT NOT NULL, product_id TEXT NOT NULL, quantity INTEGER NOT NULL, extended_amount NUMERIC NOT NULL, extended_cost NUMERIC NOT NULL, PRIMARY KEY(order_id,line_no));CREATE TABLE fact_sales_history ( order_id TEXT NOT NULL, line_no INTEGER NOT NULL, event_date TEXT NOT NULL, durable_customer_id TEXT NOT NULL, customer_sk INTEGER NOT NULL REFERENCES dim_customer_history(customer_sk), product_id TEXT NOT NULL, quantity INTEGER NOT NULL, extended_amount NUMERIC NOT NULL, extended_cost NUMERIC NOT NULL, PRIMARY KEY(order_id,line_no));1. Use one interval convention everywhere
AtlasMart uses half-open intervals: a row
applies when
effective_from <= event_time AND event_time <
effective_to. If Ada's first row ends at 19 September and the next row
begins at 19 September, an event on the 19th matches only the
new row. No subtraction of one second or one day is required.
| Version | effective_from | effective_to | is_current | segment |
|---|---|---|---|---|
| Ada v1 | 2026-01-01 | 2026-09-19 | 0 | SMB |
| Ada v2 | 2026-09-19 | 9999-12-31 | 1 | Mid-Market |
SELECT customer_sk, segment, geography_code, lifecycle_statusFROM dim_customer_historyWHERE durable_customer_id = :durable_id AND effective_from <= :event_date AND :event_date < effective_toORDER BY effective_from DESCLIMIT 1;2. Integrity is stronger than a current flag alone
A current flag is useful for current-state queries, but it does not prove temporal correctness. Two historical rows can overlap while only one row is marked current. AtlasMart therefore tests both invariants: one current row and zero pairwise interval overlap for each durable customer.
SELECT a.durable_customer_id, a.customer_sk AS a_sk, b.customer_sk AS b_skFROM dim_customer_history aJOIN dim_customer_history b ON a.durable_customer_id = b.durable_customer_id AND a.customer_sk < b.customer_sk AND a.effective_from < b.effective_to AND b.effective_from < a.effective_to;Expected result: zero rows. The overlap predicate works naturally with the same half-open convention used by the as-of join.
3. Controlled failure: inclusive end dates double-match a boundary
If one implementation stores the old row as valid through
2026-09-19 and another query uses
event_date BETWEEN effective_from AND effective_to,
an event on the 19th can match both the old and new versions.
The resulting fact duplication can inflate revenue even though
every source fact is correct.
The repair is not DISTINCT. The repair is one
interval convention, one lookup predicate, and a temporal
uniqueness test.
4. Build the local history state and re-key facts
The end-to-end loader appears in Lesson 5. For this lesson, the observable target after applying the three Chapter 07 changes is:
| Customer | Version history |
|---|---|
| D-CUST-001 | SMB until 2026-09-19; Mid-Market from 2026-09-19 |
| D-CUST-002 | G-WEST until 2026-09-10; G-EAST from 2026-09-10 (backdated correction) |
| D-CUST-004 | active until 2026-09-21; inactive from 2026-09-21 |
-- Historical/as-was segment: use the fact's stored Type 2 surrogate key.SELECT d.segment, SUM(f.extended_amount) AS paid_gmvFROM fact_sales_history fJOIN dim_customer_history d ON d.customer_sk=f.customer_skGROUP BY d.segment ORDER BY d.segment;-- Current/as-is segment: join durable identity to the one current row.SELECT cur.segment, SUM(f.extended_amount) AS paid_gmvFROM fact_sales_history fJOIN dim_customer_history cur ON cur.durable_customer_id=f.durable_customer_id AND cur.is_current=1GROUP BY cur.segment ORDER BY cur.segment;With the final fixture, as-was segment totals are Consumer 350, Mid-Market 150, and SMB 125. The current view groups both C001 orders under Mid-Market, so Mid-Market becomes 275 while Consumer remains 350. Both views sum to 625; they answer different questions.
5. Surrogate keys make the historical binding durable
Once a fact stores the version surrogate key selected at event time, later customer changes do not rewrite that historical relationship. A restatement is therefore an explicit operation, not an accidental effect of joining by natural key to a mutable dimension row.
Chapter 11 will revisit late-arriving facts and restatement policy. Here the principle is narrower: version lookup is deterministic, and facts do not silently drift when the current row changes.
Knowledge check
Check your understanding
- Why does the course use half-open intervals?
- Does a unique current row prove that history does not overlap?
- Why is DISTINCT not a repair for a double-matching as-of join?
- Why can historical and current segment reports both be correct while returning different group totals?
- What does the fact foreign key preserve in Type 2?
Review the answers
1. They make adjacent versions share one boundary without overlap: the old version excludes the boundary and the new version includes it.
2. No. Historical rows can overlap even if only one row is current, so an explicit overlap test is required.
3. It hides symptoms and can discard legitimate duplicates; the interval model and temporal join predicate must be corrected.
4. They answer different semantic questions: segment as of the sale versus segment today.
5. The exact dimension version that was valid for the business event under the declared effective-time policy.
Summary and next step
Type 2 correctness is interval correctness plus deterministic key resolution. Next, compare the more advanced Types 4–7 when the business needs both historical and current perspectives or rapidly changing attribute groups.
Authoritative references
- Kimball Group — Dimensional Modeling Techniques — Authoritative index listing SCD Types 0 through 7 and their standard names.
- Kimball Group — Slowly Changing Dimensions — Why changing descriptive attributes require deliberate history policy.
- Kimball Group — Slowly Changing Dimensions, Part 2 — Type 2 new-row mechanics and Type 3 alternate-reality framing.
- Kimball Group — Design Tip #152 — Definitions and intent for advanced/hybrid SCD Types 4–7.
- SQLite — Partial Indexes — Used by the local lab to enforce one current row per durable customer.
- SQLite — CREATE TABLE — Constraint semantics used by the deterministic local lab.