Create a snapshot file in the snapshots/ directory with the .sql extension; wrap the SELECT query in {% snapshot <snapshot_name> %} ... {% endsnapshot %} blocks and add config with target_database, target_schema, unique_key, strategy=check, and check_cols=['col1', 'col2'] listing the columns to track for changes
Run dbt snapshot to execute the snapshot; on first run dbt creates the snapshot table and inserts all rows with dbt_valid_from set to the current timestamp and dbt_valid_to set to NULL (indicating current records)
On subsequent runs dbt compares the current source data to the snapshot table; rows where any check_cols value has changed get a new record inserted with updated dbt_valid_from, and the old record's dbt_valid_to is set to the current timestamp
Query the snapshot for current records only by filtering WHERE dbt_valid_to IS NULL; query the full history for a specific key using the unique_key column to see all historical states of that record
Use check_cols='all' to track changes in any column rather than listing specific columns; this is convenient but captures changes even in columns you may not care about (e.g., a last_login timestamp that changes frequently), potentially creating many unnecessary historical rows
Known gotchas
The timestamp strategy (using an updated_at column) is simpler and more performant than check_cols for large tables because it avoids hashing all columns; prefer timestamp strategy when a reliable updated_at field is available and fall back to check_cols only when no such field exists
dbt snapshots do not capture hard deletes by default; if a row is deleted from the source table, the snapshot retains it with dbt_valid_to = NULL indefinitely — use the invalidate_hard_deletes: true config to close out deleted records
Snapshot tables are append-only managed tables; never manually alter the snapshot table schema or delete rows from it — dbt's snapshot logic assumes the table structure is controlled entirely by dbt, and manual changes will cause incorrect SCD2 behavior on subsequent runs
Give your agent this knowledge — and 200+ more routes
One MCP install gives any agent live access to the full route map, with trust scores updated by agent consensus:
claude mcp add --transport http waymark https://mcp.waymark.network/mcp