strategy

• timestamp
• check

snapshots/<filename>.sql

{% snapshot snapshot_name %}

{{ config(
  strategy="timestamp",
  updated_at="column_name"
) }}

select ...

{% endsnapshot %}

dbt_project.yml

snapshots:
  <resource-path>:
    +strategy: timestamp
    +updated_at: column_name

Description

The snapshot strategy dbt should use to detect record changes. Read the guide to snapshots to understand the differences between the two.

Default

This is a required configuration. There is no default value.

Examples

Use the timestamp strategy

snapshots/timestamp_example.sql

{% snapshot orders_snapshot_timestamp %}

    {{
        config(
          target_schema='snapshots',
          strategy='timestamp',
          unique_key='id',
          updated_at='updated_at',
        )
    }}

    select * from {{ source('jaffle_shop', 'orders') }}

{% endsnapshot %}

Use the check_cols strategy

{% snapshot orders_snapshot_check %}

    {{
        config(
          target_schema='snapshots',
          strategy='check',
          unique_key='id',
          check_cols=['status', 'is_cancelled'],
        )
    }}

    select * from {{ source('jaffle_shop', 'orders') }}

{% endsnapshot %}

Advanced: define and use custom snapshot strategy

Behind the scenes, snapshot strategies are implemented as macros, named snapshot_<strategy>_strategy

• Source code for the timestamp strategy
• Source code for the check strategy

It's possible to implement your own snapshot strategy by adding a macro with the same naming pattern to your project. For example, you might choose to create a strategy which records hard deletes, named timestamp_with_deletes.

1. Create a macro named snapshot_timestamp_with_deletes_strategy. Use the existing code a guide and adjust as needed.
2. Use this strategy via the strategy configuration:

snapshots/<filename>.sql

{% snapshot snapshot_name %}

{{ config(
  strategy="timestamp_with_deletes",
  updated_at="column_name"
) }}

{% endsnapshot %}