A customer moves from London to Manchester. Last quarter's regional sales report now shows different numbers. Why, and how do you stop it?

The cause is an overwrite, not a reporting bug

The report is doing exactly what it was told. fact_sales joins to dim_customer on the customer, dim_customer.region says Manchester, so every sale that customer ever made is counted in Manchester — including the ones made while they lived in London.

The dimension was updated in place. That is a type 1 change: the new value replaces the old, no history is kept, and every historical fact silently adopts the current attribute. It is not wrong in itself, and it is wrong here, because the report is asking a question about the past and the model can only answer about the present.

This class of question — the same query returning different numbers on different days with no new data — is one of the fastest ways to lose a business's trust in a warehouse, precisely because it looks like a bug in the report rather than a modelling decision made two years ago.

Type 1 and type 2, chosen by whether history matters

Type 1: overwrite. Correct when the old value was wrong rather than different. A misspelled name, a mistyped email, a data-entry error — nobody wants to preserve those, and restating history is the desired behaviour.

Type 2: version the row. Correct when the old value was true at the time. The customer really did live in London; the region on that sale is a fact about the sale, not only about the customer.

Type 2 keeps one row per version, with validity dates and a flag for the current one:

create table dim_customer (
  customer_key   bigint primary key,   -- surrogate: one per VERSION
  customer_id    text not null,        -- natural key: stable across versions
  name           text not null,
  region         text not null,
  valid_from     timestamptz not null,
  valid_to       timestamptz,          -- null while current
  is_current     boolean not null
);

-- customer_key  customer_id  region      valid_from   valid_to     is_current
-- 8801          C-4471       London      2023-02-01   2026-05-14   false
-- 9142          C-4471       Manchester  2026-05-14   null         true

The surrogate key is what makes this work, and it is the detail candidates most often miss. The fact table stores customer_key, not customer_id. A sale in March 2026 points at row 8801 and keeps pointing at it forever, so it reports under London no matter how many times the customer moves afterwards. Store the natural key on the fact and you are back to a type 1 join with extra tables.

Answering both questions from one model

The follow-up is usually "the business wants both" — sales by the region at the time, and sales by the customer's current region. A type 2 dimension gives you both without a second model.

-- As it was: join on the surrogate key the fact recorded.
select d.region, sum(f.amount)
from fact_sales f
join dim_customer d on d.customer_key = f.customer_key
group by d.region;

-- As it is now: hop through the natural key to the current version.
select cur.region, sum(f.amount)
from fact_sales f
join dim_customer d   on d.customer_key = f.customer_key
join dim_customer cur on cur.customer_id = d.customer_id and cur.is_current
group by cur.region;

Being able to write both, and to say which one a stakeholder means, is more useful than knowing the type numbers. The phrasing that resolves it in a meeting is to ask whether they want the answer to change when a customer moves.

What it costs

Type 2 is not free and pretending otherwise is a tell.

The dimension grows with changes, not with entities: a customer whose attributes change monthly produces twelve rows a year. On a dimension of millions of rows with volatile attributes, this becomes the largest table in the warehouse, which is why volatile attributes are often split into their own dimension — a mini-dimension — so that the stable attributes are not re-versioned every time a score changes.

Loading is harder. Each run must detect changed attributes, close the current row by setting valid_to, and insert a new one, all atomically. Getting it wrong produces overlapping validity ranges, which double-count in every query that joins on them, and the failure is invisible until a total looks slightly high.

And every consumer must know the rule. A select * from dim_customer now returns several rows per customer, and an analyst who does not filter on is_current gets duplicates. The conventional mitigation is a view exposing only current rows for people who want present-tense answers.

Ask before choosing

The interview answer that scores is not the taxonomy, it is the question you ask first: is a published figure allowed to change when this attribute changes?

If a stakeholder says sales by region should always reflect where the customer is today, type 1 is correct and the report is fine. If they expect last quarter's number to be reproducible, type 2 is required. Most organisations want different answers for different attributes on the same dimension, which is a legitimate outcome — region type 2, misspelled name type 1 — and writing that down per column is the deliverable.

The related discipline is to state the grain of every fact table in one sentence before modelling anything. "One row per order line per shipment" makes the join keys obvious and prevents the double-count that comes from joining two facts at different grains. Most modelling arguments turn out to be two people assuming different grains.

If the old value was wrong, overwrite it. If it was true at the time, version it — and have the fact point at the version rather than at the entity.