Imad Bouzkraoui
FRGet in touch
All posts

3 min read

SCD Type 2 with dbt snapshots, explained with Airbnb data

How dbt snapshots keep the full history of changing records — timestamp vs check strategies, the columns dbt adds, and how to query a table as it was on any date.

In my Airbnb analytics engineering project (S3 → Snowflake → dbt), listings kept changing: prices moved, room types were edited, minimum stays went up and down. If you simply overwrite the latest version, you lose the ability to answer questions like “what was the price when this review was written?”

That is what Slowly Changing Dimensions Type 2 solve, and dbt makes them almost boring with snapshots.

SCD types in one table

Type What happens on change History kept?
Type 1 Overwrite the row No
Type 2 Close the old row, insert a new version Yes, full
Type 3 Keep a “previous value” column One step back

Type 2 is the default choice when the past matters — pricing, contracts, customer segments, anything you will want to analyse “as of” a date.

The snapshot

Since dbt 1.9 you can define a snapshot in YAML:

# snapshots/listings_snapshot.yml
snapshots:
  - name: listings_snapshot
    relation: source('airbnb', 'listings')
    config:
      schema: snapshots
      unique_key: listing_id
      strategy: check
      check_cols: [price, room_type, minimum_nights]
      hard_deletes: invalidate

On older versions, the same thing is a SQL block:

{% snapshot listings_snapshot %}
{{
  config(
    target_schema='snapshots',
    unique_key='listing_id',
    strategy='check',
    check_cols=['price', 'room_type', 'minimum_nights'],
    invalidate_hard_deletes=True
  )
}}
select * from {{ source('airbnb', 'listings') }}
{% endsnapshot %}

Run it with dbt snapshot. The first run copies every row; each later run compares the source with the latest version of each listing_id and records what changed.

Timestamp or check?

  • timestamp compares an updated_at column. It is the fastest and most reliable option — if the source has a trustworthy update timestamp.
  • check compares the values of the columns you list. Use it when there is no reliable updated_at — typical for scraped or exported datasets like Airbnb listings.

With check, only list the columns whose history you care about. Snapshotting a last_scraped column would create a new version on every run.

What dbt adds

Every snapshot row gets four metadata columns:

Column Meaning
dbt_scd_id Unique id of this version
dbt_updated_at Timestamp dbt used for this version
dbt_valid_from When this version became current
dbt_valid_to When it stopped being current (null = still current)

With hard_deletes: invalidate, a listing that disappears from the source gets its current row closed instead of staying “current” forever.

Querying the history

The current state of every listing:

select *
from {{ ref('listings_snapshot') }}
where dbt_valid_to is null

The table as it was on a given date:

select *
from {{ ref('listings_snapshot') }}
where dbt_valid_from <= '2025-06-01'
  and (dbt_valid_to > '2025-06-01' or dbt_valid_to is null)

And the question we started with — the price at the time of each review:

select
  r.review_id,
  r.review_date,
  l.price as price_at_review
from {{ ref('fct_reviews') }} as r
join {{ ref('listings_snapshot') }} as l
  on r.listing_id = l.listing_id
 and r.review_date >= l.dbt_valid_from
 and (r.review_date < l.dbt_valid_to or l.dbt_valid_to is null)

If the or ... is null pattern bothers you, dbt 1.9+ lets you set dbt_valid_to_current to a far-future date so current rows get a real value instead of null.

Where snapshots fit in a Medallion setup

In my project the layers were raw → silver → gold. Snapshots sit right after raw: they capture history from the source, and silver models build on top of them. Gold dimensions (in a star schema) then expose the history with a surrogate key per version.

Pitfalls I ran into

  • Snapshots only see what exists when they run. If a price changes twice between two runs, you keep only the last value. Schedule them as often as the business needs.
  • Snapshot sources, not transformed models. Transform logic changes; history captured through it becomes inconsistent. Keep snapshots as close to the source as possible.
  • Test the unique_key. A duplicated key silently corrupts history. A unique test on the source column is cheap insurance.
  • Watch time zones. Mixing UTC and local timestamps in dbt_valid_from makes point-in-time joins subtly wrong.

Snapshots are one of those features that look minor and end up being the reason an analyst trusts your warehouse. Set them up early — history you did not capture cannot be recovered.

Chat on WhatsApp