03. 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:
timestampstrategy – relies on a timestamp column to detect changescheckstrategy – 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 changesupdated_at='last_modified'→ Column dbt monitors for changesunique_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 changescheck_cols=['first_name', 'last_name', 'email', 'phone']→ Columns to monitorunique_key='customer_id'→ Primary key of the source table
Key Differences: Timestamp vs Check
| Feature | Timestamp Strategy | Check Strategy |
|---|---|---|
| Detect changes by | Single timestamp column | Specific column values |
| Requires timestamp? | Yes | No |
| Good for multiple cols? | Only timestamp | Multiple columns |
| Use case | When updated_at exists | When 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.