Snapshot Strategies

Concepts covered: Snapshots

A snapshot reads a table that overwrites itself and keeps 1 row per version it has seen. On each row, dbt_valid_from and dbt_valid_to bound the time that version held. The intermediate lesson showed that result. Behind it, every run asks 2 questions of every row: has this row changed since the last run, and is this row still there. The strategy answers the first question, the hard_deletes setting answers the second.

timestamp: trust a column

snapshots:
  - name: customers_snapshot
    relation: source('raw', 'customers')
    config:
      unique_key: customer_id
      strategy: timestamp
      updated_at: updated_at

With strategy: timestamp, a source row counts as changed when its updated_at is later than the updated_at the snapshot recorded for that unique_key. The comparison reads 1 column, so it costs little, and dbt_valid_from takes the source's own updated_at, so the history records when the application changed the row rather than when dbt noticed. Both benefits depend on the application keeping updated_at honest. A support tool that edits plan without touching updated_at makes a change the snapshot never records. A nightly job that rewrites updated_at on every row records a new version of every row, each identical to the last apart from its dates.

check: compare the columns

With strategy: check, dbt compares each column in check_cols between the source row and the snapshot's latest row for the same key, counting 2 nulls as equal. A difference in any listed column records a new version. A difference in a column outside the list records nothing. check_cols: all compares every column, at the price of comparing every column on every row. The timeline is dbt's own: dbt_valid_from is the run's timestamp, so a row that changed twice between 2 nightly runs yields 1 version holding the final values, and the history is as fine as the schedule.

timestamp

• Changed when 1 column is later than before
• dbt_valid_from is the application's time
• Misses edits that leave updated_at alone
• Records a version for every bump of updated_at

check

• Changed when any listed column differs
• dbt_valid_from is the snapshot run's time
• Misses edits to columns outside the list
• Records 1 version per run however many edits happened

Choose by what the source gives you. A table with an updated_at the application maintains on every write should use timestamp, which gives the cheaper comparison and the truer dates. A table with no such column, or one where edits arrive from tools that skip it, needs check, with check_cols listing the columns whose history anyone will ask about and nothing more, since every listed column is compared on every row on every run.

When a row disappears

A key present last run and absent this run is a hard delete, and the default, hard_deletes: ignore, does nothing with it: the last version keeps a null dbt_valid_to, and every query for current rows reports a customer who no longer exists. The 2 other settings record the disappearance in different shapes.

In the animation, run 2 edits 2 rows: customer 1 moves from free to pro, customer 3 moves from US to CA. The snapshot gains 1 row, for customer 1, because country is outside check_cols and the edit to customer 3 is invisible to this snapshot. Run 3 finds customer 2 gone. new_record sets dbt_valid_to on customer 2's open row and inserts a 5th row carrying the same values with dbt_is_deleted set to True, so the table says when customer 2 left and what the row held at that moment.

About this lesson

This section is part of the Models: Advanced lesson on DataDriven, the home of data engineering. Lessons cover SQL, Python, Spark, data modeling and pipeline architecture, and each section pairs its explanation with worked examples and practice you run as you go.