Skip to main content

strategy

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

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