Choosing type 1 vs type 2 per column

Description

Lessons 3, 4, and 5 treated category and unit_cost as if they were dim_product_scd's only columns — and, for P002's real change you've been following since lesson 2, they are. But dim_product_scd has a fourth business column, product_name, that no earlier lesson has touched yet. This lesson asks the question Kimball answers at the column level, not the whole-table level: if Kiosko decides to add the size to P002's name — from "Energy Bar" to "Energy Bar 40g," a purely descriptive catalog improvement, with no business implication — does that change deserve a new row, like category and unit_cost? The answer, with executed evidence, is no — and this lesson builds exactly that mix: two columns with SCD type 2, one column with SCD type 1, coexisting in the same historized table.

Connection to the module. This lesson doesn't add any new row to dim_product_scd — P002's version count stays at two, exactly as lesson 5 left it. What it does is apply a product_name correction that propagates to both of P002's versions equally, demonstrating in code that a table historized with SCD type 2 doesn't force all its columns to be treated with SCD type 2 — the type gets chosen column by column, following the same criterion lesson 3 already previewed.

An analogy: the medical record, with two types of annotation

Think again of a medical record, the same kind of system you already used to think about surrogate keys. A well-designed record very carefully distinguishes between two types of annotation. There's the diagnosis — a blood pressure reading logged at every visit, a lab result with its exact date: each new value gets added as a separate entry, with its own date, because the complete history matters for understanding the patient's progress. And there's contact information — the phone number, the email address: if the patient corrects a typo in their phone number, nobody expects the record to keep "the misspelled phone number" as a separate historical entry; it just gets corrected, in place, and the record keeps showing the correct value across all previous and future visits.

dim_product_scd is that same record. category and unit_cost are the diagnosis: every real change is a business fact worth preserving, version by version. product_name, when the change is a descriptive catalog improvement — not a brand relaunch someone would want to audit — is the contact information: it gets corrected where it stands, with no new version created, and the correction applies retroactively to any row that already existed.

Worked example: a column policy, declared and applied

First, explicitly declare which SCD type applies to each of dim_product_scd's business columns — not as an implicit decision, but as a structure anyone on the team can consult:

# scd_column_policy.py
import duckdb

from kiosko import DIM_PRODUCT

con = duckdb.connect()
con.execute("CREATE SEQUENCE product_key_seq START 1")
con.execute("""
    CREATE TABLE dim_product_scd (
        product_key  INTEGER PRIMARY KEY,
        product_id   VARCHAR NOT NULL,
        product_name VARCHAR,
        category     VARCHAR,
        unit_cost    DOUBLE,
        valid_from   DATE NOT NULL,
        valid_to     DATE,
        is_current   BOOLEAN NOT NULL DEFAULT true
    )
""")
con.executemany(
    "INSERT INTO dim_product_scd VALUES (nextval('product_key_seq'), ?, ?, ?, ?, DATE '2026-08-01', NULL, true)",
    [(p["product_id"], p["product_name"], p["category"], p["unit_cost"]) for p in DIM_PRODUCT],
)
# Lesson 5's change, already applied: P002 has two versions.
con.execute("UPDATE dim_product_scd SET valid_to = DATE '2026-08-14', is_current = false WHERE product_id = 'P002' AND is_current = true")
con.execute("""
    INSERT INTO dim_product_scd VALUES
        (nextval('product_key_seq'), 'P002', 'Energy Bar', 'health-snacks', 0.68, DATE '2026-08-15', NULL, true)
""")

COLUMN_SCD_POLICY = {
    "product_name": {"scd_type": 1, "reason": "descriptive/catalog data -- corrections do not warrant history"},
    "category":     {"scd_type": 2, "reason": "changes how historical reports group data"},
    "unit_cost":    {"scd_type": 2, "reason": "affects margin -- a profitability report needs the real cost at each moment"},
}

print("=== Per-column historization policy, dim_product_scd ===")
for col, policy in COLUMN_SCD_POLICY.items():
    print(f"{col:14} -> SCD type {policy['scd_type']}  ({policy['reason']})")

What to expect (policy). Running this first part, the output is exactly this:

=== Per-column historization policy, dim_product_scd ===
product_name   -> SCD type 1  (descriptive/catalog data -- corrections do not warrant history)
category       -> SCD type 2  (changes how historical reports group data)
unit_cost      -> SCD type 2  (affects margin -- a profitability report needs the real cost at each moment)

Now, the part that makes the policy real: Kiosko decides to add the size to P002's catalog name — "Energy Bar" becomes "Energy Bar 40g" — a purely descriptive correction. Since product_name is declared SCD type 1 in the policy, it gets applied with a direct UPDATE, without the is_current = true filter you'd use to close a version — precisely because this isn't about closing anything, it's about correcting the same data across every existing row for that product:

print("\n=== P002 BEFORE the product_name correction (2 versions, same name) ===")
print(con.sql("SELECT product_key, product_id, product_name, category, unit_cost, valid_from, valid_to, is_current FROM dim_product_scd WHERE product_id = 'P002' ORDER BY product_key"))

# SCD type 1 on ONE specific column: no is_current filter, applies to ALL versions.
con.execute("""
    UPDATE dim_product_scd
    SET product_name = 'Energy Bar 40g'
    WHERE product_id = 'P002'
""")

print("\n=== P002 AFTER the product_name correction (2 versions, name corrected in both) ===")
print(con.sql("SELECT product_key, product_id, product_name, category, unit_cost, valid_from, valid_to, is_current FROM dim_product_scd WHERE product_id = 'P002' ORDER BY product_key"))

What to expect (product_name correction). Running this second part, the output is exactly this:

=== P002 BEFORE the product_name correction (2 versions, same name) ===
┌─────────────┬────────────┬──────────────┬───────────────┬───────────┬────────────┬────────────┬────────────┐
│ product_key │ product_id │ product_name │   category    │ unit_cost │ valid_from │  valid_to  │ is_current │
│    int32    │  varchar   │   varchar    │    varchar    │  double   │    date    │    date    │  boolean   │
├─────────────┼────────────┼──────────────┼───────────────┼───────────┼────────────┼────────────┼────────────┤
│           2 │ P002       │ Energy Bar   │ snacks        │       0.6 │ 2026-08-01 │ 2026-08-14 │ false      │
│           5 │ P002       │ Energy Bar   │ health-snacks │      0.68 │ 2026-08-15 │ NULL       │ true       │
└─────────────┴────────────┴──────────────┴───────────────┴───────────┴────────────┴────────────┴────────────┘

=== P002 AFTER the product_name correction (2 versions, name corrected in both) ===
┌─────────────┬────────────┬────────────────┬───────────────┬───────────┬────────────┬────────────┬────────────┐
│ product_key │ product_id │  product_name  │   category    │ unit_cost │ valid_from │  valid_to  │ is_current │
│    int32    │  varchar   │    varchar     │    varchar    │  double   │    date    │    date    │  boolean   │
├─────────────┼────────────┼────────────────┼───────────────┼───────────┼────────────┼────────────┼────────────┤
│           2 │ P002       │ Energy Bar 40g │ snacks        │       0.6 │ 2026-08-01 │ 2026-08-14 │ false      │
│           5 │ P002       │ Energy Bar 40g │ health-snacks │      0.68 │ 2026-08-15 │ NULL       │ true       │
└─────────────┴────────────┴────────────────┴───────────────┴───────────┴────────────┴────────────┴────────────┘

Stop at the final result's two rows. product_name changed from "Energy Bar" to "Energy Bar 40g" in both rows — product_key = 2 (the historical, closed version) and product_key = 5 (the current version) — while category and unit_cost each keep their own values for that version: snacks/0.6 in the closed row, health-snacks/0.68 in the current one. No new row got created, and P002's version count stays at two. This is, in code, the complete difference between the two techniques coexisting in the same table: category/unit_cost respect each row's validity range; product_name gets corrected retroactively and uniformly, as if the "correct" name had always been "Energy Bar 40g."

Diagram: the same table, two different disciplines

dim_product_scd -- P002, after the product_name correction
─────────────────────────────────────────────────────────────────────
product_key │ product_name    │ category      │ unit_cost │ validity
─────────────────────────────────────────────────────────────────────
     2      │ Energy Bar 40g  │ snacks        │   0.60    │ through 08-14
     5      │ Energy Bar 40g  │ health-snacks │   0.68    │ from 08-15
─────────────────────────────────────────────────────────────────────
              ↑ SCD type 1:          ↑ SCD type 2 in both:
              same value across       each row keeps ITS
              ALL rows                own historical value

flowchart TB
    A["dim_product_scd, P002"] --> B["product_name\nSCD type 1\nUPDATE with no is_current filter\napplies to ALL rows"]
    A --> C["category, unit_cost\nSCD type 2\nUPDATE + INSERT with is_current filter\nonly the current row gets closed"]

Going deeper: Kimball's criterion, in Kiosko's practice

Lesson 3 already previewed the criterion in the abstract: SCD type 1 is correct when the change is a correction of a descriptive value, not an auditable business fact. This lesson turns it into an operational question, column by column, you can ask about any dimension: if someone asked "what was this column's value on such-and-such past date?", does that question have real business meaning, or is it just "what's the correct value today?" disguised as a historical question?

For unit_cost, the question "how much did P002 cost on August 10th?" has real business meaning: a margins analyst needs it to calculate that week's sales profitability with the real cost from that moment, not today's cost. For category, the question "which category was P002 in on August 10th?" also matters: a "revenue by category, full August" report needs to classify sales from before August 15th under snacks, not retroactively under health-snacks — exactly the mistake module 5 is going to show in numbers when the JOIN is written incorrectly. For product_name, on the other hand, the question "what was P002 called on August 10th?" almost never has a business answer different from "whatever it's called now, corrected" — unless the name change is itself a business event (the brand relaunch from lesson 3's exercise 3), the name is, for practical purposes, catalog information that gets kept correct, not a fact that gets audited.

This distinction has an important practical consequence for any real dim_* table: there's no such thing as "this dimension is SCD type 1" or "this dimension is SCD type 2" as a single label for the whole table. dim_product_scd, in this module, is SCD type 2 for two of its columns and SCD type 1 for a third — and that mix is exactly what Kimball documents as common practice in a real dimensional model, not an exception or a half-measure compromise.

Common mistakes

Applying the same WHERE (with or without is_current) to every column, without distinguishing their policy. What happens: someone, correcting product_name, copies the UPDATE ... WHERE product_id = 'P002' AND is_current = true pattern used to close versions in lessons 4 and 5, without realizing that filter leaves the historical row (product_key = 2) with the old name. Why it happens: the WHERE ... AND is_current = true pattern becomes almost automatic after repeating it several times in the module. How to spot it: if, after "correcting" product_name, you still see two different names for P002 when querying its two versions, you applied the wrong filter — a catalog correction should look the same across the product's entire history. How to fix it: before writing any UPDATE against dim_product_scd, first ask what policy applies to that specific column — check COLUMN_SCD_POLICY — and only then decide whether the UPDATE needs the is_current = true filter (type 2, close a version) or doesn't need it at all (type 1, correct every version).

Deciding a column's policy without involving whoever actually consumes the data. What happens: a data team unilaterally decides category should be SCD type 1 — "to simplify, category almost never changes anyway" — without asking the finance team whether it needs historical revenue correctly classified by category. Why it happens: the type 1 vs. type 2 decision looks purely technical, when in reality it depends entirely on what business questions someone is going to ask later. How to spot it: if your column policy got decided with no conversation with the people consuming reports built on that dimension, you risk someone discovering, months later, that they needed history that was never preserved — an irreversible mistake, the same as lesson 3's. How to fix it: this lesson's column policy — COLUMN_SCD_POLICY — should be a documented conversation with the data's consumers, not an isolated technical decision; each entry's reason (reason) is, deliberately, just as important as the type itself.

Assuming a column with SCD type 1 never needs revisiting. What happens: a team declares product_name type 1 today and never questions that decision again, even when the business context changes — for example, if Kiosko starts running A/B tests on product names and does need to know which name was active during each test. Why it happens: a column policy, once declared, feels like a permanent decision. How to spot it: if the business starts asking historical questions about a column marked type 1 — and the answer is always "we don't know, it got overwritten" — the policy has gone stale. How to fix it: COLUMN_SCD_POLICY isn't a decision made once — it's a declaration that gets revisited when the business changes, exactly as every other design decision in this guide is justified by context, not dogma.

Exercises

Exercise 1 — Verify P001, P003, and P004 weren't affected by the product_name correction. After running the worked example, confirm the other three products keep their original product_name.

print(con.sql("""
    SELECT product_id, product_name
    FROM dim_product_scd
    WHERE product_id != 'P002'
    ORDER BY product_id
"""))

┌────────────┬───────────────────────┐
│ product_id │      product_name     │
│  varchar   │        varchar        │
├────────────┼───────────────────────┤
│ P001       │ Bottled Water 600ml   │
│ P003       │ Instant Coffee Sachet │
│ P004       │ Phone Charger Cable   │
└────────────┴───────────────────────┘

Exercise 2 — Explain why product_id and product_key don't appear in COLUMN_SCD_POLICY. In 2-3 sentences, using what you know about natural and surrogate keys since module 2, explain why those two columns need no SCD policy at all.

Exercise 3 — Decide the policy for a new, hypothetical column. Suppose Kiosko adds a supplier_name column to dim_product_scd, recording which supplier stocks each product. In 2-3 sentences, argue whether supplier_name should be treated with SCD type 1 or type 2, using this lesson's "going deeper" criterion.

Summary and next step

This lesson demonstrated, with executed code, that dim_product_scd doesn't need a single SCD type for the whole table: category and unit_cost remain SCD type 2 — each version keeps its own value, respecting valid_from/valid_to — while product_name gets corrected with SCD type 1 — an UPDATE with no validity filter, propagating equally to every existing version. COLUMN_SCD_POLICY formalized that mix as an explicit, documented decision, not an implicit one.

Before moving on you should be able to: explain the difference between a type 1 UPDATE's WHERE (no is_current) and a type 2 one (with is_current = true); name the central question that decides a column's policy ("does the historical value have business meaning?"); and argue, for a new hypothetical column, which SCD type would apply to it.

Lesson 7 briefly completes the SCD vocabulary: SCD type 3 — which preserves one previous value in an additional column, without growing indefinitely like type 2 — and the names of the variants that exist beyond types 1, 2, and 3, without implementing them in depth.

Resources

• Kimball Group — "Slowly Changing Dimension Type 2" — the source that explicitly documents that the SCD type choice happens attribute by attribute within the same dimension. kimballgroup.com/data-warehouse-business-intelligence-resources/kimball-techniques/dimensional-modeling-techniques/type-2. In English.
• Kimball Group — "Slowly Changing Dimension Type 1" — the definition behind this lesson's product_name correction. kimballgroup.com/data-warehouse-business-intelligence-resources/kimball-techniques/dimensional-modeling-techniques/type-1. In English.