Skip to main content

02. Snapshot Check Strategy Example

Snapshot Check Strategy Example​

Use the Check strategy when your source table does not have an updated_at or last_modified column, or when you only want to detect changes on specific columns.


Step 1: Raw Table Setup (SQL)​

Imagine a raw customer table where we want to track changes to the customer's email or country, but there is no update timestamp in the raw schema:

CREATE OR REPLACE TABLE DEMO_DB.RAW.CUSTOMERS_CHECK (
CUSTOMER_ID INT,
NAME STRING,
EMAIL STRING,
COUNTRY STRING
);

-- Insert initial customers
INSERT INTO DEMO_DB.RAW.CUSTOMERS_CHECK (CUSTOMER_ID, NAME, EMAIL, COUNTRY)
VALUES
(1, 'Alice Johnson', 'alice.johnson@example.com', 'USA'),
(2, 'Carlos Rivera', 'carlos.rivera@example.com', 'Mexico');

Step 2: Create the dbt Snapshot File​

Create this file as snapshots/customers_snapshot_check.sql inside your dbt project:

{% snapshot customers_snapshot_check %}

{{
config(
target_schema='snapshots',
unique_key='CUSTOMER_ID',
strategy='check',
check_cols=['EMAIL', 'COUNTRY']
)
}}

SELECT
CUSTOMER_ID,
NAME,
EMAIL,
COUNTRY
FROM {{ source('raw', 'customers_check') }}

{% endsnapshot %}

Key Parameters Explained​

  • strategy='check': Configures dbt to compare the actual values of specified columns between the snapshot run and the source table to detect changes.
  • check_cols=['EMAIL', 'COUNTRY']: The specific columns to monitor. If either the email or country changes, dbt archives the old record (sets a dbt_valid_to timestamp) and inserts a new active record (dbt_valid_to IS NULL).
    • Tip: You can also use check_cols='all' to compare all columns in the SELECT query (excluding the unique key and standard metadata).
  • unique_key='CUSTOMER_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 customers_snapshot_check

On the first run, the table SNAPSHOTS.CUSTOMERS_SNAPSHOT_CHECK is created and populated with active records.


Step 4: Confirm Initial Snapshot State​

Query the snapshot table in Snowflake to verify that the rows were successfully ingested:

SELECT 
CUSTOMER_ID,
NAME,
EMAIL,
COUNTRY,
dbt_valid_from,
dbt_valid_to
FROM SNAPSHOTS.CUSTOMERS_SNAPSHOT_CHECK
ORDER BY CUSTOMER_ID;

Expected Output​

On this first run, all records are active.

[!IMPORTANT] Because the source table has no update timestamp, dbt populates dbt_valid_from using the execution time (the exact timestamp when dbt snapshot was run). dbt_valid_to remains null.

CUSTOMER_IDNAMEEMAILCOUNTRYdbt_valid_fromdbt_valid_to
1Alice Johnsonalice.johnson@example.comUSA2026-06-08 10:00:00null
2Carlos Riveracarlos.rivera@example.comMexico2026-06-08 10:00:00null

Step 5: Simulate an Update in the Source​

Simulate a record change by executing an UPDATE statement in Snowflake:

UPDATE DEMO_DB.RAW.CUSTOMERS_CHECK
SET
EMAIL = 'alice.new@example.com',
COUNTRY = 'Canada'
WHERE CUSTOMER_ID = 1;

Step 6: Run the Snapshot Again​

Run the snapshot command a second time:

dbt snapshot --select customers_snapshot_check

dbt scans the selected check columns (EMAIL and COUNTRY) and compares them against the stored snapshot. Since customer 1 has different values, dbt will update the active records.


Step 7: Validate Historical Records​

Query the snapshot table to check the resulting SCD Type 2 history:

SELECT 
CUSTOMER_ID,
NAME,
EMAIL,
COUNTRY,
dbt_valid_from,
dbt_valid_to
FROM SNAPSHOTS.CUSTOMERS_SNAPSHOT_CHECK
WHERE CUSTOMER_ID = 1
ORDER BY dbt_valid_from;

Expected Output​

dbt has archived the old record and created a new active row.

CUSTOMER_IDNAMEEMAILCOUNTRYdbt_valid_fromdbt_valid_to
1Alice Johnsonalice.johnson@example.comUSA2026-06-08 10:00:002026-06-08 10:15:00
1Alice Johnsonalice.new@example.comCanada2026-06-08 10:15:00null

How dbt_valid_from & dbt_valid_to behave in Check Strategy:​

  • Archived Row (Row 1):
    • dbt_valid_from = 2026-06-08 10:00:00 (The execution time of the first run).
    • dbt_valid_to = 2026-06-08 10:15:00 (The execution time of the second run, when the change was detected).
  • Active Row (Row 2):
    • dbt_valid_from = 2026-06-08 10:15:00 (The execution time of the second run, when the change became active).
    • dbt_valid_to = null (Currently active record).