Skip to main content

05. Timestamp vs Check Strategy

️ Timestamp vs Check Strategy in dbt Snapshots​

When implementing SCD Type 2 in dbt, you can choose between two snapshot strategies:

  1. timestamp strategy – relies on a timestamp column to detect changes
  2. check strategy – compares specific column values to detect changes

Choosing the right strategy ensures efficient snapshotting and accurate historical tracking.


1. Timestamp Strategy​

The timestamp strategy uses a column (usually updated_at or modified_at) to detect if a row has changed.

How it works:

  • dbt compares the value in your timestamp column to the previously stored value.
  • If the timestamp is newer, a new snapshot record is created.
  • No need to explicitly track every column; only the timestamp matters.

Example Snapshot Using Timestamp Strategy​

{% snapshot customers_snapshot_timestamp %}
{{
config(
target_schema='snapshots',
unique_key='customer_id',
strategy='timestamp',
updated_at='last_modified'
)
}}

select * from {{ source('app', 'customers') }}

{% endsnapshot %}

Explanation:

  • strategy='timestamp' → Use timestamp to detect changes
  • updated_at='last_modified' → Column dbt monitors for changes
  • unique_key='customer_id' → Primary key of the source table

2. Check Strategy​

The check strategy monitors specific columns for any changes.

  • If any of the specified columns change, a new snapshot record is created.
  • Useful when timestamps aren’t available or multiple columns need tracking.

Example Snapshot Using Check Strategy​

{% snapshot customers_snapshot_check %}
{{
config(
target_schema='snapshots',
unique_key='customer_id',
strategy='check',
check_cols=['first_name', 'last_name', 'email', 'phone']
)
}}

select * from {{ source('app', 'customers') }}

{% endsnapshot %}

Explanation:

  • strategy='check' → Compares columns for changes
  • check_cols=['first_name', 'last_name', 'email', 'phone'] → Columns to monitor
  • unique_key='customer_id' → Primary key of the source table

Key Differences: Timestamp vs Check​

FeatureTimestamp StrategyCheck Strategy
Detect changes bySingle timestamp columnSpecific column values
Requires timestamp?YesNo
Good for multiple cols?Only timestampMultiple columns
Use caseWhen updated_at existsWhen no reliable timestamp exists or multi-column changes

Best Practices​

Use timestamp strategy when you have a reliable updated_at column
Use check strategy when multiple columns need monitoring
Always define a unique_key
Schedule snapshots regularly (daily/hourly) for accurate history


Query Example for Snapshot Changes​

select * from snapshots.customers_snapshot_check
where customer_id = 123
order by dbt_valid_from desc;

This will show the historical versions of a customer.