SCD type 3 and other variants, briefly

Description

The previous lessons covered the two SCD types that solve 95% of real cases: type 1 (overwrite) and type 2 (historize with new rows). This lesson briefly closes the vocabulary, with SCD type 3 — which Kimball defines as the type that "adds a new attribute in the dimension to preserve the old attribute value; the new value overwrites the main attribute, as in a type 1 change" — and with two more names worth recognizing even though this guide doesn't implement them in depth: type 4 (mini-dimension) and type 6 (hybrid). You're going to build a small, executed type 3 example over the same P002 change, and you're going to finish the lesson able to name, precisely, all five variants of Kimball's SCD vocabulary — not just the two you implemented in depth.

Connection to the module. This lesson is deliberately brief — this guide's design calls it "briefly" for a reason: type 3 has a much more limited use than type 1 and type 2 in practice ("used relatively infrequently," in Kimball's own documentation's words), and type 4 and type 6 are extensions this guide names but doesn't build. Lesson 8 doesn't depend on anything you learn here about type 3, 4, or 6; it depends only on dim_product_scd as lesson 6 left it.

An analogy: the notebook with a single "before" line

Think of an address-change form with two boxes: "current address" and "previous address." Unlike an ID document's complete history — which records every move, with no limit — this form only has room for one previous address. If the person moves a second time, the "previous address" box gets overwritten with what was, until a moment ago, the "current address" — and the move from two addresses ago disappears forever, with no trace at all.

SCD type 3 is exactly that two-box form. Unlike type 2, which adds a new row every time something changes — with no limit on how many versions it can accumulate — type 3 adds a new column (previous_category, for example) that stores only one previous value, in the same row. It's simpler to query than type 2 — you never need a JOIN or a date range to know "what was the previous value," it's right there, in the row — but it pays for that with limited memory: it only remembers one step back, never the complete history.

Worked example: SCD type 3 over P002

Build dim_product_type3 with two additional columns — previous_category and previous_unit_cost — that store, exclusively, each attribute's immediately previous value, plus a changed_on column recording when that single remembered change happened:

# scd_type3.py
import duckdb

con = duckdb.connect()
con.execute("""
    CREATE TABLE dim_product_type3 (
        product_id          VARCHAR,
        category            VARCHAR,
        previous_category   VARCHAR,
        unit_cost           DOUBLE,
        previous_unit_cost  DOUBLE,
        changed_on          DATE
    )
""")
con.execute("""
    INSERT INTO dim_product_type3 VALUES
        ('P001', 'beverages',   NULL, 0.40, NULL, NULL),
        ('P002', 'snacks',      NULL, 0.60, NULL, NULL),
        ('P003', 'beverages',   NULL, 0.35, NULL, NULL),
        ('P004', 'electronics', NULL, 2.10, NULL, NULL)
""")

print("=== dim_product_type3, BEFORE the change (P002) ===")
print(con.sql("SELECT * FROM dim_product_type3 WHERE product_id = 'P002'"))

# The same usual change: P002's category and unit_cost, on 2026-08-15.
# Unlike type 2, NO new row is inserted -- the main value gets overwritten,
# but before overwriting it, it gets copied to the "previous_*" column.
con.execute("""
    UPDATE dim_product_type3
    SET previous_category  = category,
        previous_unit_cost = unit_cost,
        category            = 'health-snacks',
        unit_cost            = 0.68,
        changed_on           = DATE '2026-08-15'
    WHERE product_id = 'P002'
""")

print("\n=== dim_product_type3, AFTER the change (P002, still ONE row) ===")
print(con.sql("SELECT * FROM dim_product_type3 WHERE product_id = 'P002'"))

What to expect. Running python3 scd_type3.py, the output is exactly this:

=== dim_product_type3, BEFORE the change (P002) ===
┌────────────┬──────────┬───────────────────┬───────────┬────────────────────┬────────────┐
│ product_id │ category │ previous_category │ unit_cost │ previous_unit_cost │ changed_on │
│  varchar   │ varchar  │      varchar      │  double   │       double       │    date    │
├────────────┼──────────┼───────────────────┼───────────┼────────────────────┼────────────┤
│ P002       │ snacks   │ NULL              │       0.6 │               NULL │ NULL       │
└────────────┴──────────┴───────────────────┴───────────┴────────────────────┴────────────┘

=== dim_product_type3, AFTER the change (P002, still ONE row) ===
┌────────────┬───────────────┬───────────────────┬───────────┬────────────────────┬────────────┐
│ product_id │   category    │ previous_category │ unit_cost │ previous_unit_cost │ changed_on │
│  varchar   │    varchar    │      varchar      │  double   │       double       │    date    │
├────────────┼───────────────┼───────────────────┼───────────┼────────────────────┼────────────┤
│ P002       │ health-snacks │ snacks            │      0.68 │                0.6 │ 2026-08-15 │
└────────────┴───────────────┴───────────────────┴───────────┴────────────────────┴────────────┘

P002 still has exactly one row — unlike dim_product_scd in lesson 5, which has two — but that single row keeps snacks and 0.6 in previous_category/previous_unit_cost, alongside the current value. Any query needing "the current value and the immediately previous one, with no JOIN" can read them directly from this row. What this table can't answer is the question type 2 does resolve: if P002 changed a third time, previous_category would get overwritten with health-snacks, and snacks — the original value — would disappear without a trace, exactly the same limitation the two-box form analogy already anticipated.

Diagram: Kimball vocabulary's five variants, in a table

Type  Name                   What it does                                How many versions it keeps
────  ─────────────────────  ──────────────────────────────────────────  ─────────────────────────
1     Overwrite              Overwrites the value on the same row         1 (current only)
2     Add new row            Adds a new row, with valid_from/             All, no limit
                              valid_to/is_current
3     Add new attribute      Adds a "previous_*" column on the            2 (current + 1 previous)
                              same row
4     Mini-dimension         Separates fast-changing attributes into      All (in the mini-dimension,
                              a separate table, with its own key          not in the base dimension)
6     Hybrid (1+2+3)         Combines the three previous techniques on    All, PLUS the current value
                              the same row                                 embedded in every version

flowchart LR
    A["Type 1\noverwrite\n1 version"] --- B["Type 3\n+ 1 previous_* column\n2 versions"]
    B --- C["Type 2\n+ new row\nall versions"]
    C --- D["Type 6\nhybrid 1+2+3\nall + current embedded"]
    E["Type 4\nseparate\nmini-dimension"]

Going deeper: type 4 and type 6, named without implementing

SCD type 4 (mini-dimension). Kimball describes it this way: "used when a group of attributes in a dimension changes rapidly and is separated into a mini-dimension." The idea: if dim_product_scd had, besides category and unit_cost, a group of attributes that changed constantly — say, a stock_level updated every hour — historizing those attributes with type 2 would make the main dimension grow out of control, one new row for every inventory update. Type 4 solves this by separating that group of volatile attributes into a separate table — a "mini-dimension" — with its own surrogate key, referenced from the fact alongside the main dimension's key. Kiosko has no attribute in this guide volatile enough to justify a mini-dimension — category and unit_cost change, at most, a few times — so this guide names type 4 without building it.

SCD type 6 (hybrid). Kimball's definition is precise: "type 6 builds on the type 2 technique, also embedding current type 1 versions of the same attributes in the dimension row, so that fact rows can be filtered or grouped both by the type 2 attribute value in effect at the moment of the measurement, and by the attribute's current value." In Kiosko's terms, this would mean every historical row of dim_product_scd — including product_key = 2, P002's already-closed version — would have, besides its own historical category (snacks), an additional column like current_category that always shows the most recent value (health-snacks), no matter how old that row is. This lets you answer two different questions with the same table: "what category was this product in when it sold?" (historical column) and "what category does this sale fall into if I reclassify it with today's catalog?" (embedded type 1 column) — the name "type 6" comes, per informal industry convention, from combining type 1 + type 2 + type 3 (1+2+3=6). This guide doesn't implement it because Kiosko, with only one P002 change, doesn't yet have a business case that needs both questions at once — but it's worth recognizing as this vocabulary's complexity ceiling, for when the business case justifies it.

The boundary with this guide: a lakehouse's native time travel. Everything this module built — type 1, type 2, type 3 — historizes by hand, with columns you design and maintain yourself (valid_from, valid_to, is_current, previous_*). A completely different alternative exists, one that solves the same problem from the storage layer instead of from the model: modern lakehouse table formats — Apache Iceberg, Delta Lake — offer native time travel: every time a row changes, the table format itself preserves earlier versions of the complete file, queryable with engine syntax (for example, SELECT * FROM table FOR TIMESTAMP AS OF '2026-08-10'), with the modeler never having to design a single validity column. It's a genuinely different way of solving the same problem — querying the past without losing it — not just another implementation of the same pattern. This guide names this alternative once, here, without implementing it: lakehouse-and-iceberg-guide, this ecosystem's sibling guide, builds it in depth.

Common mistakes

Confusing type 3 with "a simpler version of type 2." What happens: someone implements type 3 thinking of it as a shortcut toward type 2, with the idea of "I'll migrate later to add more previous_previous_* columns if more history is needed." Why it happens: type 3, with just one extra column, looks like a natural intermediate step toward type 2. How to spot it: if your type 3 design assumes you're going to be able to "scale it up" by adding more and more previous_* columns to store more steps of history, you have this confusion — each additional column only adds one more step, never unlimited history, and the number of columns you'd need grows without bound if the attribute changes frequently. How to fix it: type 3 and type 2 are techniques with different purposes, not levels of the same scale — type 3 is correct when you genuinely only care about "the current value and the immediately previous one" (for example, to compare "before/after" a one-off change), never as a substitute for type 2 when the complete history matters.

Implementing type 4 or type 6 "because they sound more complete," with no business case justifying them. What happens: someone, after reading about the five variants, decides to implement the most sophisticated pattern available — type 6, the hybrid — for dim_product_scd, with Kiosko having no business question that actually needs it. Why it happens: it's tempting to assume "more capability" is always better, without measuring the extra maintenance cost. How to spot it: if you can't precisely name the specific business question type 6 would solve that type 2 doesn't — "reclassify historical sales with today's category," in this lesson's example — you don't have a real case, just a preference for complexity. How to fix it: the same discipline from lesson 6 applies here, at the whole-table level: choose the simplest variant that solves the real business question you have today, and increase complexity only when a new, concrete question demands it — never ahead of time, with no evidence.

Thinking a lakehouse's time travel makes learning SCD by hand unnecessary. What happens: someone, learning that Iceberg or Delta Lake solve the same problem with native time travel, concludes learning SCD type 1/2/3 by hand is wasted effort, something no longer done in practice. Why it happens: it's reasonable to assume the more modern tool completely replaces the manual technique. How to spot it: if you assume no company historizes dimensions by hand anymore, you're underestimating how many production warehouses still run on engines with no native time travel — or on tables that do have it, but where the data team still decides to build explicit validity columns anyway, for reasons of portability across engines. How to fix it: a lakehouse's time travel solves the same problem from a different layer, but the concept underlying both solutions — preserving the past without losing it, distinguishing the current version from historical ones — is the same one this module taught. Understanding SCD by hand first is what lets you recognize, with judgment, what problem native time travel solves when you see it in lakehouse-and-iceberg-guide.

Exercises

Exercise 1 — Simulate a second P002 change in dim_product_type3, and observe what gets lost. Suppose that, on 2026-08-25, P002's cost changes again, to 0.72, with no category change. Apply that change to dim_product_type3 with this lesson's same technique, and check what happened to the value 0.6 (the original cost, before the first change).

con.execute("""
    UPDATE dim_product_type3
    SET previous_unit_cost = unit_cost,
        unit_cost           = 0.72,
        changed_on           = DATE '2026-08-25'
    WHERE product_id = 'P002'
""")
print(con.sql("SELECT * FROM dim_product_type3 WHERE product_id = 'P002'"))

┌────────────┬───────────────┬───────────────────┬───────────┬────────────────────┬────────────┐
│ product_id │   category    │ previous_category │ unit_cost │ previous_unit_cost │ changed_on │
│  varchar   │    varchar    │      varchar      │  double   │       double       │    date    │
├────────────┼───────────────┼───────────────────┼───────────┼────────────────────┼────────────┤
│ P002       │ health-snacks │ snacks            │      0.72 │                0.68 │ 2026-08-25 │
└────────────┴───────────────┴───────────────────┴───────────┴────────────────────┴────────────┘

Exercise 2 — Name, from memory, the five SCD variants and their central idea in one sentence each. Without looking at this lesson's diagram, write the names of types 1, 2, 3, 4, and 6, with a sentence capturing each one's central idea.

Exercise 3 — Argue when you'd choose type 3 instead of type 2 for Kiosko's unit_cost. In 2-3 sentences, describe a hypothetical business scenario where type 3 — not type 2 — would be the right choice for unit_cost, different from this guide's scenario.

Summary and next step

This lesson closed the SCD vocabulary: type 3 (a previous_* column, one step of memory, run over P002), and the names of type 4 (mini-dimension, for fast-changing attributes) and type 6 (hybrid, type 1+2+3 combined), recognized without implementing them. You also named, once, the deeper alternative that exists outside this guide: a lakehouse's native time travel, which solves the same problem from the storage layer instead of with manual columns.

Before moving on you should be able to: explain the difference between type 3 (one-step memory) and type 2 (complete memory); name Kimball's SCD vocabulary's five variants, with one sentence for each; and name the native time travel alternative and which sibling guide builds it in depth.

Lesson 8 integrates the entire module into a single pipeline: dim_product_scd built from scratch, historized with MERGE INTO run twice, verified with exactly two versions of P002 — this module's closing project.

Resources

• Kimball Group — "Slowly Changing Dimension Type 3" — the official definition this lesson implements: "adds a new attribute... the new value overwrites the main attribute, as in a type 1 change." kimballgroup.com/data-warehouse-business-intelligence-resources/kimball-techniques/dimensional-modeling-techniques/type-3. In English.
• Kimball Group — "Slowly Changing Dimension Type 4 (Mini-Dimension)" — the definition for the pattern named, without implementing it, in this lesson's "going deeper" section. kimballgroup.com/data-warehouse-business-intelligence-resources/kimball-techniques/dimensional-modeling-techniques/type-4-mini-dimension. In English.
• Kimball Group — "Slowly Changing Dimension Type 6 (Hybrid)" — the definition for the hybrid pattern combining type 1, 2, and 3. kimballgroup.com/data-warehouse-business-intelligence-resources/kimball-techniques/dimensional-modeling-techniques/type-6. In English.