dbt Snapshots: Tracking Coverholder History with SCD Type 2

Some questions can only be answered with history. Was this coverholder approved when the risk incepted? Which broker was on the contract section when the claim was notified? What was the commission rate before it was renegotiated? If your dimension tables only hold current values, those answers are gone.

In SSIS we built Slowly Changing Dimension Type 2 logic by hand, and later as MERGE statements. In dbt, the same job is done by snapshots. This post shows how to use them for insurance reference data, including the YAML configuration introduced in dbt 1.9, and the traps to avoid.

How a snapshot works

A snapshot looks at a source table each time it runs, compares each row with what it stored last time, and:

• inserts new rows with dbt_valid_from set and dbt_valid_to null,
• for changed rows, closes the old version by setting dbt_valid_to and inserts a new version,
• leaves unchanged rows alone.

The result is a full Type 2 history table, maintained by dbt.

Defining a snapshot in YAML (dbt 1.9+)

From dbt 1.9, snapshots can be defined entirely in YAML, next to your models:

 1: # snapshots/coverholder_snapshot.yml
 2: snapshots:
 3:   - name: coverholder_snapshot
 4:     relation: source('feed', 'coverholder')
 5:     config:
 6:       schema: snapshots
 7:       unique_key: coverholder_id
 8:       strategy: check
 9:       check_cols: ['coverholder_name', 'country', 'approval_status', 'oversight_rating']
10:       hard_deletes: invalidate

In older versions, the same thing is a {% snapshot %} block in a .sql file. It still works, but the YAML version is shorter and easier to review.

Choosing a strategy

timestamp uses an "updated at" column. If the source row's timestamp is newer than the stored version, it's a change:

1:     config:
2:       unique_key: [umr, section_no]
3:       strategy: timestamp
4:       updated_at: last_modified_at

It's efficient and reliable, but only if the source really updates that column every time something changes. Plenty of systems don't.

check compares the listed columns directly. It's slower on very wide or large tables, but it doesn't depend on the source system behaving. For reference data from a feed, where I can't be sure an update timestamp is maintained, I use check with an explicit column list.

Avoid check_cols: all unless you really mean it. If the source has a load timestamp or a batch ID that changes on every delivery, every row will look changed and you'll get a new version of every coverholder every day.

Deletes

By default, a row that disappears from the source stays "current" in the snapshot forever. hard_deletes: invalidate (dbt 1.9+) closes it instead, and new_record goes further and adds a row marking the deletion. For a coverholder that's been removed from the register, invalidate is usually what you want.

Only do this if the source is a complete extract. If the source table only holds changes, every coverholder missing from today's delivery would be closed.

Snapshot raw data, not models

Snapshot the source as close to raw as you can, before business logic. If you snapshot the output of a model and later change the model's logic, the snapshot will record a "change" for every row, and you can't recreate the true history. Apply cleaning in a model that reads from the snapshot instead.

Building the dimension

The snapshot itself isn't the dimension. A model on top of it adds a surrogate key and friendlier column names:

 1: -- models/marts/dim_coverholder.sql
 2: select
 3:     {{ dbt_utils.generate_surrogate_key(['coverholder_id', 'dbt_valid_from']) }} as coverholder_key,
 4:     coverholder_id,
 5:     coverholder_name,
 6:     country,
 7:     approval_status,
 8:     oversight_rating,
 9:     dbt_valid_from                                   as valid_from,
10:     coalesce(dbt_valid_to, cast('9999-12-31' as timestamp)) as valid_to,
11:     dbt_valid_to is null                             as is_current
12: from {{ ref('coverholder_snapshot') }}

Replacing a null valid_to with a far-future date makes range joins from the facts simpler.

Joining facts to the right version

1: select
2:     r.*,
3:     c.coverholder_key
4: from {{ ref('stg_risks') }} r
5: join {{ ref('dim_coverholder') }} c
6:   on  c.coverholder_id = r.coverholder_id
7:   and r.risk_inception_date >= c.valid_from
8:   and r.risk_inception_date <  c.valid_to

Now "premium written while the coverholder was suspended" is a simple filter on approval_status, using the version that applied at the time.

Things to watch

• Run snapshots on a schedule. A snapshot only sees the state of the source when it runs. Changes made and reverted between two runs are never captured. For reference data that changes rarely, daily is usually enough.
• History starts on day one. A snapshot can't recreate the past. If you need history before that, load it separately, for example from archived extracts, into a table with the same columns.
• Back it up. Snapshot tables hold history that can't be rebuilt from the source. Don't drop them casually in a full refresh, and make sure your platform's retention or time travel settings cover them.
• Mind the time zone. dbt_valid_from comes from the timestamp column or the run time. Keep it consistent with the dates in your facts, or range joins will miss on boundary days.
• Test it. A simple test that every coverholder_id has exactly one current row catches most problems early.

1: # models/marts/_marts.yml
2: models:
3:   - name: dim_coverholder
4:     tests:
5:       - dbt_utils.unique_combination_of_columns:
6:           combination_of_columns: ['coverholder_id', 'valid_from']
7:     columns:
8:       - name: coverholder_key
9:         tests: [unique, not_null]

Snapshots take away almost all of the hand-written SCD code I used to maintain. The design questions don't go away, though: which columns to track, what counts as a delete, and how facts pick the right version.