SCD Type 2 in SQL

The numbered SCD types are Kimball's catalogue of ways to handle a dimension attribute that changes. Type 0 keeps the original value forever, as with a customer's signup date. Type 1 overwrites, Type 2 adds a row, and Type 3 adds a column that holds the prior value, which supports a before and after comparison for 1 change but no deeper history.

The higher numbers combine the basic 3. Type 4 moves fast-changing attributes into a separate mini-dimension with its own key on the fact, and Type 5 adds the current mini-dimension key to the base dimension as a Type 1 column. Type 6 keeps Type 2 rows and adds current-value columns that are overwritten on every version, so 1 join reports either the historical or the current value. Type 7 gives the fact 2 keys: the surrogate key for the version and the durable key for a view of current rows.

In practice most warehouses mix Type 1 and Type 2 attributes in the same table, choosing per attribute. When analysts need both as-was and as-is reporting, they add Type 6 or 7. The slowly changing dimensions guide walks through each type with its own table.

A Type 2 load compares a staging table, 1 row per changed or new member, with the current rows of the dimension, and does 2 things: close the rows whose tracked attributes changed, and open a row for every member that has no current row. Run both statements in 1 transaction. A failure between them would leave changed customers with no current row, and every fact loaded meanwhile would lose its dimension.

The order of the statements is what keeps the insert simple. Once the changed rows are closed, the members without a current row are exactly the ones that need a new row: the customers just closed plus the ones never seen before. Unchanged customers still have their current row and are skipped, which makes the load idempotent. Run the same batch twice and the second run changes nothing.

Detect changes with IS DISTINCT FROM, not <>. In SQL, NULL <> 'pro' is NULL rather than TRUE, so a country that goes from NULL to a value, or back, never registers as a change with <>. When many columns are tracked, compare a hash of them instead: compute md5 or sha2 over the concatenated attributes in staging and store it on the row.

Deduplicate staging first. If 1 customer changed plan twice since the last load, staging holds 2 rows for it, and both statements would act twice. Either keep only the latest change per key, or, when every intermediate version matters, apply the changes in order of changed_at 1 batch at a time. Type 1 attributes such as name get their own UPDATE across all rows of the customer, so a correction reaches every version.

A changed customer needs 2 writes: an update to its current row and an insert of a new one. A MERGE applies at most 1 action to each target row, and the SQL standard makes it an error for 1 target row to match 2 source rows, so a plain MERGE on customer_id can close the old row or insert the new one, never both.

The standard workaround stages every changed row twice. The first copy carries the real key as merge_key, matches the current row and closes it. The second copy carries a NULL merge_key, which matches nothing, so it falls through to WHEN NOT MATCHED and is inserted as the new version. New customers appear once, with a real key and no current row, and are inserted. The Delta Lake documentation uses the same union of NULL-keyed and keyed rows for its Type 2 example.

The single statement buys atomicity on engines where multi-statement transactions are awkward, and 1 scan of the target instead of 2. It costs readability, and the matched condition must repeat the change test, or unchanged customers would be closed on every run. The same rule about duplicates applies with more force here: 2 staged rows for 1 customer make the statement fail with a cardinality error.

The fast way to join a fact to the version that was valid when it happened looks the surrogate key up once, when the fact is loaded, and stores it. Every later query is an equality join on customer_key, and a change to the customer never moves the fact.

The other way stores only the natural key on the fact and joins on a date range at query time: order_ts >= valid_from AND (order_ts < valid_to OR valid_to IS NULL). It gives the same answer when the intervals are clean. But every query pays for a range join, and a gap in the intervals silently drops facts while an overlap duplicates them. Use it to rebuild facts after a backfill, or where facts arrive before the warehouse assigns keys.

Write the range as half-open, with valid_from inclusive and valid_to exclusive. The closing row's valid_to must equal the new row's valid_from. BETWEEN valid_from AND valid_to includes both ends, so an order stamped at the exact change instant matches both versions and is counted twice. The older habit of ending a row 1 day before the next begins avoids that with dates but leaves a gap for any timestamp later that day.

Orders can arrive before their customer does. An inner join at fact load drops them, so join with a left join and give unmatched facts the key of an unknown member row, -1 by convention, then repair those keys when the dimension catches up.

A fact that stores the natural key, customer_id, points at a customer. A fact that stores the surrogate key, customer_key, points at 1 version of that customer. In a Type 2 dimension only the second binding is stable: the natural key matches every version, so a plain join on it multiplies each fact by the number of versions the customer has, and a join to the current row reports today's attributes against yesterday's events.

That is why the foreign key from fact_order references dim_customer.customer_key, and why the relationship is many to exactly 1: many orders can share a version, and each order has exactly 1. In a star schema, every dimension key on a fact follows the same rule.

Keep the natural key on the dimension so the load can find a customer's current row, and add it to the fact as well when analysts ask for totals by current attribute. That is Kimball's Type 7: the fact carries both keys, the surrogate key joins to history and the durable key joins to a view of current rows.

In a dbt project, Type 2 is a snapshot. You point it at the source relation with a unique_key and a change detection strategy. Each dbt snapshot run then inserts new versions and closes the old ones. dbt adds 4 columns: dbt_valid_from, dbt_valid_to, dbt_scd_id, a unique key per snapshot row, and dbt_updated_at. Current rows have a NULL dbt_valid_to unless dbt_valid_to_current sets a sentinel such as 9999-12-31.

The 2 strategies differ in where the time comes from. The timestamp strategy reads an updated_at column and uses it for the validity columns, so versions start when the source says the change happened. The check strategy compares the columns in check_cols and, unless an updated_at is also configured, stamps versions with the time of the snapshot run, so a change made on Monday and captured on Wednesday starts on Wednesday. The dbt snapshot documentation recommends the timestamp strategy and warns against check_cols: all.

Since dbt 1.9 snapshots are configured in YAML rather than a Jinja snapshot block, and hard_deletes decides what a deleted source row does. ignore leaves its last version open and invalidate closes it; new_record goes further and inserts a row with dbt_is_deleted set. A snapshot captures only what it sees when it runs, so a source that changes twice between runs keeps only the second change. Schedule snapshots at least as often as the history you need, or snapshot the change stream instead of the table.

The SQL is the same everywhere; what changes is what the engine guarantees. PostgreSQL enforces the 1-current-row rule with a partial unique index. Snowflake accepts PRIMARY KEY and UNIQUE constraints on standard tables but enforces only NOT NULL. BigQuery's primary keys are unenforced too, so on both engines the invariant exists only if a test checks it after every load.

All 3 support MERGE with IS DISTINCT FROM, so the staged union MERGE runs unchanged. Databricks also offers a declarative path in its pipelines: AUTO CDC ... STORED AS SCD TYPE 2, formerly APPLY CHANGES, takes the keys and a SEQUENCE BY column and writes __START_AT and __END_AT on every version. It orders late or out-of-order change records by the sequence column instead of arrival time.

On columnar engines, nearly every read of the dimension filters on the natural key or the current flag, so cluster or partition by 1 of them. Keep the closed history in the same table. Splitting current and history into 2 tables makes current reads cheaper but turns every point-in-time query into a union.

Handle members deleted at the source on purpose, because leaving the current row open reports a closed account as active. Closing the row by setting valid_to keeps the history and ends it at the deletion. Adding a final version flagged as deleted says when it happened as a row of its own, which is what dbt's new_record option does. Physically deleting dimension rows breaks every fact that points at them and is almost never right.

A change that arrives late, dated before the current row began, cannot simply be appended. It belongs inside an existing interval, so the load has to split that version in 2 and move every fact loaded since the backdated change to the new key. Most teams accept load-time validity for this reason: valid_from records when the warehouse learned of the change. Where business-time accuracy matters, rebuild the affected member's versions from the ordered change log and re-key its facts in 1 transaction.

Reruns must be harmless. A load that is safe to run twice compares with IS DISTINCT FROM so an unchanged row is never versioned, takes valid_from from the source's change time rather than the clock, and refuses a batch whose change times are older than the current rows it would close.

A Type 2 dimension holds 1 row per member plus 1 per change, so its size is set by the fastest-changing tracked attribute. Take 10 million customers kept for 3 years. Track only attributes that change about once a year, such as plan tier, and the table holds 40 million rows. Track last_login_at as Type 2 as well and it changes daily, so the table grows to 10.96 billion rows, about 274 times larger, almost all of it login noise.

The fix is to take high-churn values out of the dimension. An event such as a login belongs in a fact table of its own. A banded, frequently changing profile such as an age band or a usage band belongs in a Type 4 mini-dimension, whose key sits on the fact beside the customer key. What stays in the Type 2 dimension should be attributes that change rarely and change the meaning of past facts.

Type 1 is the right answer for corrections. A misspelled name or a mistyped postcode was never true, so versioning it creates history nobody wants and a row count that grows with every data quality fix. Decide per attribute, write the decision down, and track as Type 2 only the attributes the business reports history on.

SCD Type 2 is one of the topics data modeling interviewers return to most, because 1 question exposes whether a candidate has run a warehouse. Expect to write the 2-statement load live, then the point-in-time join, and expect a follow-up on why the fact stores customer_key and not customer_id. Strong answers mention the transaction, IS DISTINCT FROM, half-open intervals and the test for 2 current rows without being asked.

Design questions push further: a dimension that grew to hundreds of millions of rows for a few million customers, a change that arrived a week late, a source that deletes rows, or a business that wants revenue by both the plan at purchase and the plan today. Each is answered by a technique on this page, from moving churn out of the dimension to Type 6 and Type 7.

The SCD interview questions collect these as they are asked in real interviews and answer each one in full, while the data modeling interview questions cover the rest of the round. To practise drawing and loading a Type 2 model, work through the data modeling practice problems, and read the data modeling interview round guide for how the round is run.