Snapshot tables for historical tracking in dbt

Introduction

Snapshot tables help you keep a history of data changes over time. They let you see what data looked like at different points.

Tracking price changes of products over months.

Keeping a record of customer address updates.

Monitoring employee role changes in a company.

Saving daily status of orders for analysis.

Auditing changes in financial transactions.

Syntax

dbt

snapshots:
  - name: your_snapshot_name
    target_schema: your_schema
    strategy: timestamp
    updated_at: updated_at_column
    unique_key: id_column
    check_cols: ['column1', 'column2']
    source: your_source_table

strategy: Choose timestamp to track changes based on a timestamp column or check to track changes by comparing columns.

unique_key: This is the column that identifies each record uniquely.

Examples

This snapshot tracks product price changes using the last_updated timestamp.

dbt

snapshots:
  - name: product_price_snapshot
    target_schema: analytics
    strategy: timestamp
    updated_at: last_updated
    unique_key: product_id
    check_cols: ['price']
    source: raw.products

This snapshot tracks changes in customer addresses by checking if any address fields changed.

dbt

snapshots:
  - name: customer_address_snapshot
    target_schema: analytics
    strategy: check
    unique_key: customer_id
    check_cols: ['address', 'city', 'zip']
    source: raw.customers

Sample Program

This snapshot tracks changes in employee roles by comparing the role column. When you run dbt snapshot, it creates a table that keeps all historical role changes.

dbt

version: 2

snapshots:
  - name: employee_role_snapshot
    target_schema: analytics
    strategy: check
    unique_key: employee_id
    check_cols: ['role']
    source: raw.employees

-- Run this snapshot with dbt snapshot command to create and update the snapshot table.

Important Notes

Snapshots require a unique key to identify records.

Choose timestamp strategy if you have a reliable updated timestamp column.

Choose check strategy if you want to track changes by comparing specific columns.

Summary

Snapshot tables keep historical versions of data for tracking changes.

Use timestamp or check strategies depending on your data.

Run dbt snapshot to create and update snapshot tables.