Chapter 07 · Slowly Changing Dimensions: Types 0–7, History, and Effective Dating
Type 0/1/2/3 Fundamentals: Preserve, Overwrite, Add Version Rows, or Carry Alternate Values
Choose SCD behavior from reporting requirements and compare preserve, overwrite, version-row, and alternate-value techniques without treating type numbers as interchangeable recipes.
Learning outcomes
AtlasMart has reached the point where a single
dim_customer row cannot answer every question.
Finance wants prior sales grouped by the customer segment that
was true when the order occurred; account management wants those
same sales regrouped by the customer segment today. The first
design decision is therefore not an SCD number—it is which
version of descriptive truth each question requires.
Select Type 0, 1, 2, or 3 behavior from a concrete reporting requirement instead of from habit.
Explain exactly what historical evidence is preserved or destroyed by each technique.
Separate durable customer identity from version-specific surrogate identity.
Show why load time and business-effective time are different temporal concepts.
Define migration acceptance criteria from the Chapters 01–06 current-state customer dimension into Chapter 07 history-aware semantics.
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 Type 1 overwrite can be correct when the business explicitly wants only the latest value or when fixing an error. It is wrong only when it silently destroys history that downstream users expected to retain.
1. SCD types are response policies, not interchangeable recipes
A slowly changing dimension (SCD) technique defines what the warehouse does when a descriptive dimension attribute changes. The source event alone does not tell the warehouse whether to overwrite, preserve, version, or carry an alternate value. Governance and reporting requirements decide that.
| Technique | Warehouse response | Historical grouping | Typical requirement |
|---|---|---|---|
| Type 0 — retain original | Never change the governed attribute | Always original value | Original signup source / immutable original classification |
| Type 1 — overwrite | Replace old value in-place | Always latest value; prior value lost | Correction or explicitly current-only attribute |
| Type 2 — add row | Insert new surrogate-keyed version | As-was reporting by effective interval | True historical attribute changes |
| Type 3 — add attribute | Keep current plus one/few alternate values | Limited alternate-reality reporting | Current vs previous/reclassified view with bounded alternatives |
| Type 4 — mini-dimension | Split rapidly changing profile attributes | Profile history through mini-dimension key | High-churn attribute groups |
| Type 5 — Type 4 + Type 1 current outrigger | Historical profile plus overwritten current profile reference | As-was and current profile | Current profile needed without traversing fact |
| Type 6 — Type 2 + current Type 1 attributes | Type 2 rows also carry overwritten current attribute | As-was and as-is from same rows | Both historical and current grouping |
| Type 7 — dual Type 1/Type 2 views | Fact carries surrogate key plus durable key | As-was via surrogate; as-is via durable/current row | Separate semantic views for both realities |
AtlasMart will use Type 2 for customer segment,
geography_code, and
lifecycle_status in the executable lab.
original_signup_channel is treated as Type 0. A
spelling correction to customer_name could be Type
1 if governance classifies it as error correction rather than
business history.
2. Durable identity and version identity solve different problems
D-CUST-001 means “this customer across time.” A
Type 2 surrogate key means “this version of that customer over
one effective interval.” The durable key lets AtlasMart find all
versions and support current-view logic; the surrogate key lets
a fact bind to the exact descriptive state in effect at the
fact's business event time.
| Key | Example | Meaning | Safe use |
|---|---|---|---|
| Durable key | D-CUST-001 | same customer across all versions | find all versions; Type 7 current view |
| Source business key | ERP C001 | identity in one source namespace | source matching; not necessarily globally durable |
| Version surrogate key | generated integer | one Type 2 row/effective interval | fact foreign key for as-was reporting |
3. Deliberately wrong approach: overwrite every customer change
Suppose Ada Retail changes from SMB to Mid-Market effective 19 September. A Type 1 update would make the 18 September order appear to have been sold to a Mid-Market customer. The sales amount remains 125, so financial control totals still reconcile; the descriptive history is nevertheless wrong.
The repair is to preserve the earlier SMB version, close its
interval at 2026-09-19, and create a Mid-Market
version beginning at that boundary. A historical query joins the
18 September fact to the first version and the 19 September fact
to the second.
original_signup_channel -> Type 0 (retain original)customer_name spelling correction -> Type 1 (governed error correction)segment -> Type 2 (true business history)classification_current + classification_previous -> Type 3 (bounded alternate view)4. Business-effective time is not load time
AtlasMart receives the C002 geography correction on 20 September, but the source contract says it became effective on 10 September. If the warehouse starts the new version at load time, every fact from 10–19 September will bind to the wrong geography. Chapter 07 therefore stores business-effective boundaries and separately records when the change event was processed.
This is a policy choice: some sources do not provide reliable effective time. In that case a warehouse may deliberately use observation/load time, but the limitation must be documented rather than hidden.
5. Migration acceptance from the prior current-state dimension
The migration does not change paid sales rows or measures. It replaces one current customer row with one or more effective-dated customer versions and re-resolves the fact foreign key by event date. Acceptance requires the same 7 lines, 4 orders, 9 units, 625 GMV, 380 cost, and 245 gross profit before and after re-keying.
Reports that group by historical segment may change compared with Chapters 01–06 because those chapters had no segment history. That is an intentional semantic improvement, not a metric drift.
Knowledge check
Check your understanding
- Why can a Type 1 update preserve sales totals while still corrupting analytical history?
- What is the difference between a durable customer ID and a Type 2 surrogate key?
- When is Type 0 appropriate?
- Why is business-effective time preferred in this lab?
- What must remain unchanged during the Chapter 07 migration?
Review the answers
1. Because measures remain unchanged but old facts are regrouped under the new descriptive value, so the row counts and sums can reconcile while attribution is historically false.
2. The durable ID identifies the entity across time; the surrogate key identifies one effective-dated version of that entity.
3. When the attribute is defined to retain its original value, such as an original acquisition attribute or another immutable original classification.
4. It makes historical lookup reflect when the business change was actually true, including corrections that arrive after their effective date.
5. The previously accepted atomic fact rows and measure control totals; only descriptive version binding changes.
Summary and next step
SCD design starts with the truth users need, not with a type number. Next, implement Type 2 mechanics precisely enough that overlapping windows, duplicate current rows, and boundary mistakes become test failures instead of report surprises.
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.