Snapshots

The problem: sources that overwrite

When a patient moves practice, many feeds simply update the row — the old practice is gone. Next month someone asks “how many patients did practice X have in January?” and the source can no longer answer. A snapshot solves this by checking the source on every run and recording each change as a new row, building history the source never kept.

How it looks

Snapshots live in snapshots/ and use the same SELECT-plus-config shape as models. dbt adds bookkeeping columns; a row is “current” while dbt_valid_to is null:

sk_patient_id  practice_code  dbt_valid_from  dbt_valid_to
1234           A81001         2024-03-01      2025-06-12
1234           A81002         2025-06-12      null          <- current row

This is the classic slowly changing dimension (type 2) pattern. The project uses it where history matters — for example dim_snapshot_person_pds_demographics tracks demographic changes from the PDS feed.

How a snapshot is defined

Snapshot files look like models with extra configuration: a unique key, and a strategy for detecting that a row has changed:

{% snapshot snapshot_patient_registration %}

{{
    config(
        unique_key='person_id',
        strategy='timestamp',
        updated_at='updated_at'
    )
}}

select person_id, practice_code, registration_status, updated_at
from {{ ref('stg_pds_registration') }}

{% endsnapshot %}

• timestamp strategy — dbt compares an updated_at column and processes only rows whose timestamp moved. Fast and simple, when the source has a reliable last-updated column.
• check strategy — dbt compares the actual values of a configured column list and records a new version when any of them change. Slower, but it works on data that has no timestamp at all — which makes it the natural choice for most of what we want to snapshot.

dbt's general advice is to prefer timestamp wherever the source has a reliable last-updated column — it is cheaper and more robust. Much of what this project snapshots has no such column, for the reason the next section explains, so check appears here more often than in most projects. Whichever strategy you use, the unique_key must genuinely be unique: it is how dbt matches this run's rows to the recorded versions, and a duplicated key corrupts the very history the snapshot exists to protect.

Why check strategy fits population health

The textbook snapshot watches a source table with an updated_at column. Much of what this team needs history for is different: derived states, not events. A person's risk group or population segment is computed from several temporal factors at once — conditions, age, latest results, service contacts. Nothing in the data says “this person moved from low to medium risk”; their computed segment is simply different the next time the model builds. There is no timestamp to watch, because the transition only exists as a difference between two builds.

That is exactly what check strategy captures: snapshot the stratification model and list the columns that define the state — dbt notices when a person's computed values differ from the last recorded version and writes a new row:

{% snapshot snapshot_person_risk_segment %}

{{
    config(
        unique_key='person_id',
        strategy='check',
        check_cols=['risk_band', 'segment']
    )
}}

select person_id, risk_band, segment
from {{ ref('fct_person_risk_stratification') }}

{% endsnapshot %}

Now the questions the stratification model alone cannot answer become simple queries on the snapshot: when did this person move from low to medium risk? Who entered the high-risk band this quarter, and from where? How long do people typically stay in a segment before stepping up? Each transition is a pair of adjacent rows — the old state closing (dbt_valid_to) at the moment the new one opens — without the upstream models having to model their own history.

Keep check_cols to the columns that define the state: every column listed is a reason to write a new version, so incidental columns inflate the history.

Querying a snapshot

-- current state
select * from {{ ref('my_snapshot') }}
where dbt_valid_to is null

-- as of a specific date
select * from {{ ref('my_snapshot') }}
where '2025-01-15' >= dbt_valid_from
  and ('2025-01-15' < dbt_valid_to or dbt_valid_to is null)

That “as of” pattern is common enough that the project wraps it in the temporal_join() macro — reach for that before writing your own.