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