A slowly changing dimension is a dimension whose attributes, such as a customer's segment, change from time to time; its type decides what a report shows for facts recorded before a change. Type 1 overwrites the old value, while type 2 keeps the old row and adds a new one for each version. A dbt snapshot builds type 2 history from a source table that only ever holds the present.
Four types in plain words
These are the first four types in Kimball Group's list:
- Type 0, retain original: the value never changes once written, so facts always group by the original value. It suits attributes labelled original, such as a customer's original credit score.
- Type 1, overwrite: the new value replaces the old one in the same row. The row shows only the latest value, so history is lost and any aggregate built on the old value has to be recomputed.
- Type 2, add new row: each change keeps the old row and adds one with the new values under a new surrogate key. At least three extra columns say when each row became valid, when it stopped being valid and whether it is the current row.
- Type 3, add new attribute: the old value moves to a new column and the new value overwrites the main one, so a report can group by either. It keeps the old value in a column of its own, and Kimball Group says the technique is used relatively infrequently.
Worked example
In the synthetic data of the dbt snapshots note, customer c1, Priya Raman, moves from the standard segment to premium on 15 January 2026. A dbt snapshot records that change as type 2: two rows, the first closed on the day the second opens.
| segmentVARCHAR | dbt_valid_fromDATE | dbt_valid_toDATE | customer_idVARCHAR | customer_nameVARCHAR | dbt_scd_idVARCHAR |
|---|---|---|---|---|---|
| standard | 2026-01-01 | 2026-01-15 | c1 | Priya Raman | s1a |
| premium | 2026-01-15 | NULL | c1 | Priya Raman | s1b |
Synthetic data, showing 2 of 7 rows from customer_snapshots in the note dbt snapshots and SCD type 2: what history misses
The current row's dbt_valid_to is null, and the closed row's dbt_valid_to equals the new row's dbt_valid_from. Customer c1 also placed an order on 10 January. Here is how that order is reported under each type, and what each type holds after the change:
| Type | Order reports as | What the dimension holds for c1 |
|---|---|---|
| Type 0 | standard |
One row, still standard |
| Type 1 | premium, although it was placed as standard |
One row, now premium |
| Type 2 | standard, through a join on the order's date |
Two rows: standard until 15 January, then premium |
| Type 3 | Either, as the report chooses | One row: premium, with standard kept as the prior value |
How dbt snapshots build type 2
dbt's snapshot documentation says snapshots implement type 2 slowly changing dimensions over mutable source tables. Each dbt snapshot run compares the source with what the snapshot recorded last time. On the timestamp strategy, a row whose updated_at is newer than the last run closes the old version and records the new one. The check strategy compares a list of columns instead.
dbt adds meta columns to every row it writes. dbt_valid_from and dbt_valid_to do the job of type 2's effective and expiration dates, and a null dbt_valid_to marks the current row. From dbt 1.9, dbt_valid_to_current can put a date such as 9999-12-31 there instead of null, on the current rows written after it is set. Each row's dbt_scd_id is a key dbt uses internally; a report joins on the source's own key and the validity dates.
Snapshots are batch-based. dbt's FAQ on snapshot frequency says the command must run on a schedule and is intended to run between hourly and daily.
DataEngr is not affiliated with dbt Labs.
Where it goes wrong
Joining facts to the current row
Filtering the snapshot to the rows where dbt_valid_to is null, then joining on the customer key, reports every past order under the customer's present segment. In the dbt snapshots note this moves 14000 cents off standard without changing the total.
Joining facts to every version
Joining on the customer key alone, with no date condition, repeats each order once per version. The same note's every-version join inflates 43000 cents to 76000.
Leaving hard deletes on the default
dbt's hard_deletes setting defaults to ignore: a row deleted from the source is not tracked and its dbt_valid_to stays null, so the deleted customer still looks current. The note's trade-off section explains the choice. Under invalidate, which the note models, a current-row join drops the deleted customer's orders without an error: 10500 cents in its hard-delete scenario.
Changes between two runs
A snapshot sees only the state that exists when it runs. If a key changes twice between two runs, the middle state leaves no row at all. dbt's documentation does not say this directly: it follows from how the timestamp strategy compares each run with the last, and the note's customer c3 shows it.
Orders before the first snapshot row
An order dated before a customer's first snapshot row matches no version. An as-of join should send it to an explicit unknown segment rather than drop it or guess, and the note's late-arrival scenario routes 6000 cents that way.
Questions
What is SCD type 2?
SCD type 2 keeps history by adding a new dimension row for each change, with dates that say when each version was valid, so a fact can join to the version that was true when it happened.
Do dbt snapshots implement SCD type 2?
Yes: dbt's documentation says snapshots implement type 2 slowly changing dimensions, and each version is written as a row with dbt_valid_from and dbt_valid_to.
What is the difference between SCD type 1 and type 2?
Type 1 overwrites the old value so every report shows the latest one, while type 2 adds a row for each change so a report can show the value that was true at the time.