Configure dbt snapshots with SCD Type 2 using check_cols strategy to track changes in specific columns

Steps

1. 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
2. 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)
3. 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
4. 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
5. 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