ModelingGENERALSOURCE-SPECIFICORG-SPECIFIC
Slowly Changing Dimensions
A customer moves from Poland to Germany. Do last quarter's Polish revenue figures change? That question, answered per attribute, is the whole topic.
Who needs this, what one row is, and why the obvious build breaks
Every lesson starts from the consumer, because designing from the source outward is this domain's characteristic mistake.
The question
When a descriptive attribute changes, should history be restated to the new value or preserved as it was?
Who needs this
Anyone who will re-run a report for a past period and expect the same answer they got last time. Finance and audit care most, because a number that changes after it was signed off is not a data-quality issue to them — it is a control failure.
What one row is
The choice here decides the dimension's grain. Type 1 keeps one row per entity. Type 2 makes it one row per entity version, which changes every join, every distinct count and every test in the model (Grain: What Does One Row Represent?).
The obvious build
Refresh the dimension by overwriting it from the source each night. The dimension always matches the source, the load is a simple replace, and nobody has to reason about validity intervals.
Why it breaks
A customer moves Poland → Germany. The overwrite reassigns every order they ever placed to Germany, so last quarter's Polish revenue drops and Germany's rises, retroactively, for a period that was already reported.
How it breaks with real data
- A customer moves Poland → Germany. The overwrite reassigns every order they ever placed to Germany, so last quarter's Polish revenue drops and Germany's rises, retroactively, for a period that was already reported.
- A sales rep changes territory. Their historical quota attainment moves with them, and the commission calculation for a closed quarter no longer reproduces.
- A product's category is corrected. The year-on-year comparison in the board deck no longer matches the one shown last month, and nobody can explain why because no pipeline failed (Dimension Tables).
- An auditor asks why the Q1 figure in the filed report differs from the same query run today. The honest answer — "we overwrite dimension attributes" — is not an acceptable one (Audit Logs for Privileged Actions).
- The team switches everything to Type 2 in response, doubling the dimension, breaking every existing join that assumed one row per customer, and making distinct-customer counts wrong overnight (SCD Type 2 in Practice).
Source→Ingestion→Raw→Transformation→Validation→Storage Model→Serving→Consumer→Observability
What is actually happening
- Type 0 — never change. The attribute is fixed at creation and any later source change is ignored or rejected. Original signup date, original acquisition channel, date of birth. Rare, and valuable exactly because it is unambiguous.
- Type 1 — overwrite. The attribute holds the current value and history is not kept. Every historical query is answered with today's value. This is correct for corrections: a misspelled name was always wrong, and restating history to the fixed spelling is what you want.
- Type 2 — new row per version, with validity intervals and a current flag. The dimension's grain becomes one row per version, facts join to the version that was current when they occurred, and history is preserved. This is correct for real-world changes: the customer genuinely lived in Poland then and genuinely lives in Germany now (SCD Type 2 in Practice).
- Type 3 — add a column. Keep
countryandprevious_countryon the same row. Cheap, keeps one row per entity, and holds exactly one step of history. Useful for a specific known question — "compare before and after the re-segmentation" — and useless as a general history mechanism. - The decision rule is a question about the world, not about the data: was the old value wrong, or was it right at the time? A typo was always wrong: Type 1. A move happened: Type 2. Getting this backwards is what produces both silently restated history and dimensions that are three times larger than they need to be.
- The types are chosen per attribute, not per table. A single
dim_customernormally has Type 0 columns, Type 1 columns and Type 2 columns side by side, and only the Type 2 ones cause a new row when they change (Dimension Tables).
Poland to Germany: the question in one example
SIMPLIFIEDPractitioners also use hybrid forms — Type 6 combines 1, 2 and 3 so a row carries both the historical value and the current value of the same attribute, which lets one query ask "revenue by where they lived then" and "revenue by where they live now" from the same dimension.
A customer signed up in Warsaw in 2024 and placed orders throughout the year. In March 2026 they move to Berlin and update their address. The source system does what source systems do: it runs an UPDATE and the row now says Germany.
The nightly dimension load overwrites country. Nothing fails. Tomorrow, someone re-runs the 2024 revenue-by-country report and Poland is lower than it was, Germany is higher, and the totals still match. Every check in the platform passes, because the platform is internally consistent — it is consistent with a version of history that has been quietly rewritten.
Whether that is a bug depends entirely on the question being asked. "Where do our customers live?" wants the new value applied everywhere. "Where was the revenue earned?" wants the old value preserved. Both are legitimate; the model can only encode one per column, and that is the decision.
The table below lays out the four types against this one change. Read the last column: it is what a consumer experiences, which is the only column that ever generates a complaint.
| Type | What happens on the change | Dimension grain | Good for | What a consumer sees |
|---|---|---|---|---|
| Type 0 — retain original | Nothing. The source change is ignored or flagged as an exception. | One row per entity | Facts about origin: signup date, acquisition channel, original plan. | The value never moves. If the source is genuinely correcting an error, the dimension is now knowingly stale and someone must handle the exception. |
| Type 1 — overwrite | country becomes DE. The old value is gone from the warehouse. | One row per entity | Corrections. A misspelling was always wrong; restating history to the fixed value is what you want. | Every historical report reassigns this customer to Germany. Last quarter's numbers change, silently, and no job failed. |
| Type 2 — new version | The PL row is closed at the change date; a DE row opens. Facts keep their original key. | One row per entity VERSION | Real-world changes where history must reproduce: address, segment, territory, tier. | 2024 revenue stays in Poland. 2026 revenue is in Germany. Re-running last quarter gives the same answer it gave last quarter (SCD Type 2 in Practice). |
| Type 3 — previous-value column | previous_country becomes PL, country becomes DE. One row still. | One row per entity | One specific before/after comparison, usually around a known re-segmentation. | Both values are available for exactly one step. The next move discards PL with no warning, and a chain of three changes is unrepresentable. |
What goes wrong with each choice
Both directions of the mistake are expensive and they fail differently. Choosing Type 1 for a real change loses information permanently and silently. Choosing Type 2 for a correction adds a version that describes a state the world was never in, which is noisier but recoverable.
That asymmetry is worth stating as a rule: when genuinely unsure, version. Merging two versions later is a repair; recovering an overwritten value is often impossible. This inverts the usual instinct to start simple, and it is the right inversion here because one option destroys information and the other does not.
The rows below are all real failure shapes. Notice that in four of the five, the pipeline is completely healthy throughout.
SCD choices and how each fails
| Trigger | Symptom | Cause | Response |
|---|---|---|---|
| Type 1 applied to a genuine relocation, re-segmentation or territory change. | A closed period's numbers differ from what was reported. Totals still reconcile; the split across the attribute has moved. | Historical facts join to the current dimension row, so a Type 1 attribute is retroactive to all of history by construction. | Promote the attribute to Type 2. Rebuild history from retained change data if it exists; if it does not, versioning starts today and that gap is documented rather than hidden (SCD Type 2 in Practice). |
| Type 2 applied to a spelling correction or a data-entry fix. | A version boundary appears on a date when nothing happened, and per-version counts show a customer "changing" for no business reason. | The merge treats any attribute difference as a change, because it cannot distinguish a correction from an event without being told. | Merge the spurious versions and rekey affected facts. Then classify the column properly, and where possible take corrections from a source signal that marks them as such (What a CDC Event Contains). |
| Type 3 used to "keep history" on an attribute that changes repeatedly. | The previous-value column holds the second-most-recent value and nothing older. Nobody notices, because the column is always populated. | Type 3 is a fixed-width history of exactly one step. Each change overwrites the prior value. | Type 2 if history matters at all. Type 3 is for a single known before-and-after comparison and should be named after that comparison, not called history. |
| Type 2 applied to a status flag that changes several times a day. | The dimension grows faster than the fact table and eventually stops broadcasting, slowing every query in the star. | The attribute is not slowly changing. Versioning a rapidly changing attribute is an event log with a dimension's schema. | Move it onto the fact as a degenerate attribute, or into a mini-dimension of distinct attribute combinations that the fact references (Dimension Tables). |
| Every attribute set to Type 2 "to be safe". | Dimension row count multiplies, distinct-customer counts silently become distinct-version counts, and every existing query that counted customers is now wrong. | Type 2 changes the dimension's grain. Everything that assumed one row per entity is affected, including tests written before the change (Grain: What Does One Row Represent?). | Version per attribute, not per table. Add an is_current filter to entity-level counts, and re-express distinct-entity metrics against the natural key rather than the surrogate key (Surrogate Keys). |
Promoting an attribute to Type 2 is a breaking change
Teams usually discover SCD2 because a report changed retroactively, and the instinct is to fix it by versioning the column. That is the right fix and it is not a small one: it changes the dimension's grain, and grain changes break consumers (Grain: What Does One Row Represent?).
The schema diff below is what the change actually looks like. Three columns appear and one property disappears — the guarantee that customer_id is unique. Every query that counted customers, every join that assumed one match, and every test written against the old grain is affected, and almost none of them will error.
This is why the promotion is planned rather than shipped: build the versioned dimension alongside, provide an is_current view with the old shape so existing consumers keep working, migrate them deliberately, and only then retire the view (Impact Analysis).
dim_customer, promoted from Type 1 to Type 2 on `country` and `segment`
Before
- customer_key BIGINT (one per customer)
- customer_id VARCHAR (unique)
- name VARCHAR
- country VARCHAR
- segment VARCHAR
- email VARCHAR
After
- customer_key BIGINT (one per customer VERSION)
- customer_id VARCHAR (NO LONGER UNIQUE)
- name VARCHAR
- country VARCHAR (SCD2)
- segment VARCHAR (SCD2)
- email VARCHAR (SCD1)
- valid_from TIMESTAMP
- valid_to TIMESTAMP NULL
- is_current BOOLEAN
change Version country and segment. The dimension's grain moves from one row per customer to one row per customer version; three columns are added and no column is removed or retyped.
| Consumer | Effect | How it shows up |
|---|---|---|
| A query joining on `customer_id` instead of `customer_key` | Matches every version of the customer, multiplying every fact row by that customer's version count. Revenue rises for exactly the customers who changed. | Silently — no error, wrong result |
| `COUNT(DISTINCT customer_key)` used as "number of customers" | Now counts versions. A customer who moved twice counts three times, so the metric drifts upward over time in a way that looks like growth. | Silently — no error, wrong result |
| A BI tool with `dim_customer` modelled as one-row-per-customer | Generates joins that fan out, with no SQL for anyone to review. The dashboards keep rendering. | Silently — no error, wrong result |
| A dbt-style uniqueness test on `customer_id` | Fails on the first load after promotion. The loudest and most useful consumer in this list. | Loudly — it raises |
| A fact load that resolves `customer_key` by joining on `customer_id` alone | Matches several versions and either fails on a duplicate-key insert or multiplies fact rows, depending on the sink. | Loudly — it raises |
| An `is_current = TRUE` view preserving the old shape | Continues to work unchanged, which is what makes a staged migration possible instead of a flag day. | Loudly — it raises |
How to build it
Most important first.
- Go through the dimension column by column and label each one Type 0, 1, 2 or 3, with a one-line reason. This takes an hour and is the artefact the whole topic reduces to (Dataset Documentation).
- Default to Type 1 and promote to Type 2 only where a historical report would be wrong otherwise. Type 2 on every column triples the dimension and makes every query harder for no benefit (SCD Type 2 in Practice).
- Ask the consumer, not the engineer. "If this changes, should last quarter's number change?" is a question finance can answer in seconds and a data engineer cannot answer at all.
- Distinguish corrections from changes at the source if you can. A CDC stream tells you a value changed; only the business can tell you whether it was a fix (What a CDC Event Contains).
- Where a Type 2 attribute changes very frequently, consider moving it out of the dimension entirely and onto the fact or into a mini-dimension, because a version per day is not a slowly changing dimension any more (Fact Tables).
- Whichever type you choose, write it where consumers see it. A Type 1 attribute that a consumer assumes is Type 2 produces a wrong answer with full confidence (The Data Catalog).
What this actually promises
Naming the guarantee you do not have is worth more than naming the one you do — everything downstream inherits the weakest promise in the chain.
- Type 1 guarantees the dimension matches the source right now, and explicitly guarantees that historical reports are not reproducible across an attribute change.
- Type 2 guarantees reproducibility of historical reports for the versioned attributes, and only if the fact load resolved keys against event time rather than current state (Surrogate Keys).
- Type 3 guarantees exactly one prior value. The second change overwrites the first prior value, silently, and nothing warns you (SCD Type 2 in Practice).
- No type guarantees you know when the change actually happened in the real world. You know when the source told you, which can be days later and is a different fact (Late-Arriving Data).
Can I trust it?
A green pipeline is evidence that code ran. These four fields are the evidence that the data is right.
The check that would catch this
- For Type 1, the check is a reconciliation against the source: every attribute matches current source state. It is easy and it deliberately proves nothing about history.
- For Type 2, the checks are interval integrity — exactly one current row per entity, no overlapping intervals, no gaps — and they are the subject of the next lesson because they are where the real failures live (SCD Type 2 in Practice).
- Across both, add an as-at spot check: pick a handful of facts from a past period, resolve their dimension attributes, and compare against what the source's change history says was true at that instant. This is the only check that verifies the type was implemented as intended rather than merely consistently (Reconciliation).
- What all of them miss: whether the type was the right choice. A Type 1 attribute that consumers believe preserves history passes every test, matches the source perfectly, and answers every historical question with today's value. Nothing internal to the platform can detect a correct implementation of a wrong decision — only a consumer noticing that last quarter's number moved (Trusting Data).
Freshness
- Type 1 is the freshest: the dimension is the source's current state as of the last load, with no interval logic.
- Type 2 introduces a subtlety — the validity interval is bounded by *detection* time, not by the time the change happened. A daily-batch SCD2 dimension cannot distinguish two changes on the same day, so the intermediate version simply never existed as far as the model is concerned (CDC vs Polling).
- Late-arriving dimension changes require retroactively splitting an existing version and rekeying the facts that fall in the corrected interval, which is one of the more expensive repairs in this module (Late-Arriving Data).
When the schema or meaning changes
- Promoting an attribute from Type 1 to Type 2 is a grain change to the dimension. Existing joins that assumed one row per entity now fan out, and distinct-entity counts become distinct-version counts (Grain: What Does One Row Represent?).
- History cannot be reconstructed at promotion time unless the change history was retained somewhere — a CDC stream, a raw snapshot archive, or an audit table in the source. Without one, versioning starts from today and everything before it is a single version (Keeping Raw History: The Recovery Position and the Liability).
- Demoting Type 2 to Type 1 discards history irreversibly. It should require the same review as dropping a table, because that is what it is.
- Adding a new Type 2 attribute to an existing SCD2 dimension increases version count going forward and leaves history at the old granularity, so version boundaries mean different things before and after (Semantic Changes).
How to re-run this safely
- A wrongly applied Type 1 overwrite is recoverable only from a retained source-change history. If CDC or snapshot archives exist, the versions can be reconstructed; if not, the old value is gone from every system (Change Data Capture).
- A wrongly applied Type 2 — versioning a correction — leaves a spurious version. It is repaired by merging the two versions and rekeying the affected facts, which is fiddly but reversible (Planning a Backfill).
- Because Type 1 destroys information and Type 2 merely adds it, the asymmetry argues for versioning when genuinely unsure and merging later. That is the opposite of the usual "start simple" advice and it is correct here.
What can go wrong
Failure modes
- Type 1 applied to a real-world change, silently restating signed-off historical reports.
- Type 2 applied to a correction, creating a spurious version so that history splits at a date where nothing happened.
- Type 3 used as a general history mechanism, quietly losing the second-oldest value on every subsequent change.
- Type 2 chosen for an attribute that changes daily, producing a dimension larger than the fact it describes (SCD Type 2 in Practice).
- The mitigation failing: a per-attribute type policy documented once and never enforced, so a new column is added with whatever behaviour the merge statement happened to give it.
Misreads
- "SCD2 is the correct answer and SCD1 is a shortcut." SCD1 is correct for corrections. Versioning a typo means the model claims the customer was genuinely called
Jhonuntil Tuesday. - "We should make everything Type 2 to be safe." That multiplies the dimension, changes its grain, breaks every distinct-entity count, and buys history for columns nobody ever reports on historically (Grain: What Does One Row Represent?).
- "Type 3 gives us history." It gives you one prior value, and only the most recent one. The second change silently discards the first.
- "CDC gives us SCD2 automatically." CDC gives you the change events. Turning them into non-overlapping validity intervals with exactly one current row is a modelling job with its own failure modes (What a CDC Event Contains).
- "The dimension matches the source, so it is correct." That is precisely the Type 1 guarantee, and it is exactly what makes historical reports irreproducible (Operational vs Analytical Models).
Privacy, retention and access
- Preserving history and honouring deletion requests pull in opposite directions: SCD2 keeps every version of a person's attributes, which is more personal data retained for longer, in more rows (Deletion Requests).
- The usual resolution is to version business attributes and to treat directly identifying fields as Type 1 with a separate, shorter retention — so history remains analysable after identity is removed (Data Minimization).
- A dimension that versions an attribute like health status, ethnicity or political affiliation has created a longitudinal record that needs classification and access control at the column level, not just at the table level (Data Classification).
Operating it
How you see it in production
- Version creation rate per attribute per day. A column producing far more versions than expected is either misclassified or genuinely not slowly changing (Pipeline Metrics).
- Count of entities with more than one current row — must be zero, checked every load (The Data Quality Dashboard).
- A diff of a past period's headline metric across two runs. If a report for a closed month changes between Monday and Tuesday, a Type 1 attribute moved underneath it, and this is the cheapest detector for that (Two Dashboards, Two Numbers).
What changes at 10x and 100x
- At 10x entities, Type 2 dimensions grow proportionally and the merge becomes the slowest part of the dimension load.
- At 100x, or with high change rates, mini-dimensions — splitting fast-changing attributes into their own small dimension referenced by the fact — become the standard answer, because versioning the whole customer row for a frequently changing flag is wasteful (Dimension Tables).
- More consumers means the type policy must be published rather than known. An undocumented Type 1 attribute is a trap that scales linearly with the number of analysts (The Data Catalog).
What drives cost here
- Type 1 costs nothing beyond the dimension itself. Type 2 costs one row per version per entity, which multiplies dimension size by the average number of changes (SCD Type 2 in Practice).
- The real cost of Type 2 is join strategy: a versioned dimension can cross the broadcast threshold, which changes the cost of every query in the star at once (Broadcast Joins).
- Type 2 also costs a merge rather than a replace on every load, which is more expensive and more failure-prone than overwriting a small table (Upserts and Merges).
What this approach costs
- Type 2 buys reproducible history and costs dimension size, merge complexity, a grain change and a validity predicate in every load. It is worth it only for attributes historical reports actually use.
- Type 1 buys simplicity and current-state accuracy and costs the ability to reproduce any past report that touched the attribute. That cost is invisible until an auditor asks.
- Deciding per attribute buys a dimension that is as small as it can be and costs an explicit, documented decision per column — which is real work and is the work.
Modeling lab — one grain, ten questions
Change an input and watch which number moves — and which one does not. Everything here comes from a model in this repository, not from a measurement.
Modeling lab — one grain, ten questions
Pick the grain of the fact table. The questions do not change; what the table can honestly say about them does.
Fact grain
The transaction and its total. The individual products are gone.
Answered
3
With care
1
Confidently wrong
4
Unanswerable
2
4 of these questions get an answer at this grain that is wrong, and none of them raise an error. That is the whole difficulty: the unanswerable ones announce themselves, and these do not.
| Business question | At this grain | Why |
|---|---|---|
What was revenue by country last month? Wants: One order, or one order line — either works, provided the measure is additive at that grain and is not summed twice. | answered | The measure is additive at this grain and each order is counted once. |
What is the average order value? Wants: One order. An average over lines answers a different question entirely. | answered | The denominator is orders, which is exactly what one row is. |
What was revenue by customer country at the time of each order? Wants: One order, joined to the version of the customer that was current when the order was placed. | WRONG no history | With a Type 1 dimension every historical order is attributed to the customer's current country. A customer moving from Poland to Germany silently rewrites last year's regional reports, and last month's report no longer reproduces. |
What is net revenue after refunds? Wants: One order, with refunds either netted into the measure or held as a separate signed fact at the same grain. | answered | Refunds net into the measure, or sit beside it as a signed fact at the same grain. |
What was the total account balance on each day last year? Wants: One account-day. A balance is a state, not an event, and cannot be reconstructed by summing transactions unless every transaction since account opening is retained. | WRONG | Summing transactions per day gives the daily change in balance, not the balance. The chart has the right shape and the wrong y-axis. |
What is month-three retention by signup cohort? Wants: One user-month of activity, joined to the user's signup month. | WRONG | Users who were active but did not buy are invisible, so retention is understated by exactly the non-buyers. |
What share of sessions ended in a purchase? Wants: One session — which requires a session window over events, because no source system emits a session. | unanswerable | No source system emits a session. Without a session window over events there is no denominator to divide by. |
Which products are most often bought together? Wants: One order line, with the order key retained so lines can be grouped back into baskets. | unanswerable | The most instructive failure in this lab: the model is not wrong, it is at the wrong resolution, and no query can recover what was aggregated away. |
What was yesterday's revenue, asked at 06:00 this morning? Wants: One order, in a period that is not yet closed. | with care | The grain is right and the period is not closed. Orders that happened yesterday and arrive later today are still missing at 06:00. |
What was global revenue, across markets that bill in different currencies? Wants: One order, with both the transaction amount and the converted amount stored, plus the rate and the date the rate applied. | WRONG no history | Converting at query time with today's rate makes every historical report change daily. Converting once with no record of the rate makes the number unreproducible. Both pass every type check there is. |
answeredThe grain is the thing the question is about.
with careIt works, and there is one specific way to get it wrong.
WRONGIt returns a plausible number that is not the answer, and nothing raises.
unanswerableThe resolution needed was aggregated away. No query recovers it.
SIMPLIFIEDA single fact table against ten questions. A real model has several, and a question that one answers badly another may answer exactly — which is the argument for more than one fact table, not for a finer one.
Where this applies
Almost nothing here is universal. These labels say what each claim is specific to, and where a different engine, format, warehouse or scale would differ.
- GENERALThe question "was the old value wrong or right at the time" is about the business, not about any technology, and the four types are the four possible answers. Every warehouse, lakehouse and hand-built model faces the same choice.
- SOURCE-SPECIFICWhether you can build history retroactively depends entirely on the source: a database with a retained WAL or binlog lets you reconstruct past versions, a source with an application-level audit table gives you the business intent as well, and a SaaS API exposing only current records makes retroactive versioning impossible no matter what the model wants.
- ORG-SPECIFICWhich attributes need versioning is a finance, audit and product question rather than a technical one — the same
segmentcolumn may need SCD2 in a company that pays commission on it and SCD1 in one that does not, and no engineer can decide that from the schema.
Where the depth lives
This domain teaches how data moves and how you know it arrived intact. It hands the rest off by name.
Domains that do not exist yet
- — Distributed Systems owns why the time a change was observed is not the time it happened, which is the reason an SCD2 validity interval records detection rather than truth.