Chapter 07 · Slowly Changing Dimensions: Types 0–7, History, and Effective Dating
Detect Changes Reliably: Hashes, Column Comparison, Source CDC, Null Semantics, and Late Corrections
Detect real changes with deterministic column comparison, canonical hashes, CDC evidence, explicit NULL semantics, and policy for corrections that arrive after their business-effective time.
Learning outcomes
An SCD2 loader is only as trustworthy as its change detector. If it treats NULL and empty string as the same, ignores a governed column, serializes fields inconsistently, or creates a new row for every duplicate CDC delivery, history becomes either incomplete or explosively noisy. AtlasMart therefore separates source-event identity, change comparison, and effective-time policy.
Compare explicit column-by-column detection with canonical-hash detection and state what each proves.
Preserve NULL semantics and deterministic serialization before hashing.
Use CDC event identity/order as evidence without assuming CDC automatically provides end-to-end exactly-once behavior.
Distinguish business changes from corrections and late-arriving effective dates.
Prove reruns and duplicate deliveries do not create spurious Type 2 versions.
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.
A hash is an optimization for comparing a defined set of normalized columns. It is not business identity, not proof of source ordering, and not a substitute for storing the governed values needed for audit and diagnosis.
1. Define the comparison domain first
AtlasMart's Type 2 comparison domain is
customer_name, segment,
geography_code, and lifecycle_status.
original_signup_channel is Type 0 and is
deliberately excluded from later change detection. Operational
columns such as batch ID or ingestion timestamp must also be
excluded or every rerun would appear different.
| Column | Policy | Why |
|---|---|---|
| customer_name | governed comparison; often Type 1 correction by policy | human-readable identity label |
| segment | Type 2 | historical analytical classification |
| geography_code | Type 2 | historical location classification |
| lifecycle_status | Type 2 | active/inactive history |
| original_signup_channel | Type 0 | retain original |
| ingested_at | operational metadata; exclude | would cause false changes on rerun |
2. Column comparison is transparent; hashes are compact
Explicit comparison is easy to diagnose because the changed column is visible. A canonical hash can reduce comparison width, but the serialization must be stable and the algorithm/version must be recorded. Changing hash algorithm or normalization rules without a migration plan can make every row look changed.
import hashlib, jsondef tracked_hash(customer_name, segment, geography_code, lifecycle_status): payload = json.dumps({ "customer_name": customer_name, "segment": segment, "geography_code": geography_code, "lifecycle_status": lifecycle_status, }, sort_keys=True, separators=(",", ":"), ensure_ascii=False) return hashlib.sha256(payload.encode("utf-8")).hexdigest()3. NULL and empty string are different states
A missing value can mean unknown/not supplied; an empty string
can mean a source supplied an empty value. Treating both as
'' before hashing collapses two source states. The
lab's JSON serialization preserves that distinction.
| Input state | SHA-256 example from canonical JSON |
|---|---|
{"email": null} |
441594d595b3469d0fa31c64045d9d78dcd9ee682020245a46a2ba95d1597cd7
|
{"email": ""} |
bee800c777eac6a5d6548043ae66e0512dcd92a725dc9a8b6ea2963fde2bf0de
|
The hashes differ. That proves only that this serialization distinguishes the values; it does not decide whether the business should treat them as semantically different. That decision belongs in the source/data contract.
4. CDC event identity and row-change identity are not the same
A CDC stream can redeliver the same source transaction.
AtlasMart uses a unique event_id to make
application idempotent. Separately, it compares the tracked
business attributes to decide whether that event creates a new
dimension version. A duplicate event ID is a replay; a new event
ID with unchanged tracked values is a no-op business change.
| Case | Event identity | Tracked values | Loader action |
|---|---|---|---|
| Exact retry | same event ID | same | ignore replay |
| New heartbeat/snapshot | new event ID | same | record processed event; no new Type 2 row |
| True business change | new event ID | different | close covering interval and create version |
| Correction effective in past | new event ID | different + backdated effective time | apply correction policy and re-evaluate affected facts |
5. Deliberately wrong approach: hash every source column including load timestamp
If ingested_at participates in the hash, every
retry has a different hash even when customer semantics are
identical. The loader then emits a new Type 2 row on every run.
This is a classic case where a technically correct hash produces
business-incorrect history because the comparison domain was
wrong.
The repair is to version the comparison contract, include only governed history-bearing attributes, keep operational metadata separate, and test repeat runs.
6. Late correction is a temporal operation, not just a changed hash
C002's geography change is received later but effective from 10 September. The changed hash tells AtlasMart that the row differs; the effective date tells it where history must split. If facts from 10–19 September were previously linked to the old version, a governed correction workflow must identify and re-key or restate those affected facts. Chapter 11 covers the broader restatement policy; this chapter proves the interval mechanics.
Knowledge check
Check your understanding
- Why should ingestion timestamp usually be excluded from an SCD business-attribute hash?
- What does a changed hash prove?
- Why preserve NULL separately from empty string?
- How are duplicate delivery and unchanged business state different?
- What extra work can a backdated correction require?
Review the answers
1. It changes on reruns even when business semantics do not, causing false Type 2 versions.
2. Only that the canonical values in the defined comparison domain differ; it does not identify the business reason or effective time by itself.
3. They can represent different source states, and collapsing them can hide real changes or create ambiguous semantics.
4. Duplicate delivery is detected by event identity; unchanged business state is detected by comparing tracked values even for a new event.
5. Splitting history at the business-effective boundary and identifying downstream facts/materializations that were previously bound under the old version.
Summary and next step
Reliable history requires deterministic comparison, explicit NULL semantics, event idempotency, and effective-time policy. The final lesson combines those pieces into an executable Type 2 loader with repeat, backdate, delete/inactivation, unknown-member, and reconciliation tests.
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.