Slowly Changing Dimensions, Explained Without the Jargon

The report that changes its own history

A sales rep moves from the Dhaka team to the Chattogram team in June. In July, someone runs the regional performance report for April — and April's numbers have moved. Their deals, closed months earlier in Dhaka, now count towards Chattogram.

Nobody wrote a bug. The dimension table simply stores one row per rep with their current team, so every historical query gets today's answer. That's the problem slowly changing dimensions solve, and the jargon around them hides how simple the underlying idea is.

Two questions, two different answers

Before choosing a technique, decide which question the business is asking. There are only two, and confusing them is the actual source of trouble.

"How is the Chattogram team doing?" — meaning the team as it exists today, including deals its members closed elsewhere. This is the current view.

"What did Chattogram sell in April?" — meaning the team as it was in April. This is the historical view.

Both are legitimate. They give different numbers, and neither is wrong. What's wrong is a model that can only answer one while people believe it's answering the other.

Callout: Ask the stakeholder directly: "when a rep changes team, should past months move with them?" People answer that question easily. They cannot answer "do you want Type 1 or Type 2?" — so don't ask it that way.

Type 1: overwrite, and forget

Store one row per entity. When something changes, update it in place.

-- dim_sales_rep
rep_id | rep_name    | team         | updated_at
101    | A. Rahman   | Chattogram   | 2026-06-14

The June change overwrote Dhaka. History is gone — not archived, gone. Every query, for every period, returns Chattogram.

This is the right choice more often than purists admit. Use it for attributes where the past genuinely doesn't matter: a corrected spelling of someone's name, a fixed typo in a product description, an email address. Nobody wants a report sliced by "customers whose name used to be misspelled."

Use it also when nobody has asked for history. Type 2 costs real complexity, and building it speculatively is a common way to make a model harder than it needs to be.

Type 2: a new row per version

Keep one row per entity per version, each with a validity window and a flag marking the current one.

-- dim_sales_rep
rep_key | rep_id | rep_name  | team       | valid_from | valid_to   | is_current
1       | 101    | A. Rahman | Dhaka      | 2024-01-01 | 2026-06-14 | false
2       | 101    | A. Rahman | Chattogram | 2026-06-14 | 9999-12-31 | true

Three things make this work, and all three are easy to get subtly wrong.

rep_key is a surrogate key, unique per row. rep_id is the business key and now appears more than once. Your fact table must reference rep_key, not rep_id — that's the whole mechanism. A fact row points at the version of the rep that was true when the fact occurred.

The validity window is half-open. valid_from is inclusive, valid_to is exclusive. Notice that version 1 ends on the same date version 2 begins. That's intentional: it means a >= valid_from and < valid_to comparison matches exactly one row, with no gaps and no overlaps. Using inclusive-inclusive windows and setting valid_to to the day before creates off-by-one bugs at every boundary and breaks entirely if you later need intraday changes.

9999-12-31 rather than null for the open-ended row. A null makes every range comparison need an or valid_to is null clause, which people forget. A far-future sentinel makes the comparison uniform. Some teams prefer null on principle; be consistent either way, because mixing them guarantees bugs.

Querying it without tripping over yourself

For the current view, filter to current rows:

select r.team, sum(f.deal_value) as revenue
from fct_deals f
join dim_sales_rep r on r.rep_key = f.rep_key
where r.is_current = true
group by r.team;

Wait — that's wrong, and it's the single most common Type 2 mistake. Filtering is_current after joining on rep_key discards every deal that points at a historical version. Deals A. Rahman closed before June vanish entirely, so your total is lower than reality.

To get the current view, join on the business key instead:

select r.team, sum(f.deal_value) as revenue
from fct_deals f
join dim_sales_rep r on r.rep_id = f.rep_id and r.is_current = true
group by r.team;

For the historical view — what was true at the time — join on the surrogate key and drop the filter:

select r.team, sum(f.deal_value) as revenue
from fct_deals f
join dim_sales_rep r on r.rep_key = f.rep_key
group by r.team;

That's the payoff. Store both keys on the fact table and you can answer either question with a one-line change, from one dimension.

Building it in dbt

dbt has this built in as a snapshot. You don't hand-roll the versioning:

{% snapshot snap_sales_rep %}
{{
  config(
    target_schema='snapshots',
    unique_key='rep_id',
    strategy='check',
    check_cols=['team', 'manager_id', 'seniority'],
  )
}}
 
select rep_id, rep_name, team, manager_id, seniority
from {{ source('crm', 'sales_reps') }}
 
{% endsnapshot %}

Run dbt snapshot on a schedule and dbt maintains dbt_valid_from and dbt_valid_to for you, adding a row whenever any column in check_cols changes.

The strategy choice matters. Use check with an explicit check_cols list when you want to version specific columns — this is usually what you want, because it ignores churn in columns you don't care about. Use timestamp with an updated_at column when the source reliably maintains one; it's cheaper, since dbt compares one field rather than several.

Two operational cautions. Snapshots only see what's there when they run — if a rep changes team twice between runs, you capture the last state and the intermediate version is lost forever. And check_cols='all' will version on any change at all, including meaningless ones, which produces enormous snapshot tables. Name your columns.

Type 3 and Type 6, briefly

Type 3 adds a previous_team column alongside team. It answers "what was it before?" without full history. Occasionally useful, rarely worth it — you get exactly one step back and no dates.

Type 6 combines the others: a Type 2 row structure plus a Type 1 column carrying today's value on every historical row. Concretely, each row has both team (as at the time) and current_team (as of now). This lets one join answer both questions without switching keys, at the cost of rewriting every historical row whenever an attribute changes. Worth knowing about; reach for it when analysts keep getting the join wrong, because it makes the correct query harder to get wrong.

Choosing, in practice

Start at Type 1. Move to Type 2 for a specific attribute when someone asks a question that needs it — and they will ask about team, segment, region, price tier, or account owner, because those drive how performance is judged.

Do not snapshot everything by default. Every Type 2 dimension makes queries harder to write correctly, and a wrong query on a correct model is worse than a right query on a simple one.

The one thing to get right from the start is the fact table: store both the surrogate key and the business key on every fact row. It costs one integer column, and it means you can add history to a dimension later without rebuilding your facts. That single decision is the difference between a half-day change and a month-long migration.