Slowly Changing Dimensions

Scenario

Example company: dim_customer is refreshed nightly from the CRM by overwriting each customer's row. In March, a large customer moves from Berlin to Munich.

What you see in production

• The 2025 "sales by city" report, already sent to the board, now shows that customer's 2025 purchases under Munich.
• Sales commissions recomputed for last year move money between regional teams.
• Nobody can answer "where was this customer when they bought this?"

The naive code

UPDATE dim_customer SET city = :new_city WHERE customer_id = :id;    -- history is overwritten

Reproduce it

The same orders are reported by city, once with an overwritten (Type 1) dimension and once with a Type 2 dimension that keeps dated versions.

CREATE TABLE fct_sales (customer_id INTEGER, sale_date TEXT, amount INTEGER);
INSERT INTO fct_sales VALUES (7, '2025-11-10', 500), (7, '2026-04-02', 300);

-- Type 1: one row per customer, overwritten on change
CREATE TABLE dim_customer_t1 (customer_id INTEGER PRIMARY KEY, city TEXT);
INSERT INTO dim_customer_t1 VALUES (7, 'Berlin');
UPDATE dim_customer_t1 SET city = 'Munich' WHERE customer_id = 7;

-- Type 2: a new row per version, with a validity range
CREATE TABLE dim_customer_t2 (customer_key INTEGER PRIMARY KEY, customer_id INTEGER, city TEXT,
                              valid_from TEXT, valid_to TEXT);
INSERT INTO dim_customer_t2 VALUES
  (1, 7, 'Berlin', '2020-01-01', '2026-03-01'),
  (2, 7, 'Munich', '2026-03-01', '9999-12-31');

SELECT 'type 1' AS model, d.city, s.sale_date, s.amount
FROM fct_sales s JOIN dim_customer_t1 d ON d.customer_id = s.customer_id
UNION ALL
SELECT 'type 2', d.city, s.sale_date, s.amount
FROM fct_sales s JOIN dim_customer_t2 d
  ON d.customer_id = s.customer_id AND s.sale_date >= d.valid_from AND s.sale_date < d.valid_to
ORDER BY model, sale_date;

model | city | sale_date | amount
type 1 | Munich | 2025-11-10 | 500
type 1 | Munich | 2026-04-02 | 300
type 2 | Berlin | 2025-11-10 | 500
type 2 | Munich | 2026-04-02 | 300

Root cause

The dimension stored only the current value. Facts joined to it by the business key always see today's attributes, so history is rewritten every time an attribute changes.

The fix

Choose a slowly changing dimension (SCD) type per attribute:

• Type 1 (overwrite): for corrections and attributes where history doesn't matter (fixing a typo in a name).
• Type 2 (add a row): for attributes that affect reporting history (city, segment, sales region). Each version gets a surrogate key, valid_from, valid_to and often an is_current flag. Either store the surrogate key on the fact at load time, or join on the date range.
• Other types exist (Type 3 keeps a "previous value" column; Type 6 combines approaches); see Kimball's Type 2 technique.

Tools help: dbt snapshots build Type 2 tables automatically from a source table, using either an updated_at column or a comparison of selected columns.

Trade-offs

• Type 2 tables grow with every change and joins become date-range joins, which are slower and easier to get wrong.
• Choosing the type is a business decision ("should last year's sales move with the customer?"), not just a technical one.
• Snapshots only capture changes as often as they run; changes between runs are lost unless you use CDC.

Detect it in production

• Test that each business key has exactly one current row and that validity ranges don't overlap.
• Compare closed historical reports with their published values after each load.

Key points

• Overwriting dimension attributes rewrites history in every report.
• Type 2 keeps dated versions so facts join to the version valid at the time.
• Decide the SCD type per attribute with the business; test for overlapping ranges.