Skip to main content

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_AT is 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_IDPRODUCT_NAMEPRICEUPDATED_ATdbt_valid_fromdbt_valid_to
101Premium Wireless Headphones99.992026-06-01 10:00:002026-06-01 10:00:00null
102Ergonomic Office Chair249.992026-06-01 10:30:002026-06-01 10:30:00null

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_IDPRODUCT_NAMEPRICEUPDATED_ATdbt_valid_fromdbt_valid_to
101Premium Wireless Headphones99.992026-06-01 10:00:002026-06-01 10:00:002026-06-08 14:00:00
101Premium Wireless Headphones89.992026-06-08 14:00:002026-06-08 14:00:00null

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 source UPDATED_AT value).
    • dbt_valid_to = 2026-06-08 14:00:00 (Directly matches the new source UPDATED_AT value, 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 source UPDATED_AT value).
    • 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.