As-was or as-is: the slowly changing dimension types

Why can no single stored version answer both as-was and as-is, and what does each SCD type keep?

The constraint: one fact, two legitimate questions

A fact occurred at time T under attributes that were true at T. It is queried at a later time T′. Two legitimate questions exist, and they give different numbers:

• As-was: revenue by the region the customer was in when they bought.
• As-is: revenue by the region the customer is in today.

No single stored version of Lena can answer both. This is not an implementation limit. It is information-theoretic: if you keep one version, you have thrown away the information needed for the other question. Everything below is a different way to pay for keeping both.

The slowly changing dimension types

A slowly changing dimension (SCD) is a dimension whose attributes change occasionally. The "Type" numbers come from standard dimensional-modeling practice.

Type 1 — overwrite

Replace the old value with the new one. Only as-is survives. Every historical report silently restates on the next load. This is fine for a corrected typo in a name. It is catastrophic for a reorganized sales territory, and it is exactly what happened to Owen's report.

Type 2 — a new row per change

Insert a new row for each change, with an effective date range and a fresh surrogate key (a new meaningless id).

The fact table stores the surrogate key that was valid at load time (101 for the March sale, 102 for the September sale). So the natural join gives as-was. Filtering on is_current gives as-is:

-- As-was: join on the surrogate key stored in the fact
SELECT d.region, SUM(f.net_amount)
FROM fact_sales f JOIN dim_customer d ON d.customer_sk = f.customer_sk
GROUP BY d.region;

-- As-is: go through the natural key to the current version
SELECT cur.region, SUM(f.net_amount)
FROM fact_sales f
JOIN dim_customer v   ON v.customer_sk = f.customer_sk
JOIN dim_customer cur ON cur.customer_id = v.customer_id AND cur.is_current
GROUP BY cur.region;

Type 2 is the valid-time half of bitemporal data: it records when an attribute was true, not when the warehouse learned it, so it cannot reproduce a report made before a backdated correction.

Recognize the shape: an append-only substrate, an indirection layer (the surrogate key), and versions accumulating. That is the immutable-store law. Note what is missing: there is no collector. Versions grow without bound. Cleaning them is a storage-maintenance task, and it is usually nobody's job.

Type 3 — a "previous value" column

Add a previous_region column next to region. It is fixed width and keeps one step of history. Its real use case is narrow but valid: a reorganization where the business wants to report both ways at the same time during a transition period. You get as many alternate realities as you have columns.

Type 4 — a mini-dimension for fast-changing attributes

The derivation is arithmetic. In Type 2, the number of rows grows as member count × change rate. Worked example: dim_customer has 5 million members. A credit_band attribute changes on average 6 times per year. Type 2 adds 5,000,000 × 6 = 30 million new rows per year, six times the size of the dimension itself, caused by one volatile column.

Type 4 moves the volatile attributes into a small separate mini-dimension that the fact table references directly. If credit_band has 5 values and age_band has 4 values, the mini-dimension has only 5 × 4 = 20 rows. The fact stores a customer_key and a profile_key. The customer dimension no longer grows when someone changes band.

This is the rate-of-change split again, applied recursively to the thing the star already split once. It is also an instance of the variance law ("variance costs more than volume"): the problem is not how many members there are, but that a few attributes change all the time.

Type 6 — carry both answers on every row

Type 6 keeps both the as-was value and the as-is value on every Type 2 row (as region_at_time and region_current). Reads become trivial in either direction. Writes do not. When one attribute changes, the current-value column must be backfilled on every historical row of that member. A member with 12 versions needs 12 historical rows rewritten, plus one new row. That is write amplification proportional to the number of versions. It is the granularity-mismatch law (hardware version: you ask for 4 bytes and the system moves 64): the change is one logical fact, and the stored copies are one per version.

SCD type is a per-attribute decision

A correction to common practice: choose the SCD type per attribute, not per table. One customer dimension can hold a Type 1 email (history does not matter), a Type 2 territory (history matters) and a Type 4 credit_band (it changes too often for Type 2) at the same time. Choosing one type for the whole table is the common error. It is usually chosen by whoever built the loader, not by whoever will read the report.