SCD type 1: overwrite and lose history

Description

This lesson builds the simplest possible solution to the problem lesson 2 declared: when P002 changes category and cost, overwrite the existing row with the new values. That is, formally, the technique Ralph Kimball calls SCD type 1 (Slowly Changing Dimension type 1): "the old attribute value in the dimension row gets overwritten with the new value." You're going to implement it with a single UPDATE, confirm it works — dim_product_type1 correctly reflects P002's current state — and also confirm its real cost: once the UPDATE is applied, there is no query, no matter how clever, capable of recovering the snacks and 0.60 values.

Connection to the module. This lesson isn't a design mistake to avoid at all costs — it's a legitimate technique, with its own place, and this lesson makes that clear with evidence: SCD type 1 is exactly correct when the change is a correction (a capture error, a typo) and not a business fact someone is going to need to audit later. Lesson 4 is going to build, over this same P002 change, the alternative that does preserve history — and lesson 6 is going to formalize the exact criterion for choosing between the two, column by column.

An analogy: erasing the whiteboard

Think of a whiteboard where someone writes, every week, a product's current price so the sales team can check it quickly. When the price changes, whoever's in charge erases the old number and writes the new one — nobody expects the whiteboard to keep a history of every price it ever had; its only job is to show the current value, in the simplest, fastest-to-update way possible. If someone asks "what was the price a month ago?", the honest answer is "I have no way of knowing" — and in many contexts, that answer is perfectly acceptable, because nobody designed the whiteboard to answer that question.

SCD type 1 is exactly that whiteboard. It's fast to update — a single UPDATE, no new rows, no validity columns to maintain — and it's the right choice when the "current" value is all any consumer of the data needs. The cost, just as real, is that the erased whiteboard can't be reconstructed: once the old value disappears, it disappears forever.

Worked example: overwriting P002 with SCD type 1

Build dim_product_type1 with the usual four business columns — no validity column at all, because SCD type 1, by definition, doesn't need them — and apply P002's change with a direct UPDATE:

# scd_type1.py
import duckdb

from kiosko import DIM_PRODUCT

con = duckdb.connect()
con.execute("""
    CREATE TABLE dim_product_type1 (
        product_key  INTEGER,
        product_id   VARCHAR,
        product_name VARCHAR,
        category     VARCHAR,
        unit_cost    DOUBLE
    )
""")
con.executemany(
    "INSERT INTO dim_product_type1 VALUES (?, ?, ?, ?, ?)",
    [(i + 1, p["product_id"], p["product_name"], p["category"], p["unit_cost"])
     for i, p in enumerate(DIM_PRODUCT)],
)

print("=== dim_product_type1, BEFORE the change (in effect through 2026-08-14) ===")
print(con.sql("SELECT * FROM dim_product_type1 WHERE product_id = 'P002'"))

# P002's real change, effective 2026-08-15: SCD type 1 applies it with a single UPDATE,
# on the SAME row -- with no new row inserted.
con.execute("""
    UPDATE dim_product_type1
    SET category = 'health-snacks', unit_cost = 0.68
    WHERE product_id = 'P002'
""")

print("=== dim_product_type1, AFTER the change (in effect from 2026-08-15) ===")
print(con.sql("SELECT * FROM dim_product_type1 WHERE product_id = 'P002'"))

print("=== Trying to recover category/unit_cost from BEFORE 2026-08-15 ===")
print(con.sql("SELECT DISTINCT category, unit_cost FROM dim_product_type1 WHERE product_id = 'P002'"))

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

=== dim_product_type1, BEFORE the change (in effect through 2026-08-14) ===
┌─────────────┬────────────┬──────────────┬──────────┬───────────┐
│ product_key │ product_id │ product_name │ category │ unit_cost │
│    int32    │  varchar   │   varchar    │ varchar  │  double   │
├─────────────┼────────────┼──────────────┼──────────┼───────────┤
│           2 │ P002       │ Energy Bar   │ snacks   │       0.6 │
└─────────────┴────────────┴──────────────┴──────────┴───────────┘

=== dim_product_type1, AFTER the change (in effect from 2026-08-15) ===
┌─────────────┬────────────┬──────────────┬───────────────┬───────────┐
│ product_key │ product_id │ product_name │   category    │ unit_cost │
│    int32    │  varchar   │   varchar    │    varchar    │  double   │
├─────────────┼────────────┼──────────────┼───────────────┼───────────┤
│           2 │ P002       │ Energy Bar   │ health-snacks │      0.68 │
└─────────────┴────────────┴──────────────┴───────────────┴───────────┘

=== Trying to recover category/unit_cost from BEFORE 2026-08-15 ===
┌───────────────┬───────────┐
│   category    │ unit_cost │
│    varchar    │  double   │
├───────────────┼───────────┤
│ health-snacks │      0.68 │
└───────────────┴───────────┘

Stop at the last query, because it's this lesson's central evidence: SELECT DISTINCT category, unit_cost should, if history were preserved, return two rows — the before version and the after version. It returns one — health-snacks, 0.68 — because only one physical row exists for P002, and that row has already been overwritten. There is no SQL query, no matter how complex, capable of recovering snacks and 0.60 from this table: the UPDATE left no trace of what was there before. This isn't a code bug — it's exactly what SCD type 1 promises and delivers: the most recent row, with no memory of the past.

Diagram: one row, two moments, a single visible value

flowchart LR
    subgraph Antes["2026-08-01 through 2026-08-14"]
        A["product_key: 2\nproduct_id: P002\ncategory: snacks\nunit_cost: 0.60"]
    end

    subgraph Despues["2026-08-15 onward"]
        B["product_key: 2\nproduct_id: P002\ncategory: health-snacks\nunit_cost: 0.68"]
    end

    A -->|"UPDATE ... SET category = ..., unit_cost = ...\n(same row, same product_key)"| B
    A -.->|"snacks / 0.60 -- UNRECOVERABLE"| X["??? "]

    style X fill:#00000000,stroke-dasharray: 5 5

Going deeper: when SCD type 1 is the right decision, not a shortcut

It's tempting to read this lesson as "the wrong way to do it," and expect lesson 4 to replace it entirely. That would be a misreading. SCD type 1 isn't an incomplete version of SCD type 2 — it's a technique with its own legitimate use case, and Kimball documents it with the same level of formality as the others. The right question isn't "type 1 or type 2?" in the abstract, but "does this specific change deserve an auditable trail, or is it simply a correction of the current value?"

Think about the difference between two scenarios, both possible for P002. Scenario A — the one this lesson and lesson 4 model — Kiosko renegotiates the cost with its supplier and repositions the product into a new category. It's a real business fact, with an exact date, that a margins analyst might need to consult later ("what did P002 cost before the August renegotiation?"). That scenario justifies SCD type 2. Scenario B — which this lesson doesn't model, but which is just as common in practice — someone detects product_name had a typo from the start — say, an extra space or a wrong capital letter in the source system — and fixes it. That isn't a "change" in the business sense; it's a correction of data that should always have been right. Nobody needs a trail of "what the error looked like" — in fact, preserving it as a historical version would be confusing, because it would suggest the erroneous name was, at some point, a valid business value. That scenario is exactly what SCD type 1 exists for, and lesson 6 returns to this distinction with an explicit criterion, column by column.

SCD type 1's operational advantage, moreover, is real and shouldn't be underestimated: a table with no historization is simpler to maintain, takes up less space (one row per entity, not one per version), and any query against it is trivially correct — there's no need to worry about joining against the version in effect at a specific moment, because only one version exists. SCD type 2's cost — which lesson 4 is going to make explicit — isn't free: every historized column adds complexity to every query that uses it.

Common mistakes

Using SCD type 1 for a change that does need auditing, "because it's simpler." What happens: a team, under time pressure, decides to overwrite a real price or category change in place — like P002's in this lesson — without realizing that, months later, someone is going to need to reconstruct historical margin and won't be able to. Why it happens: SCD type 1 is genuinely easier to implement at first, and the cost of not having history only becomes visible once someone needs it and discovers it doesn't exist. How to spot it: if your financial-analysis or reporting team asks questions like "how much did this cost on such-and-such date?" and the answer is always "we don't know, it got overwritten," you have exactly this problem. How to fix it: before choosing type 1 for a column, explicitly ask yourself whether anyone is ever going to need the historical value — not "it's unlikely," but "I can guarantee it's never needed." If you can't guarantee it, use type 2 — reverting a type 1 to type 2 after real history has been lost is, literally, impossible.

Believing adding an updated_at column to dim_product_type1 "fixes" the loss of history. What happens: someone, uncomfortable losing all trace of the change, adds an updated_at column recording when the last modification happened, and assumes that's enough auditing. Why it happens: updated_at does answer "when did it last change?", which feels like progress. How to spot it: if you try to answer "what was the value before that update date?" using only updated_at, you realize the question still has no answer — updated_at tells you when something changed, never what it changed from. How to fix it: updated_at is useful for technical auditing (knowing something changed), but it doesn't replace business historization (knowing each version's complete values). That's precisely what SCD type 2 adds in the next lesson: not just a date, but the complete row of the previous version.

Applying the UPDATE on the wrong scope, without checking the WHERE first. What happens: someone writes UPDATE dim_product_type1 SET category = 'health-snacks', unit_cost = 0.68 without the WHERE product_id = 'P002' clause, and overwrites the category and cost of all four products, not just the one that changed. Why it happens: it's an easy omission to make under pressure, and in SCD type 1 — with no history to check afterward — the mistake goes unnoticed until someone notices P001 also, incorrectly, has category = 'health-snacks'. How to spot it: after any UPDATE on a dimension, check the affected row count and compare it against what you expected — an UPDATE meant for a single row that reports four rows modified is an immediate red flag. How to fix it: always check the rest of the table after an UPDATE, not just the row you changed — SELECT * FROM dim_product_type1 WHERE product_id != 'P002' should still show P001, P003, and P004's original values, exactly as in this lesson's exercise 1.

Exercises

Exercise 1 — Verify P001, P003, and P004 weren't affected by the UPDATE. After running the worked example, write a query confirming the other three products keep their original category and unit_cost values.

print(con.sql("""
    SELECT product_id, category, unit_cost
    FROM dim_product_type1
    WHERE product_id != 'P002'
    ORDER BY product_id
"""))

┌────────────┬─────────────┬───────────┐
│ product_id │  category   │ unit_cost │
│  varchar   │   varchar   │  double   │
├────────────┼─────────────┼───────────┤
│ P001       │ beverages   │       0.4 │
│ P003       │ beverages   │      0.35 │
│ P004       │ electronics │       2.1 │
└────────────┴─────────────┴───────────┘

Exercise 2 — Count how many rows dim_product_type1 has after the change, and explain why that number didn't change. Without looking at the table directly, predict the result of SELECT COUNT(*) FROM dim_product_type1 after the UPDATE, and explain in 1-2 sentences why that number is identical to before the change.

print(con.sql("SELECT COUNT(*) AS total_rows FROM dim_product_type1"))

┌────────────┐
│ total_rows │
│   int64    │
├────────────┤
│          4 │
└────────────┘

Exercise 3 — Argue whether product_name should be historized the same way as category and unit_cost. Suppose that, at some future point, Kiosko decides to rename P002 from "Energy Bar" to "Energy Bar Max" as part of a brand relaunch. In 2-3 sentences, argue whether that change should be treated with SCD type 1 or SCD type 2, using this lesson's "going deeper" criterion.

Summary and next step

This lesson applied SCD type 1 to P002's real change: a single UPDATE, no new rows, dim_product_type1 goes from snacks/0.60 to health-snacks/0.68 in place. You confirmed, with a query that can only return one value, that history was irrecoverably lost — and you understood, with this lesson's "going deeper" section, that this loss isn't a code defect, but the exact promise SCD type 1 delivers, and that in certain cases — data corrections, purely cosmetic changes — it's precisely what's needed.

Before moving on you should be able to: write a row's UPDATE for SCD type 1 from memory; explain why a dimension's row count under SCD type 1 never grows from an attribute change; and name at least one scenario where SCD type 1 is the right choice, not a shortcut.

Lesson 4 applies this same P002 change — same date, same two columns — with the technique that does preserve history: SCD type 2, with valid_from, valid_to, and is_current.

Resources

• Kimball Group — "Slowly Changing Dimension Type 1" — the official definition this lesson implements: "the old attribute value in the dimension row gets overwritten with the new value." kimballgroup.com/data-warehouse-business-intelligence-resources/kimball-techniques/dimensional-modeling-techniques/type-1. In English.
• DuckDB — official UPDATE statement documentation — the syntax reference for the operation this lesson uses. duckdb.org/docs/current/sql/statements/update. In English.