A Type-2 slowly changing dimension keeps every historical version of a row so "what was true as of date X" is answerable — but only if every change is captured as a new row, never an in-place update.
customer_123.planRecord a change to this customer's plan. This appends a new row and closes out the previous one — it never overwrites history.
Simulates: SELECT * FROM dim WHERE customer_id = 123 AND effective_from <= :as_of AND (effective_to > :as_of OR effective_to IS NULL)
| customer_sk | plan | effective_from | effective_to | is_current |
|---|
A Type-1 dimension overwrites the current value in place — asking it "what plan was this customer on when they placed order #4821 last March" returns whatever plan they're on today, because history was never kept. Type-2 keeps every version with an effective window, which is exactly what a fact table needs to join against for a historically correct answer. See Data Modelling, "Slowly changing dimensions."
The real failure mode: a query joins a fact table (orders) to a dimension using customer_id and grabs the current row — is_current = true — instead of the row that was current at order_created_at. If the customer has since upgraded from free to pro, every historical order silently gets rewritten as if they had always been on pro. The report is internally consistent and shows no error; it is just wrong, and nobody notices until a revenue-per-plan number stops reconciling with billing.
f.order_created_at >= d.effective_from AND f.order_created_at < d.effective_to (or effective_to IS NULL for the open/current row).effective_to and opening the new row's effective_from at the same instant is what keeps the query exact-once.customer_sk at ingestion time, or maintaining a separate current-dimension table for serving, are common mitigations — but the historical SCD2 table is still what makes the answer possible at all.