03. Snapshot Timestamp Strategy Example
Snapshot Timestamp Strategy Example
Use the Timestamp strategy (recommended) when your source table contains a reliable timestamp column (such as updated_at or last_modified) that gets updated by the source system every time a row is changed. This is much more performant on large tables than comparing column values.
Step 1: Raw Table Setup (SQL)
Imagine a product catalog table that has a dedicated UPDATED_AT column updated automatically when any column changes:
CREATE OR REPLACE TABLE DEMO_DB.RAW.PRODUCTS_TIMESTAMP (
PRODUCT_ID INT,
PRODUCT_NAME STRING,
PRICE NUMBER(10,2),
UPDATED_AT TIMESTAMP_NTZ
);
-- Insert initial products
INSERT INTO DEMO_DB.RAW.PRODUCTS_TIMESTAMP (PRODUCT_ID, PRODUCT_NAME, PRICE, UPDATED_AT)
VALUES
(101, 'Premium Wireless Headphones', 99.99, '2026-06-01 10:00:00'),
(102, 'Ergonomic Office Chair', 249.99, '2026-06-01 10:30:00');
Step 2: Create the dbt Snapshot File
Create this file as snapshots/products_snapshot_timestamp.sql inside your dbt project:
{% snapshot products_snapshot_timestamp %}
{{
config(
target_schema='snapshots',
unique_key='PRODUCT_ID',
strategy='timestamp',
updated_at='UPDATED_AT'
)
}}
SELECT
PRODUCT_ID,
PRODUCT_NAME,
PRICE,
UPDATED_AT
FROM {{ source('raw', 'products_timestamp') }}
{% endsnapshot %}
Key Parameters Explained
strategy='timestamp': Configures dbt to use the value of a datetime column to check for changes instead of comparing multiple column values.updated_at='UPDATED_AT': The name of the timestamp column to monitor. If the timestamp value in the source table is newer than the timestamp stored in the last snapshot execution, dbt archives the old record and inserts a new one.unique_key='PRODUCT_ID': The primary key column that uniquely identifies a row in the source table.target_schema='snapshots': The schema in the destination warehouse where the snapshot table will be stored.
Step 3: Run the Snapshot
Run the snapshot command to populate the historical tracking table:
dbt snapshot --select products_snapshot_timestamp
On the first run, the table SNAPSHOTS.PRODUCTS_SNAPSHOT_TIMESTAMP is created and populated with active records.
[!WARNING] If a value changes in the source table but
UPDATED_ATis not bumped to a newer timestamp, dbt will not detect the update.
Step 4: Confirm Initial Snapshot State
Query the snapshot table in Snowflake to verify that the rows were successfully ingested:
SELECT
PRODUCT_ID,
PRODUCT_NAME,
PRICE,
UPDATED_AT,
dbt_valid_from,
dbt_valid_to
FROM SNAPSHOTS.PRODUCTS_SNAPSHOT_TIMESTAMP
ORDER BY PRODUCT_ID;
Expected Output
All rows are active, meaning dbt_valid_to is null:
| PRODUCT_ID | PRODUCT_NAME | PRICE | UPDATED_AT | dbt_valid_from | dbt_valid_to |
|---|---|---|---|---|---|
| 101 | Premium Wireless Headphones | 99.99 | 2026-06-01 10:00:00 | 2026-06-01 10:00:00 | null |
| 102 | Ergonomic Office Chair | 249.99 | 2026-06-01 10:30:00 | 2026-06-01 10:30:00 | null |
Step 5: Simulate an Update in the Source
Simulate a price change by running an UPDATE in Snowflake. Crucially, we must update the UPDATED_AT timestamp to a newer value so dbt recognizes the change:
UPDATE DEMO_DB.RAW.PRODUCTS_TIMESTAMP
SET
PRICE = 89.99,
UPDATED_AT = '2026-06-08 14:00:00'
WHERE PRODUCT_ID = 101;
Step 6: Run the Snapshot Again
Run the snapshot command a second time:
dbt snapshot --select products_snapshot_timestamp
During this second execution, dbt detects that product 101 has a source UPDATED_AT timestamp (2026-06-08 14:00:00) that is newer than its previous snapshot timestamp (2026-06-01 10:00:00).
Step 7: Validate Historical Records
Query the snapshot table again to verify that history has been captured:
SELECT
PRODUCT_ID,
PRODUCT_NAME,
PRICE,
UPDATED_AT,
dbt_valid_from,
dbt_valid_to
FROM SNAPSHOTS.PRODUCTS_SNAPSHOT_TIMESTAMP
WHERE PRODUCT_ID = 101
ORDER BY dbt_valid_from;
Expected Output
dbt has archived the old record and added the new active version:
| PRODUCT_ID | PRODUCT_NAME | PRICE | UPDATED_AT | dbt_valid_from | dbt_valid_to |
|---|---|---|---|---|---|
| 101 | Premium Wireless Headphones | 99.99 | 2026-06-01 10:00:00 | 2026-06-01 10:00:00 | 2026-06-08 14:00:00 |
| 101 | Premium Wireless Headphones | 89.99 | 2026-06-08 14:00:00 | 2026-06-08 14:00:00 | null |
How dbt_valid_from & dbt_valid_to behave in Timestamp Strategy:
Unlike the Check strategy, the Timestamp strategy does not use the snapshot execution time. Instead, it aligns with your source data's history:
- Archived Row (Row 1):
dbt_valid_from=2026-06-01 10:00:00(Directly matches the original sourceUPDATED_ATvalue).dbt_valid_to=2026-06-08 14:00:00(Directly matches the new sourceUPDATED_ATvalue, marking the exact moment the product's record was modified in the source system).
- Active Row (Row 2):
dbt_valid_from=2026-06-08 14:00:00(Directly matches the new sourceUPDATED_ATvalue).dbt_valid_to=null(Currently active record).
[!TIP] This behavior makes the Timestamp strategy far more accurate for retroactive analytics, as the historical boundaries represent the actual business events rather than when the dbt orchestrator scheduled the run.