strategy

snapshots/<filename>.sql
{% snapshot snapshot_name %}
{{ config(
strategy="timestamp",
updated_at="column_name"
) }}
select ...
{% endsnapshot %}
dbt_project.yml
snapshots:
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

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 %}