The model was right and the business changed. Add an attribute that needs history, split a dimension in two, or move a fact to a finer grain — without rewriting every downstream query on the same day. Each before-and-after pair states whether it preserves the existing report or deliberately corrects it, and that claim is verified.
It is the most common evolution in a warehouse and the naive version breaks every existing query on the day it ships.
Say this
Add the Type 2 columns, seed one open version per existing customer keeping the existing surrogate keys, and keep serving the old shape until consumers move. Existing reports join on `customer_sk` and still resolve to exactly one row, so their numbers do not change — verified below.
The reasoning
The instinct is to rebuild the dimension with a row per version, which reassigns surrogate keys and silently repoints every fact row in the warehouse. That is a data incident, not a migration. The safe version keeps every existing `customer_sk` exactly as it is and adds `valid_from`, `valid_to` and `is_current` alongside.
Seeding matters. Each existing customer gets one version, open-ended, with a `valid_from` far enough back to cover all existing facts. At that moment the dimension is Type 2 in structure and still has exactly one row per customer, so every existing join returns one row and every existing number is unchanged — which is what the verification below asserts.
History then accumulates from the change forward. That is worth stating explicitly to stakeholders, because it is the part people misunderstand: converting to Type 2 does not recover the history you did not keep. January's region is gone if the overwrite already happened, and the best you can offer is that it will not happen again.
Consumers move afterwards, one at a time. A report that wants as-was switches to the as-of predicate; a report that wants as-is adds `is_current = 1`. Until then they keep working because there is only one version per customer, which buys you the time to migrate forty reports without a flag day.
After the migration same answer, verified
After: Type 2 columns added, existing surrogate keys kept, one open version per customer.
dim_customerdimension
customer_skintPK
customer_idtextNK
regiontext
valid_fromdateSCD2
valid_todateSCD2
is_currentboolSCD2
one row per customer per version (Type 2, seeded with one version each)
2 seeded rows
fct_orderfact
order_idtextDD
customer_skintFK
order_daydateFK
amountnummeasure
one row per order
2 seeded rows
fct_order.customer_sk→ many-to-onedim_customer.customer_skexisting keys preserved, so the old query still resolves to the same rows
The same existing report, unchanged
SELECT d.region, SUM(f.amount) AS revenue
FROM fct_order f JOIN dim_customer d ON d.customer_sk = f.customer_sk
GROUP BY d.region ORDER BY d.region;
Now returns
The model
After: Type 2 columns added, existing surrogate keys kept, one open version per customer.
dim_customerdimension
customer_skintPK
customer_idtextNK
regiontext
valid_fromdateSCD2
valid_todateSCD2
is_currentboolSCD2
one row per customer per version (Type 2, seeded with one version each)
2 seeded rows
fct_orderfact
order_idtextDD
customer_skintFK
order_daydateFK
amountnummeasure
one row per order
2 seeded rows
fct_order.customer_sk→ many-to-onedim_customer.customer_skexisting keys preserved, so the old query still resolves to the same rows
SELECT d.region, SUM(f.amount) AS revenue
FROM fct_order f JOIN dim_customer d ON d.customer_sk = f.customer_sk
GROUP BY d.region ORDER BY d.region;
region
revenue
EU
100
US
50
The answer most people give
"Rebuild the dimension with full history from the source." Only if the source kept it — most operational systems overwrite too. And rebuilding reassigns surrogate keys, which repoints every fact in the warehouse at the wrong rows.
They’ll ask next
A report needs January by January's region. Can you give it that after this migration?
`credit_band` changes monthly for every customer and it is sitting in a Type 2 customer dimension that has grown twelve times in a year. What is the migration?
The model today
Before: one dimension, with a fast-changing attribute inside it.
dim_customerdimension
customer_skintPK
customer_idtextNK
regiontext
credit_bandtextchanges monthly — would explode a Type 2 dimension
one row per customer, mixing stable and volatile attributes
It is the Type 4 mini-dimension in the situation that motivates it, and the migration has to preserve every existing report while changing where an attribute lives.
Say this
Move it to a mini-dimension keyed from the fact. `dim_customer` keeps the stable attributes, `dim_credit_band` holds the distinct bands, and the fact carries `credit_band_sk` captured at transaction time. Reports grouping by region are untouched — verified below.
The reasoning
Type 2 on a monthly-changing attribute means a new customer row every month for every customer, regardless of whether anything anyone reports on changed. The dimension grows with the volatility of its worst column, and every as-of join gets slower for reports that only ever wanted region.
A Type 4 split moves the volatile attribute into its own small dimension — one row per distinct band, not per customer — and puts the key on the *fact*. That captures the band at the moment of the transaction, which is usually the question anyway ('what band were they in when they ordered'), and it does so without versioning the customer at all.
The migration is additive: create the mini-dimension, add the key column to the fact, backfill it from the customer versions that were valid at each fact's date, and only then drop the attribute from the customer dimension. Reports that group by region never touch the moved column, so they are unaffected throughout — which is what the check below confirms.
The cost is one more join for reports that want band, and one more thing to keep conformed. The gain is a customer dimension that grows with real change, which makes every other as-of query cheaper. The deciding question is whether the attribute is genuinely a property of the customer or of the transaction; if the latter, this was the right model all along.
After the migration same answer, verified
After: a Type 4 split. The customer dimension is stable; the band is captured per order.
fct_order.credit_band_sk→ many-to-onedim_credit_band.credit_band_skthe volatile attribute moved to a mini-dimension keyed from the fact
SELECT d.region, SUM(f.amount) AS revenue
FROM fct_order f JOIN dim_customer d ON d.customer_sk = f.customer_sk
GROUP BY d.region ORDER BY d.region;
region
revenue
EU
100
US
50
The answer most people give
"Leave it and add an index." The problem is row multiplication, not lookup speed — the dimension is twelve times larger than it needs to be, and every as-of join scans more versions to find the one it wants.
They’ll ask next
You need the band as it was when the customer was onboarded, not when they ordered. Does the mini-dimension still work?
An order-grain fact must become line-grain so product analysis is possible. Dozens of reports read the order-grain table. How do you do it?
The model today
Before: order grain. The business now wants revenue by product.
fct_orderfact
order_idtextPK
customer_skintFK
amountnummeasure
one row per order
2 seeded rows
What the existing report returns
total_revenue
220
Why they ask this
Changing grain is the migration people call impossible, and the expand-migrate-contract answer with a compatibility view is what makes it routine.
Say this
Build the line-grain fact alongside, then replace the old table with a view that aggregates the new one back to order grain. Existing reports keep working unchanged — total revenue is 220 before and after — and move to the line fact at their own pace.
The reasoning
The reason grain changes feel impossible is that people picture a cut-over: new table, all reports migrated on the same day. Framed that way it is a coordination problem across every consumer, which is why it never happens and the coarse table stays forever.
The compatibility view removes the coordination. Build `fct_order_line` from source, verify it reconciles to the existing order-grain totals, then drop the old physical table and recreate the name as a view that sums the lines back to order grain. Every existing query resolves against the view, returns the same numbers, and does not need to know anything changed — the check below runs the old query before and after and gets 220 both times.
The reconciliation before the swap is the step that must not be skipped: the new fact aggregated to order grain has to equal the old fact, row for row, for the whole history. If it does not, either the new load is wrong or the old table was — and finding out which *before* the swap is the entire safety of the approach.
Afterwards the view is a deprecation surface. Instrument it to see who is still reading it, move those consumers to the line fact, and drop the view when the last one leaves. That is expand-migrate-contract, and the view is what turns a flag day into a backlog item.
After the migration same answer, verified
After: the fact moved to line grain and the old shape is kept as a view.
fct_order_linefact
order_idtextDD
line_noint
product_skintFK
amountnummeasure
one row per order line
3 seeded rows
vw_orderstaging
order_idtextPK
amountnummeasureSUM over the lines
one row per order — a view preserving the old contract
2 seeded rows
fct_order_line.order_id→ many-to-onevw_order.order_idthe old order-grain table becomes a view over the new line-grain fact
The same existing report, unchanged
SELECT SUM(amount) AS total_revenue FROM vw_order;
Now returns
The model
After: the fact moved to line grain and the old shape is kept as a view.
fct_order_linefact
order_idtextDD
line_noint
product_skintFK
amountnummeasure
one row per order line
3 seeded rows
vw_orderstaging
order_idtextPK
amountnummeasureSUM over the lines
one row per order — a view preserving the old contract
2 seeded rows
fct_order_line.order_id→ many-to-onevw_order.order_idthe old order-grain table becomes a view over the new line-grain fact
SELECT SUM(amount) AS total_revenue FROM vw_order;
total_revenue
220
The answer most people give
"Keep both tables and load them separately." Two loads from the same source diverge — one picks up a fix the other misses — and then the order table and the line table disagree with nobody able to say which is right.
They’ll ask next
The line fact does not reconcile to the old order fact for 2023. What do you do before swapping the view in?
Your product dimension is keyed on the vendor SKU and the vendor is renumbering all of them next quarter. What happens, and what should have been in place?
The model today
Before: the vendor's SKU is the join key, and the vendor is about to change it.
dim_productdimension
skutextPKvendor SKU used as the key
categorytext
one row per product
2 seeded rows
fct_salesfact
skutextFK
amountnummeasure
one row per sale
2 seeded rows
fct_sales.sku→ many-to-onedim_product.skuthe vendor is renumbering every SKU next quarter
What the existing report returns
category
revenue
tools
100
toys
50
Why they ask this
It is the concrete cost of joining on a natural key, and the migration is a good test of whether the candidate protects history as well as the join.
Say this
Every fact row referencing the old SKU breaks. Introduce a surrogate key now, repoint facts at it, and keep both the new and legacy SKU as attributes — then the renumbering is an update to one dimension column instead of a rewrite of every fact.
The reasoning
With the natural key as the join key, a vendor renumbering invalidates every fact row that references it. The options are all bad: rewrite the entire fact history, maintain a translation table that every query must know about, or accept that history is now unjoinable.
The migration is the standard one. Add `product_sk` to the dimension, populate it, add the column to the fact and backfill it by joining on the current SKU, then switch reports to the surrogate join and drop the SKU from the fact. The verification below shows the same revenue by category before and after, because nothing about the data changed — only what the fact points at.
Keeping `legacy_sku` alongside the new one is what preserves traceability. A support question about an old order, a reconciliation against a vendor statement from last year, an audit — all need to get from the old identifier to the current row, and a column is the cheapest possible way to allow it.
After the change the renumbering is genuinely uneventful: update `sku` on the affected dimension rows, leave `product_sk` alone, and no fact row is touched. That is the property a surrogate key buys, and this is the clearest situation in which to explain it.
After the migration same answer, verified
After: a surrogate key absorbs the change; both vendor SKUs are kept as attributes.
dim_productdimension
product_skintPK
skutextNKnew vendor SKU
legacy_skutextNKkept for traceability
categorytext
one row per product
2 seeded rows
fct_salesfact
product_skintFK
amountnummeasure
one row per sale
2 seeded rows
fct_sales.product_sk→ many-to-onedim_product.product_skthe fact references a surrogate, so a vendor renumbering touches one dimension row
The same existing report, unchanged
SELECT d.category, SUM(f.amount) AS revenue
FROM fct_sales f JOIN dim_product d ON d.product_sk = f.product_sk
GROUP BY d.category ORDER BY d.category;
Now returns
The model
After: a surrogate key absorbs the change; both vendor SKUs are kept as attributes.
dim_productdimension
product_skintPK
skutextNKnew vendor SKU
legacy_skutextNKkept for traceability
categorytext
one row per product
2 seeded rows
fct_salesfact
product_skintFK
amountnummeasure
one row per sale
2 seeded rows
fct_sales.product_sk→ many-to-onedim_product.product_skthe fact references a surrogate, so a vendor renumbering touches one dimension row
SELECT d.category, SUM(f.amount) AS revenue
FROM fct_sales f JOIN dim_product d ON d.product_sk = f.product_sk
GROUP BY d.category ORDER BY d.category;
category
revenue
tools
100
toys
50
The answer most people give
"Do a find-and-replace on the fact table when the renumbering lands." Rewriting the largest table you own to absorb a vendor decision — and any fact loaded during the rewrite is written with the wrong key.
They’ll ask next
Two old SKUs merge into one new SKU. What does your dimension look like, and what happens to the facts?
Sales and returns each built a product dimension with different category names. Merging them will change published numbers. How do you proceed?
The model today
Before: each mart built its own product dimension.
dim_product_salesdimension
product_skintPK
skutextNK
categorytextsales team's categories
one row per product (sales mart)
2 seeded rows
dim_product_returnsdimension
product_skintPK
skutextNK
categorytextreturns team's categories
one row per product (returns mart)
2 seeded rows
fct_salesfact
product_skintFK
amountnummeasure
one row per sale
2 seeded rows
fct_sales.product_sk→ many-to-onedim_product_sales.product_sktwo dimensions, two category vocabularies, no way to combine the marts
What the existing report returns
sales_category
returns_category
revenue
hand tools
tools
100
toys
toys
50
Why they ask this
The technical migration is easy and the hard part is that it corrects numbers people have already used, which is a communication problem the candidate has to name.
Say this
Build one conformed dimension, agree the category vocabulary with both teams, and repoint both facts. It is *not* non-breaking — 'hand tools' becomes 'tools' and every report grouped by the old label changes. Announce it as a correction with a dated cut-over.
The reasoning
Two dimensions with different vocabularies cannot be combined, which is why sales and returns have never appeared in one report. The fix is one conformed product dimension, which then lets both facts be drilled across — the corrected query below returns sold and returned side by side for the first time.
The migration must not be presented as invisible. The verification records this one as *corrects-the-number*, because 'hand tools' and 'tools' were two labels for one category and the merged model reports only one. Every dashboard grouped by the sales vocabulary changes on the day of the switch, and pretending otherwise is how trust is lost.
So the sequence is as much social as technical: get both teams to agree the vocabulary before any code, publish the mapping from old labels to new, give a dated cut-over, and keep the old labels available as an attribute for a deprecation period so anyone can trace a historical report. The mapping is the artefact that makes the change auditable.
The structural lesson worth stating is that this is the cost of not having done conformance planning at the start. A bus matrix would have surfaced that both processes needed product before either was built, and the negotiation would have happened once, cheaply, instead of after two marts shipped.
After the migration answer corrected, verified
After: one conformed product dimension serving both facts.
fct_returns.product_sk→ many-to-onedim_product.product_skone conformed dimension, so the two facts can be drilled across
The same existing report, unchanged
SELECT d.category,
(SELECT SUM(s.amount) FROM fct_sales s JOIN dim_product p ON p.product_sk = s.product_sk
WHERE p.category = d.category) AS sold,
(SELECT SUM(r.amount) FROM fct_returns r JOIN dim_product p ON p.product_sk = r.product_sk
WHERE p.category = d.category) AS returned
FROM dim_product d GROUP BY d.category ORDER BY d.category;
Now returns
The model
After: one conformed product dimension serving both facts.
fct_returns.product_sk→ many-to-onedim_product.product_skone conformed dimension, so the two facts can be drilled across
SELECT d.category,
(SELECT SUM(s.amount) FROM fct_sales s JOIN dim_product p ON p.product_sk = s.product_sk
WHERE p.category = d.category) AS sold,
(SELECT SUM(r.amount) FROM fct_returns r JOIN dim_product p ON p.product_sk = r.product_sk
WHERE p.category = d.category) AS returned
FROM dim_product d GROUP BY d.category ORDER BY d.category;
category
sold
returned
tools
100
20
toys
50
5
The answer most people give
"Keep both dimensions and add a mapping table between them." That is a second definition plus a translation layer, so every cross-process query has to go through it and any new category has to be mapped twice.
They’ll ask next
Which team owns the conformed dimension afterwards, and who approves adding a category?
A January order was loaded in March and keyed to the customer version current in March. The regional report is wrong. How do you fix it, and what does that change?
The model today
Before: a late-arriving fact resolved against whichever version was current at load time.
dim_customerdimension
customer_skintPK
customer_idtextNK
regiontext
valid_fromdateSCD2
valid_todateSCD2
one row per customer per version
2 seeded rows
fct_orderfact
order_idtextDD
customer_skintFKresolved against the current version
order_daydateFK
amountnummeasure
one row per order
1 seeded row
fct_order.customer_sk→ many-to-onedim_customer.customer_ska January order loaded in March was keyed to March's version
What the existing report returns
region
january_revenue
US
100
Why they ask this
Late-arriving facts against Type 2 dimensions are a real and under-discussed failure, and the fix deliberately restates a published number.
Say this
Re-resolve the surrogate key as-of the *event* date rather than the load date, and backfill the affected rows. That moves 100 of January revenue from US to EU — a correction, not a non-breaking change, so it has to be announced.
The reasoning
The load resolved the dimension version that was current when the row was processed, not when the event happened. For an on-time fact those are the same; for a late one they are not, and the fact is permanently attributed to the wrong version. The report shows January revenue under US when the customer was in EU at the time.
The fix in the load is to resolve as-of the event date: look up the version whose validity window contains `order_day`, not the one where `is_current = 1`. That is the same as-of predicate the query-side questions are about, applied at load time, and it makes the fact row self-consistent forever afterwards.
The backfill is the second half and needs a scope. Any fact loaded more than zero days after its event date is suspect, so identify them by comparing event date to load timestamp — which is a good argument for storing the load timestamp on every fact — and re-resolve just those. The check below confirms the number moves from US to EU.
Because it changes published figures it is a correction, and it deserves the same treatment as any restatement: quantify the impact before running it, announce which periods move and by how much, and keep the before-and-after available. A silent backfill that shifts last quarter's regional revenue is indistinguishable from a bug.
After the migration answer corrected, verified
After: the same late fact, keyed to the version that was valid when it happened.
dim_customerdimension
customer_skintPK
customer_idtextNK
regiontext
valid_fromdateSCD2
valid_todateSCD2
one row per customer per version
2 seeded rows
fct_orderfact
order_idtextDD
customer_skintFKresolved as-of the order day
order_daydateFK
amountnummeasure
one row per order
1 seeded row
fct_order.customer_sk→ many-to-onedim_customer.customer_skthe load resolves the version that was valid on the event date, not at load time
The same existing report, unchanged
SELECT d.region, SUM(f.amount) AS january_revenue
FROM fct_order f JOIN dim_customer d ON d.customer_sk = f.customer_sk
GROUP BY d.region ORDER BY d.region;
Now returns
The model
After: the same late fact, keyed to the version that was valid when it happened.
dim_customerdimension
customer_skintPK
customer_idtextNK
regiontext
valid_fromdateSCD2
valid_todateSCD2
one row per customer per version
2 seeded rows
fct_orderfact
order_idtextDD
customer_skintFKresolved as-of the order day
order_daydateFK
amountnummeasure
one row per order
1 seeded row
fct_order.customer_sk→ many-to-onedim_customer.customer_skthe load resolves the version that was valid on the event date, not at load time
SELECT d.region, SUM(f.amount) AS january_revenue
FROM fct_order f JOIN dim_customer d ON d.customer_sk = f.customer_sk
GROUP BY d.region ORDER BY d.region;
region
january_revenue
EU
100
The answer most people give
"It is one order, leave it." The one order is the one you found. Anything loaded late has the same defect, and without the load timestamp you cannot even measure how many there are.
They’ll ask next
What column would you need on the fact to find every row affected by this? Do you have it?
Finance needs tax reported separately from revenue. The fact has one `amount` column that everyone uses. What is the safe change?
The model today
Before: one measure. Finance now needs tax reported separately.
fct_orderfact
order_idtextPK
amountnummeasure
one row per order
2 seeded rows
What the existing report returns
revenue
160
Why they ask this
Additive changes look trivial and go wrong when someone redefines an existing column instead of adding a new one — which is a silent semantic break.
Say this
Add `tax_amount` and `gross_amount` as new columns and leave `amount` exactly as it is. Every existing query returns the same number — verified below. The unsafe version is redefining `amount` to include tax, which changes every report without changing any of their SQL.
The reasoning
Adding a column is the safest change available: nothing that does not reference it can be affected, and consumers adopt it when they need it. The verification runs the existing revenue query before and after and gets 160 both times, which is exactly the property you want to be able to state.
The dangerous version is the one that looks tidier — redefine `amount` to be gross and let net be derived. That changes the meaning of a column that forty queries already use, so every one of them silently starts reporting a different number with no code change anywhere. A semantic change to an existing column is a breaking change even though the schema is compatible.
Backfill is the detail to get right. New columns are NULL for existing rows until populated, and `SUM` ignores NULLs while `AVG` and any arithmetic involving them do not — so a report doing `amount + tax_amount` gets NULL for old rows rather than an error. Either backfill before exposing the column, or default it and document the effective date.
The naming carries the contract: `amount` stays net because that is what it has always meant, and `gross_amount` says what it is. Renaming `amount` to `net_amount` would be clearer and is a breaking change, so it belongs in the same expand-migrate-contract sequence as anything else — add the alias, move consumers, retire the old name.
After the migration same answer, verified
After: columns added, the existing one untouched, so every old query is unaffected.
fct_orderfact
order_idtextPK
amountnummeasureunchanged: still net of tax
tax_amountnummeasurenew, NULL for rows loaded before the change
gross_amountnummeasure
one row per order
2 seeded rows
The same existing report, unchanged
SELECT SUM(amount) AS revenue FROM fct_order;
Now returns
The model
After: columns added, the existing one untouched, so every old query is unaffected.
fct_orderfact
order_idtextPK
amountnummeasureunchanged: still net of tax
tax_amountnummeasurenew, NULL for rows loaded before the change
gross_amountnummeasure
one row per order
2 seeded rows
SELECT SUM(amount) AS revenue FROM fct_order;
revenue
160
The answer most people give
"Redefine `amount` to include tax and tell people." Telling people does not update their SQL. Every existing report changes its number on the day of the deploy, which is a break with extra steps.
They’ll ask next
Old rows have NULL tax. Which existing aggregations change behaviour, and which do not?
The fact carries both a denormalised `region_name` from years ago and a `region_sk` pointing at the dimension that replaced it. They already disagree. How do you retire the old one?
The model today
Before: a legacy denormalised column alongside the dimension key that replaced it.
fct_orderfact
order_idtextPK
amountnummeasure
region_nametextdenormalised onto the fact years ago
region_skintFKadded later; the dimension is now authoritative
one row per order
2 seeded rows
dim_regiondimension
region_skintPK
regiontext
one row per region
1 seeded row
fct_order.region_sk→ many-to-onedim_region.region_sktwo sources of truth for region, and they already disagree
What the existing report returns
region
revenue
EU
60
Europe
100
Why they ask this
Deprecation is the half of schema evolution nobody plans, and this one also has to correct a number, because the two sources of truth had already drifted.
Say this
Instrument the column to find who reads it, migrate those consumers to the dimension join, then drop it. It is a correction rather than a non-breaking change — the legacy column says 'Europe' and 'EU' for the same region, so the totals consolidate from two rows to one.
The reasoning
Two columns describing the same thing is two sources of truth, and this one has already failed: the same region is spelled two ways, so any report grouping by `region_name` shows split totals that a report grouping by the dimension does not. That is the argument for retiring it — not tidiness, but that it is actively wrong.
The sequence is instrument, migrate, drop. Find the readers before touching anything: query logs, dbt lineage or view definitions, depending on the platform. Guessing which consumers exist is how a deprecation becomes an outage, and it is the step most often skipped because it is the least interesting.
The migration is per consumer, and each one changes its number — 'Europe' and 'EU' merge into one row of 160 where there were two rows of 100 and 60. That means it is a correction, so each consumer needs telling rather than silently repointing, and a dashboard that has been quoting a split figure needs its owner to know why the number moved.
Only then drop the column, and prefer a two-step: rename it to `region_name_deprecated` first, wait a cycle, then drop. The rename breaks any consumer you missed loudly and immediately, which is far kinder than a drop that surfaces days later — and it is trivially reversible.
After the migration answer corrected, verified
After: the legacy column removed, once every consumer had moved to the dimension.
fct_orderfact
order_idtextPK
amountnummeasure
region_skintFK
one row per order
2 seeded rows
dim_regiondimension
region_skintPK
regiontext
one row per region
1 seeded row
fct_order.region_sk→ many-to-onedim_region.region_skone source of truth for region
The same existing report, unchanged
SELECT d.region, SUM(f.amount) AS revenue
FROM fct_order f JOIN dim_region d ON d.region_sk = f.region_sk
GROUP BY d.region ORDER BY d.region;
Now returns
The model
After: the legacy column removed, once every consumer had moved to the dimension.
fct_orderfact
order_idtextPK
amountnummeasure
region_skintFK
one row per order
2 seeded rows
dim_regiondimension
region_skintPK
regiontext
one row per region
1 seeded row
fct_order.region_sk→ many-to-onedim_region.region_skone source of truth for region
SELECT d.region, SUM(f.amount) AS revenue
FROM fct_order f JOIN dim_region d ON d.region_sk = f.region_sk
GROUP BY d.region ORDER BY d.region;
region
revenue
EU
160
The answer most people give
"Just drop it — everything already uses the dimension key." Everything you know about. The rename-then-drop step exists precisely to find the consumer nobody remembered, at a moment when reversing takes seconds.
They’ll ask next
You rename the column and something breaks in a system you did not know existed. Was that a failure or a success?
Name the general pattern for changing a live model without a flag day, and what each phase is for.
Why they ask this
It is the framework the other migration answers are instances of, and being able to name it is the difference between having done migrations and having a method for them.
Say this
Expand: add the new structure alongside the old and dual-write. Migrate: move consumers one at a time, verifying each. Contract: remove the old structure once nothing reads it. The value is that every phase is individually reversible.
The reasoning
**Expand.** Add the new column, table or dimension without removing anything, and populate it in the same load that maintains the old one. Both are correct and consistent at the same time. This phase changes nothing for consumers and can be rolled back by dropping what you added.
**Migrate.** Move consumers to the new structure individually, verifying each against the old one before and after. Where the change is meant to be non-breaking, that verification is a comparison of the same report's output — which is exactly what the checks in this bank do. Where it corrects a number, the comparison quantifies the correction so it can be announced.
**Contract.** Once nothing reads the old structure, remove it — ideally by renaming first so a missed consumer fails immediately and reversibly, then dropping a cycle later. This is the phase teams skip, which is how warehouses accumulate columns nobody dares delete and a fact table with three ways to get to region.
What makes the pattern work is that the risky step — changing what a consumer reads — is taken once per consumer rather than once for everybody. It converts a coordination problem into a backlog, and it means an incident affects one report rather than all of them.
The prerequisite is knowing who your consumers are. Without lineage or query logs the migrate phase is guesswork, which is why instrumenting access is worth doing before the first migration rather than during it.
The answer most people give
"Version the table — `fct_order_v2` — and let people move." That is expand and migrate with no contract, so both versions live forever, both must be loaded, and they eventually disagree. A version number in a table name is a deprecation you have not planned.
They’ll ask next
Which phase is the one your team actually skips, and what does the warehouse look like after five years of skipping it?
You have migrated a fact to a new grain. What evidence would you want before switching consumers over?
Why they ask this
'We tested it' is the weak answer. The strong one is a specific reconciliation with a stated tolerance, run over the full history rather than a sample.
Say this
A row-by-row reconciliation of the new model aggregated to the old grain against the old table, over the entire history, with a tolerance of exactly zero for counts and keys and a stated rounding tolerance for money. Anything else is a spot check.
The reasoning
The reconciliation to run is the one that mirrors what consumers will experience: aggregate the new structure back to the old grain and compare it to the old table, per key rather than in total. A matching grand total hides offsetting errors — a customer gaining what another lost — and per-key comparison does not.
The tolerance has to be stated per column type. Counts and keys must match exactly; there is no acceptable difference in a row count. Money may legitimately differ by rounding if the migration introduced an allocation, in which case the tolerance is a stated number of minor units per row and the *total* must still tie exactly.
It has to cover the full history, not the last month. Migrations break on the old data — a schema that changed in 2022, a source that used a different code list, a period where a load ran twice — and those are exactly the periods nobody thinks to test. Running over everything is cheap compared with discovering it after the cut-over.
Two things worth adding to the evidence pack: the list of rows that did not reconcile with an explanation for each (there are usually a handful, and 'we know why' is very different from 'we did not look'), and the reconciliation kept as a scheduled test during the dual-running period, so drift between the two structures is caught while both still exist.
The answer most people give
"The totals match, so it is fine." Totals hide offsetting errors and say nothing about distribution. Two customers swapping 500 of revenue leaves the total identical and every customer-level report wrong.
They’ll ask next
Three rows out of 40 million do not reconcile. Do you ship? What would you need to know first?
An upstream system adds three columns and renames one. Your pipeline did not fail. Is that good?
Why they ask this
Silent schema drift is a governance question dressed as a modelling one, and the good answer distinguishes changes you can absorb from changes you must be told about.
Say this
No. Not failing means nothing noticed — the renamed column is now silently NULL or dropped, depending on how you read the source. Additions are safe to absorb; renames and type changes are breaking and should fail loudly at ingest.
The reasoning
The three additions are genuinely harmless if your ingest selects columns explicitly: new columns are ignored until someone chooses to use them. That is the argument for never using `SELECT *` at the boundary — it makes additions a no-op rather than a schema change that ripples through every downstream table.
The rename is the problem, and it fails differently depending on how you read. Selecting explicitly means the old name is missing and you either error — good — or, with a permissive reader, get NULLs for a column that used to have values. That second case is the dangerous one: the pipeline succeeds, the measure quietly becomes NULL, and a `SUM` reports a smaller number rather than failing.
So the response is a schema check at ingest that classifies changes rather than a pipeline that tolerates everything. Additions: log and continue. Renames, removals and type changes: fail the load and alert, because they need a human decision about whether the meaning changed as well as the name.
The durable fix is a data contract with the producing team: an agreed schema, a versioning policy, and a commitment that breaking changes are announced rather than deployed. That converts this from something your pipeline discovers into something you are told about — which is the only version that scales past a handful of sources.
The answer most people give
"Use `SELECT *` so new columns flow through automatically." Then a source-side addition changes your table's schema without review, a rename silently repositions data, and the warehouse shape is controlled by a team that does not know it is doing so.
They’ll ask next
Which of the three additions would you actually want to know about, and why not all of them?
A bug meant three months of revenue were loaded at 90% of their true value. Fixing it will change numbers people have reported to the board. How do you handle it?
Why they ask this
Restatement is a governance decision that a data engineer has to be able to frame, and the technical fix is the easy part.
Say this
Quantify first, then decide with the business — do not fix quietly. Restate the affected periods, keep the before-and-after available, and record the restatement as a documented event with an effective date so any historical report can be explained.
The reasoning
The technical fix is trivial and doing it first is the mistake. The moment the corrected data lands, every historical dashboard changes, and if nobody was told, the first person to notice reports it as a new bug. Quantify the impact by period and by consumer before touching anything.
The decision itself is not the engineer's alone. Finance and compliance may have constraints on restating a closed period, and the answer can legitimately be 'correct going forward and annotate the past' rather than 'rewrite it'. Presenting the options with numbers attached is the job; choosing between them is a business call.
Whatever is decided, keep both versions. A snapshot of the pre-correction figures, or a correction fact that records the delta, means any historical report can be reproduced and explained. Overwriting without keeping the original leaves you unable to answer why last quarter's board pack does not match the warehouse.
Then record it as a first-class event: what was wrong, which periods and measures are affected, the effective date of the correction, and who approved it. A restatement log is what turns 'the numbers changed' from a trust problem into an auditable fact — and it is the artefact the next person will need when they find the discontinuity in the trend.
The answer most people give
"Backfill it overnight and mention it in standup." A silent restatement of three months is indistinguishable from a new bug, and the people who reported those numbers externally find out from someone else.
They’ll ask next
Finance says the closed quarter cannot be restated. What do you do with the corrected data?
Surrogate vs natural keysDimension types (conformed, degenerate, junk, role-playing, bridge)
The company acquires a business and now has two customer dimensions with overlapping people. What is the migration?
Why they ask this
Acquisitions are the most common cause of a genuine identity merge, and the answer has to keep both histories queryable while producing one customer.
Say this
Keep one row per source record and add a master id resolving duplicates, rather than physically merging. Facts keep pointing at the record they came from, reports group by master id, and a wrong match is reversible — which it will need to be.
The reasoning
Physically merging rows destroys the ability to trace a fact back to the system that produced it, and it makes a bad match permanent. Identity resolution is probabilistic at the edges — two people sharing an email, one person with two spellings — so any design that cannot be undone will eventually be wrong and unfixable.
The structure that survives is one dimension row per source record, carrying the source system and its id, plus a `master_id` that groups records believed to be the same person. Facts continue to reference the source record, so lineage is exact, and every report groups by master id, so a person counts once.
Sequence it as expand-migrate-contract. Load the acquired dimension alongside, run matching to populate master ids, verify against a sample the business can check, then move reports from grouping by customer to grouping by master id. Both dimensions stay queryable throughout, which is what lets the acquired business keep its own reporting during the transition.
Expect the match quality to be the hard part, and plan for review rather than perfection: a confidence score on each match, a manual override table that beats the algorithm, and a way to split a master id back into its components. The model's job is to make all three possible; the matching itself belongs in an MDM process rather than in the warehouse load.
The answer most people give
"De-duplicate on email and keep one row per person." Emails are shared, reused and changed. A merge on them is irreversible once facts are repointed, and the false matches are invisible because the two customers become one.
They’ll ask next
A match is wrong and the two customers must be split again. What does your model make possible that a physical merge does not?
An attribute was made Type 2 three years ago and no report has ever used the history. Can you undo it?
Why they ask this
Removing history is the migration nobody discusses, and it forces the candidate to weigh a real cost against an option they would be destroying.
Say this
You can, and it is one-way. Collapse to the current version per customer, keeping the existing surrogate keys where facts point at them. The cost is that any future as-was question becomes unanswerable, so confirm the requirement before, not after.
The reasoning
First establish that it is genuinely unused, which means query logs and lineage rather than asking around — a quarterly regulatory report that nobody remembers is exactly the consumer that will surface afterwards. Absence of evidence is the risk here, because the loss is irreversible.
The mechanical part is a collapse: keep the current version per natural key, and repoint facts that reference superseded versions to the surviving row. That last step is the one that changes numbers — a fact keyed to an old version now resolves to the current attributes, which is precisely the as-was information being given up.
So it is a correction rather than a non-breaking change, and worth stating plainly to stakeholders as 'historical reports grouped by this attribute will change'. If that sentence causes alarm, the attribute was not unused after all, and the migration stops there.
The middle path is usually better: keep the Type 2 structure and stop *versioning* the attribute — treat it as Type 1 going forward while retaining the versions already captured. That removes the growth without destroying anything, and it is reversible if a requirement appears. Full collapse is only worth it when the row multiplication is genuinely hurting.
The answer most people give
"Nobody uses it, so just drop the old versions." The versions are the only record of what was true. Dropping them is unrecoverable, and `nobody uses it` is a statement about the consumers you can see.
They’ll ask next
What is the cheapest way to stop the dimension growing without destroying the history you already have?
A fact needs a new `channel` dimension. The dimension key is not in the historical source data. What do you do?
Why they ask this
Adding a dimension is easy going forward and the historical gap is the interesting half, with three defensible answers that trade differently.
Say this
Add the key, populate it going forward, and point history at the unknown member rather than guessing. Then decide deliberately whether to backfill from a derivable source, leave it unknown, or restrict channel reporting to the period where it exists.
The reasoning
The forward part is routine: create the dimension, add the foreign key to the fact, populate it during the load, and declare it NOT NULL with an unknown member so it is never NULL. Existing reports are unaffected because nothing references the new column.
History is where the judgement is. Pointing it at the unknown member is honest and immediately correct — channel reports show a large 'Unknown' bucket for the historical period, which accurately says 'we did not capture this'. That is unattractive on a dashboard and it is true, which is the right trade until someone establishes otherwise.
Backfilling is defensible only when the value is genuinely derivable — the source has a field you did not ingest, or another system holds it, or there is a deterministic rule ('all pre-2024 orders came through the web because the store did not exist'). Each of those is a real answer; inferring channel from a heuristic and presenting it as fact is not, and if you do it anyway the fact should carry a flag saying the value was inferred.
The third option is to scope the metric: define channel reporting as starting from the date the field exists, and have the semantic layer refuse or annotate earlier periods. That is often the cleanest answer, because it stops a dashboard implying a trend across a boundary where the data changed meaning.
The formulations
Unknown member for historyship
-- historical rows point at channel_sk = -1 ('Unknown')
Honest, immediate, and the gap is visible rather than implied; the default unless the value is derivable.
Backfill from a derivable sourceship
UPDATE fct_order SET channel_sk = ... -- from a field that was always there
Correct when the value genuinely exists somewhere; flag the rows as backfilled either way.
Stops a dashboard implying a trend across a boundary where the data changed meaning.
Infer it from a heuristicavoid
-- guess channel from order size and time of day
Presents a guess as a fact, and nothing downstream can tell which rows were invented.
The answer most people give
"Default the history to the most common channel." That invents data. Every channel report then shows a confident historical distribution that is an artefact of the backfill rather than of the business.
They’ll ask next
You backfill from a derivable source. What do you add to the fact so a reader can tell those rows apart?
You are running old and new models side by side. How do you decide when to stop?
Why they ask this
Dual-running is the safety mechanism and it is also a cost, so the answer is about exit criteria rather than a duration.
Say this
By exit criteria, not by calendar. Stop when every consumer has moved, the reconciliation has passed across at least one full business cycle including a period close, and nothing has read the old structure for a stated window that you can evidence from access logs.
The reasoning
A duration is the wrong unit because the risk is not time, it is coverage. What matters is whether every code path that touches the model has run at least once — and the ones that have not are typically the quarterly, the year-end and the regulatory report, which is exactly the set you cannot afford to break.
So the first criterion is a full business cycle. If the business has a month-end close, a quarter-end and an annual process, the dual-run has to span the longest one you care about, because those runs exercise logic that daily loads never touch.
The second is evidence of non-use rather than belief in it. Access logs on the old structure, showing zero reads for a stated window, is the only version of 'nobody uses it' worth acting on. Instrumenting that at the start of the migration is what makes the end of it decidable.
The third is that the reconciliation kept passing throughout, not just at the beginning. Dual-running two loads means they can drift — a fix applied to one and not the other — so the reconciliation should be a scheduled test for the whole period, and its passing is part of the evidence to stop.
Set the criteria at the start and write them down. Dual-running with no defined exit is how a temporary compatibility view becomes permanent infrastructure that nobody is willing to remove.
The answer most people give
"Give it a month and then switch off." A month misses the quarter-end, and the quarterly report is the one with the most scrutiny and the least coverage in testing.
They’ll ask next
You have no access logs on the old table. What is your next best evidence, and how much would you trust it?
A fact partitioned by load date needs to be partitioned by event date instead. Queries filter on event date. What is the migration?
Why they ask this
It is a purely physical migration with no logical change, and it tests whether the candidate separates the two — plus knows what partition evolution does and does not do.
Say this
Rewrite the table into the new partitioning and swap it in. The logical model does not change and no query changes; only the physical layout does. On a table format supporting partition evolution, new data can adopt the new scheme while old data keeps the old one.
The reasoning
The reason to do it at all is that partitioning should follow the filter, not the load. Partitioned by load date, a query for one event day touches every partition that might contain it — which after a backfill is most of them — so pruning does nothing and every query is a full scan.
The classic migration is a rewrite: create the new table with the new partitioning, insert from the old, verify counts and totals per partition, then swap names atomically. It is expensive in compute and completely safe, because the old table is untouched until the swap and the swap is reversible.
Modern table formats — Iceberg in particular — support partition evolution, where the spec changes and existing data keeps its old layout while new writes use the new one. That avoids the rewrite and leaves you with a table whose old partitions still do not prune well, which is fine when the queries that matter are recent and not when they are historical.
Two things to watch. Event-date partitioning means late-arriving data writes into old partitions, so the load must handle out-of-order writes and small-file accumulation in a way that load-date partitioning never had to. And partition granularity is its own decision — daily is the usual default, monthly for lower volume, and hourly almost always produces too many small files.
The answer most people give
"Add an index on event date instead." Most analytical warehouses do not have that kind of index, and where clustering exists it complements pruning rather than replacing it. Partitioning is what decides how much data is read at all.
They’ll ask next
After the change, a backfill writes into 400 old partitions. What problem does that create, and how do you handle it?
`active_customer` is being redefined from 90 days to 30. Both definitions are in use. How do you land it?
Why they ask this
Metric changes are schema changes to meaning rather than to structure, and they break reports without changing a single column.
Say this
Treat it as two named metrics rather than a redefinition. Publish `active_customer_30d` alongside the existing one, move consumers explicitly, and retire the old name only when nothing uses it — so no dashboard changes meaning without its owner deciding.
The reasoning
Redefining a metric in place changes every number computed from it with no code change anywhere, which is the same failure as redefining a column's meaning. The person who notices is a stakeholder looking at a trend with a discontinuity, and by then the cause is several weeks back.
Naming both definitions removes the ambiguity entirely. `active_customer_90d` and `active_customer_30d` can coexist, be compared, and be adopted per consumer. It also forces the useful conversation: often the two teams wanting different windows both have good reasons, and the answer is that there are genuinely two metrics rather than one contested one.
Where a single canonical name must survive, make it a parameterised definition — a window argument with a documented default — so that the choice is visible at the call site. That is what a metrics layer is for, and it is the difference between a definition and a hard-coded number.
Either way, the change needs an owner, a review and a dated announcement with the expected impact quantified. A metric is a contract with the business, and changing it silently costs more trust than getting it slightly wrong in the first place.
The answer most people give
"Change the definition and add a note in the docs." Nobody reads the note before the dashboard. The number moves, someone escalates, and the explanation arrives after the escalation.
They’ll ask next
Is 30 days and 90 days two metrics or one with a parameter? What decides?
You cut over to a new model at 2am and the numbers are wrong. What does your rollback look like?
Why they ask this
Most migration plans stop at the cut-over. Having thought about the reverse is what distinguishes someone who has done this in production.
Say this
Rename back, if you kept the old structure. That is the whole reason expand-migrate-contract leaves the old thing in place until the end — the rollback is a metadata operation rather than a reload, and it takes seconds.
The reasoning
The rollback is decided long before the cut-over, by whether you deleted anything. If the old table still exists and the swap was a rename, reversing is a rename back and the system is as it was in seconds. If the old table was dropped or overwritten, the rollback is a restore from backup and the outage is measured in hours.
That is the practical argument for the contract phase being last and slow. Keeping the old structure through the dual-run costs storage and a second load; it buys a rollback that does not need anyone to be awake or clever, which at 2am is worth considerably more.
The second thing to have ready is the trigger. 'The numbers are wrong' is not a criterion — decide in advance which reconciliation, at what tolerance, run at what point after cut-over, and that a failure means roll back rather than investigate. Investigating with production wrong is how a thirty-second rollback becomes a four-hour incident.
Third, know what is not reversible. Any fact rows written by the new load after the cut-over need to be either replayable into the old structure or discardable, and any dimension whose surrogate keys were reassigned cannot simply be renamed back. Those are the parts to identify before the cut-over, because they are what turn a reversible change into a one-way door.
The answer most people give
"Restore from backup." That is the fallback, not the plan — it is slow, it loses everything written since, and it is what you are left with if you dropped the old structure too early.
They’ll ask next
Which part of your migration is genuinely one-way? What would you do differently to shrink it?
How would you find out that a model no longer matches the business it describes, before a stakeholder tells you?
Why they ask this
Models rot quietly, and the good answer is a set of running tests rather than a review meeting.
Say this
Assert the model's own invariants continuously: grain uniqueness, dimension uniqueness on natural keys, no overlapping Type 2 intervals, no orphan foreign keys, and reconciliation against source counts. Drift shows up as a test that starts failing rather than as a complaint.
The reasoning
The invariants are the ones the model already claims. The declared grain implies a unique key — assert it. A dimension claims one row per entity — assert uniqueness on the natural key. Type 2 claims non-overlapping, gap-free validity per key — assert both. Every foreign key claims a match — assert no orphans. Each of these is cheap and each catches a real class of failure.
Reconciliation against source is the second layer: row counts and control totals per period, compared to what the source system says it sent. That catches the failures the internal invariants cannot see, such as a feed that silently stopped delivering one region.
Distribution checks catch the subtler drift. A sudden change in the share of rows hitting the unknown member, a new value appearing in a column with a known code list, a measure whose daily average moves outside its historical range — none of these are wrong exactly, and all of them mean something changed upstream that nobody mentioned.
What makes it work is that failures have to be actionable. A test that fires weekly and is always slightly red teaches everyone to ignore it, so thresholds need tuning and each test needs an owner. The goal is that a red test means something happened, which is the only version anyone acts on.
The answer most people give
"Review the model quarterly with the business." Useful and far too slow — a quarter of wrong numbers is already in board packs. Reviews catch requirement drift; tests catch data drift, and you need both.
They’ll ask next
Which single test would you write first for a Type 2 dimension, and what exactly does it assert?
A transaction is deleted in the source system. What happens to the fact table row it produced, and what are your options?
Why they ask this
Facts are supposed to be immutable records of events, and a source that hard-deletes breaks that assumption — so the question is how you reconcile two models of truth.
Say this
You do not delete the fact row silently. Either post a reversing entry, or mark it deleted with an effective timestamp — both keep the history that a report published last week was based on.
The reasoning
**Why deleting is the wrong reflex.** A fact row is a record that something happened. If a report was published last month showing 4.2M in revenue and you delete rows behind it, that report can never be reproduced, and nobody can tell whether the number was wrong then or the data changed since. In a regulated context that is not just inconvenient, it is the thing the audit is checking for.
**Option 1 — the reversing entry.** Insert a new row with the same keys and negated measures, timestamped now. The original stays, the net is correct, and the history shows both what was recorded and when it was undone. This is the right answer for anything financial and it composes with everything downstream, because aggregates keep working without special handling.
**Option 2 — a soft-delete flag.** Add `is_deleted` and `deleted_at`, and have consumers filter. Simpler to write, and it moves the burden onto every reader — one query that forgets the filter reports deleted transactions as live. Acceptable when the consumer set is small and controlled, and it needs the filter built into a view or the semantic layer rather than left to each analyst.
**Option 3 — actually delete**, which is right in exactly one case: erasure for privacy compliance. That is a legal requirement to remove data, not a business event, and it needs deletion support in the storage format and a record kept of the fact that a deletion happened, even though the data is gone.
**And the pipeline half.** Hard deletes are invisible to a timestamp-based incremental load — a deleted row simply stops appearing, and a `WHERE updated_at > watermark` query never sees it. You need CDC that emits delete events, or a periodic full-key comparison against the source, or the deletion never reaches you at all. This is the failure mode most teams discover months late, when someone notices the warehouse has rows the source does not.
The formulations
Reversing entryship
INSERT INTO fact_txn
SELECT txn_id, -amount, 'reversal', CURRENT_TIMESTAMP
FROM fact_txn WHERE txn_id = 8842
History intact, aggregates correct, nothing downstream changes.
Soft delete with a filter in the viewworks
UPDATE fact_txn SET is_deleted = true, deleted_at = now()
WHERE txn_id = 8842; -- and consumers read a filtered view
Simple. Every reader that forgets the filter reports deleted rows.
Hard delete for erasureworks
DELETE FROM fact_txn WHERE customer_key = ... -- GDPR
The one legitimate case. Keep a record that the deletion happened.
Hard delete to match the sourceavoid
DELETE FROM fact_txn WHERE txn_id = 8842
Last month's published report can no longer be reproduced.
The answer most people give
"Delete it, so the warehouse matches the source." The warehouse is not a replica of the source — it is a record of what the source said over time. Matching current state exactly means giving up the ability to explain any number you published in the past.
They’ll ask next
Your incremental load is watermarked on updated_at. Would it even notice the delete?
