Slowly Changing Dimensions (SCD) Types Explained

Dimensions describe the context of facts: the customer's region, the product's category, the employee's team. That context changes. Customers move, products are reclassified, accounts are reassigned. A slowly changing dimension, SCD, is the rule a warehouse uses to handle such a change, and the choice decides whether last year's reports still say what they said last year.

The main types

The Kimball Group catalogues types from 0 to 7 (Kimball Group: techniques); four cover most real designs:

Type 1 overwrites the old value, so it always reflects the most recent assignment and, in Kimball's words, destroys history (Kimball Group: Type 1). Type 2 adds a new row with the updated values, marked by an effective date, an expiration date and a current row indicator (Kimball Group: Type 2). Type 3 keeps the previous value in an extra column while overwriting the main one, and is used relatively rarely (Kimball Group: Type 3).

Type 1 versus Type 2 on real data

The difference is easiest to see in a report. The Chinook sample database assigns every customer a support representative (Chinook on GitHub). Suppose that on 1 July 2012 all Brazilian customers are reassigned to Steve Johnson, and the business then asks for 2012 revenue by representative. The script builds both kinds of customer dimension, applies the change to each, and runs the same report through both:

import shutil
import sqlite3
import urllib.request
from pathlib import Path

url = "https://github.com/lerocha/chinook-database/releases/download/v1.4.5/Chinook_Sqlite.sqlite"
if not Path("chinook.sqlite").exists():
    urllib.request.urlretrieve(url, "chinook.sqlite")
shutil.copy("chinook.sqlite", "work.sqlite")
con = sqlite3.connect("work.sqlite")

con.executescript("""
-- Type 1: one row per customer, overwritten on change
CREATE TABLE dim_customer_t1 AS SELECT CustomerId, Country, SupportRepId FROM Customer;

-- Type 2: one row per version, with a surrogate key and a validity range
CREATE TABLE dim_customer_t2 (customer_sk INTEGER PRIMARY KEY, CustomerId INTEGER, Country TEXT, SupportRepId INTEGER,
                              valid_from TEXT, valid_to TEXT, is_current INTEGER);
INSERT INTO dim_customer_t2 (CustomerId, Country, SupportRepId, valid_from, valid_to, is_current)
SELECT CustomerId, Country, SupportRepId, '2009-01-01', '9999-12-31', 1 FROM Customer;
""")

# on 1 July 2012 every Brazilian customer is reassigned to Steve Johnson (employee 5)
CHANGE_DATE, NEW_REP = "2012-07-01", 5
with con:
    con.execute("UPDATE dim_customer_t1 SET SupportRepId = ? WHERE Country = 'Brazil'", (NEW_REP,))
    changed = [r[0] for r in con.execute("""SELECT CustomerId FROM dim_customer_t2
                                            WHERE Country = 'Brazil' AND is_current = 1 AND SupportRepId <> ?""", (NEW_REP,))]
    con.execute(f"""UPDATE dim_customer_t2 SET valid_to = date(?, '-1 day'), is_current = 0
                    WHERE is_current = 1 AND CustomerId IN ({','.join('?' * len(changed))})""", (CHANGE_DATE, *changed))
    con.executemany("""INSERT INTO dim_customer_t2 (CustomerId, Country, SupportRepId, valid_from, valid_to, is_current)
                       VALUES (?, 'Brazil', ?, ?, '9999-12-31', 1)""", [(c, NEW_REP, CHANGE_DATE) for c in changed])
print("customers whose rep changed:", len(changed))
print("dimension rows: type 1", con.execute("SELECT COUNT(*) FROM dim_customer_t1").fetchone()[0],
      "| type 2", con.execute("SELECT COUNT(*) FROM dim_customer_t2").fetchone()[0])

# 2012 revenue by support rep, as each design reports it
report = """
SELECT e.FirstName || ' ' || e.LastName AS rep, ROUND(SUM(i.Total), 2) AS revenue_2012
FROM Invoice i
JOIN {dim} d ON d.CustomerId = i.CustomerId {extra}
JOIN Employee e ON e.EmployeeId = d.SupportRepId
WHERE strftime('%Y', i.InvoiceDate) = '2012'
GROUP BY rep ORDER BY rep"""
type1 = con.execute(report.format(dim="dim_customer_t1", extra="")).fetchall()
type2 = con.execute(report.format(dim="dim_customer_t2",
                                  extra="AND date(i.InvoiceDate) BETWEEN d.valid_from AND d.valid_to")).fetchall()
print(f"{'rep':<16}{'type 1':>10}{'type 2':>10}")
for (rep, t1), (_, t2) in zip(type1, type2):
    print(f"{rep:<16}{t1:>10.2f}{t2:>10.2f}")
print("totals equal:", round(sum(r[1] for r in type1), 2) == round(sum(r[1] for r in type2), 2))
con.close()

customers whose rep changed: 4
dimension rows: type 1 59 | type 2 63
rep                 type 1    type 2
Jane Peacock        130.76    130.76
Margaret Park       171.46    188.29
Steve Johnson       175.31    158.48
totals equal: True

What the result shows

Four customers changed representative, and the Type 2 dimension grew from 59 rows to 63: one expired and one current row for each. The Type 1 dimension still has 59, with the old values gone.

The reports disagree. Under Type 1, every 2012 invoice from those customers is credited to Steve Johnson, including purchases made between January and June while Margaret Park was responsible for some of them. Type 1 moves 16.83 of revenue from Margaret to Steve that he did not look after. Under Type 2, each invoice joins to the version of the customer that was valid on the invoice date, so revenue stays with whoever was responsible at the time. The totals are identical; only the attribution differs, which is exactly why this kind of error goes unnoticed. If commission were paid from this report, Type 1 would pay the wrong person.

Neither answer is wrong in the abstract. "Revenue of the customers Steve looks after now" is a Type 1 question; "revenue earned by each rep" is a Type 2 question. Deciding which questions the business asks is how you choose the type per attribute.

Loading a Type 2 dimension

Each load compares incoming records with the current rows and, for every tracked attribute that changed:

1. Expire the current row: set its end date to the day before the change and clear the current flag.
2. Insert a new row with a new surrogate key, the new values, a start date and the current flag.
3. Leave unchanged customers alone, and insert new customers with an open-ended end date.

In a production warehouse, fact rows usually store the surrogate key of the dimension row current when the fact was loaded, rather than joining on dates at query time as the example does for clarity. Either way, the rule is the same: every fact belongs to exactly one version.

Choosing the type attribute by attribute

SCD type is decided per attribute, not per table. A customer dimension might treat email and phone as Type 1, since nobody reports on old email addresses; region, segment and account manager as Type 2, since revenue must stay with the region or manager at the time; and signup date as Type 0. Write the decision down for every attribute, agree it with the people who own the reports, and review it when new reports appear, because a question the business starts asking next year may need history you only have if you started keeping it now.

Mistakes that corrupt history

• Overlapping or gapped validity ranges, so a fact matches two versions or none. Test that each business key has exactly one current row and contiguous ranges.
• Tracking volatile attributes as Type 2, such as a last-login timestamp, which multiplies rows for no analytical value. Use Type 1 or move them to a separate table.
• Late-arriving changes applied with today's date instead of the real effective date, which misattributes facts in between; handling late-arriving data covers backdating.
• Forgetting downstream aggregates: after a Type 1 overwrite, Kimball warns that aggregate tables and cubes must be recomputed.

Where SCDs fit in overall dimension design is covered in designing fact and dimension tables and data modelling: star, snowflake and data vault.

Data modelling techniques, star and snowflake schemas and data warehouse concepts are part of the data warehousing module of the Data Engineering course.

Sources

• Type 1: overwrite (Kimball Group) — Kimball Group
• Type 2: add new row (Kimball Group) — Kimball Group
• Type 3: add new attribute (Kimball Group) — Kimball Group
• Dimensional modeling techniques (Kimball Group) — Kimball Group
• Chinook sample database (GitHub) — GitHub