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,
customer_id text not null,
name text not null,
region text not null,
valid_from timestamptz not null,
valid_to timestamptz,
is_current boolean not null
);
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.
select d.region, sum(f.amount)
from fact_sales f
join dim_customer d on d.customer_key f.customer_key
d.region;
cur.region, (f.amount)
fact_sales f
dim_customer d d.customer_key f.customer_key
dim_customer cur cur.customer_id d.customer_id cur.is_current
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.
© 2026 Preptima. Originally published at preptima.com (opens in a new tab).