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 adbt_valid_totimestamp) 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).
- Tip: You can also use
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_fromusing the execution time (the exact timestamp whendbt snapshotwas run).dbt_valid_toremainsnull.
| CUSTOMER_ID | NAME | COUNTRY | dbt_valid_from | dbt_valid_to | |
|---|---|---|---|---|---|
| 1 | Alice Johnson | alice.johnson@example.com | USA | 2026-06-08 10:00:00 | null |
| 2 | Carlos Rivera | carlos.rivera@example.com | Mexico | 2026-06-08 10:00:00 | null |
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_ID | NAME | COUNTRY | dbt_valid_from | dbt_valid_to | |
|---|---|---|---|---|---|
| 1 | Alice Johnson | alice.johnson@example.com | USA | 2026-06-08 10:00:00 | 2026-06-08 10:15:00 |
| 1 | Alice Johnson | alice.new@example.com | Canada | 2026-06-08 10:15:00 | null |
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).