Skip to main content

Testing Snapshots

Just like Sources, Seeds, and Models, Snapshots (Slowly Changing Dimensions) are a core part of your dbt Directed Acyclic Graph (DAG) and must be tested.

If your snapshot logic is flawed, or if the source data violates your unique key assumptions, you will create a corrupted history of your data. For example, you might end up with a customer having two active records simultaneously, which will break all downstream models that join on that customer.

Topics Covered​

  • Configuring tests for Snapshots
  • Testing the dbt_scd_id
  • Testing the original Primary Key
  • Testing the Validity Window

1. Configuring Tests for Snapshots​

To test a snapshot, you define the tests in a YAML file exactly like you do for models. By convention, this file should be placed in the snapshots/ directory alongside your .sql snapshot files.

Create a new file: snapshots/schema.yml

version: 2

snapshots:
- name: snp_app_customers
description: "SCD Type 2 snapshot of application customers."

columns:
# We must test BOTH the dbt-generated columns and the raw source columns!

- name: dbt_scd_id
description: "The surrogate key generated by dbt for this specific historical record."
tests:
- unique
- not_null

- name: customer_id
description: "The original primary key from the source."
tests:
- not_null

- name: dbt_valid_from
description: "The timestamp when this record became active."
tests:
- not_null

2. What to Test in a Snapshot​

Notice how the tests applied to a snapshot are slightly different than a standard model.

1. Test the Surrogate Key (dbt_scd_id), NOT the Primary Key​

In a normal model, you would put a unique test on customer_id. In a snapshot, you cannot put a unique test on customer_id. Why? Because if Arjun moves from Mumbai to Delhi, there will be two rows for Arjun in the snapshot table! One historical, one current. Instead, you must apply the unique test to dbt_scd_id (a unique hash generated by dbt for every historical row).

2. Test the Original Primary Key for not_null​

Even though customer_id is no longer unique, it must never be NULL. If the source system accidentally inserts a NULL customer_id, the snapshot logic will break.

3. Test the Validity Columns​

dbt automatically generates dbt_valid_from and dbt_valid_to. You must ensure dbt_valid_from is never NULL. (Note: Do not test dbt_valid_to for not_null, because the currently active record will always have a NULL value here!)

3. Advanced Snapshot Testing (Custom Data Tests)​

One of the most dangerous bugs in an SCD Type 2 table is "overlapping validity windows."

If Arjun moved from Mumbai to Delhi, the dbt_valid_to timestamp of the Mumbai record must be identical to the dbt_valid_from timestamp of the Delhi record.

You can write a custom generic test (or use the dbt-expectations package) to ensure that a single customer_id never has two active records at the exact same time.

# A conceptual example of a custom test
snapshots:
- name: snp_app_customers
tests:
- assert_no_overlapping_validity_windows:
primary_key: customer_id
valid_from: dbt_valid_from
valid_to: dbt_valid_to

4. Execution Workflow​

To run tests on your snapshots, use standard dbt CLI commands:

# Test a specific snapshot
dbt test --select snp_app_customers

# Run the unified build command (Snapshots, then Tests, then downstream Models)
dbt build

Summary​

  • Snapshots must be tested to ensure the historical integrity of your data.
  • Configure snapshot tests in snapshots/schema.yml.
  • Never apply a unique test to the original primary key (e.g., customer_id), because SCD Type 2 tables have multiple rows per entity.
  • Always apply unique and not_null tests to the surrogate key: dbt_scd_id.