Snapshot hard deletes and new snapshot config

dbt snapshots capture historical state changes in source tables using Type 2 slowly changing dimensions. In dbt 1.9, the handling of source deletions is managed by the hard_deletes configuration, which determines whether disappeared records are ignored, closed out with a validity timestamp, or recorded as explicit tombstone rows.

Hard delete tracking modes

When a record is deleted from an upstream database, dbt detects that the primary key no longer appears in the source query. The hard_deletes config offers three distinct behaviors:

• ignore is the default setting. dbt leaves the most recent snapshot row untouched, leaving dbt_valid_to as null. The snapshot behaves as if the record still exists in the source.
• invalidate updates the existing active row by setting dbt_valid_to to the current snapshot execution timestamp. This closes the record validity window and replaces the older invalidate_hard_deletes boolean setting.
• new_record closes the existing row and inserts a brand new snapshot row with dbt_is_deleted set to true. This provides an explicit audit trail showing exactly when the deletion occurred.

These options give teams precise control over deletion semantics.

Defining snapshots in YAML

Starting in dbt 1.9, snapshots can be defined directly inside YAML files under the top-level snapshots key, matching how models and semantic models are declared:

snapshots:
  - name: snap_customers
    relation: source('raw', 'customers')
    config:
      unique_key: customer_id
      strategy: timestamp
      updated_at: updated_at
      hard_deletes: new_record

This YAML syntax eliminates the need for standalone SQL snapshot files wrapped in Jinja blocks.

Strategy recap: timestamp versus check

dbt snapshots identify row modifications using one of two strategies:

• timestamp strategy uses a reliable updated_at column from the source table. dbt checks if the timestamp is newer than the stored snapshot row. This is the preferred and most efficient method.
• check strategy computes a hash across a list of monitored columns when the source table lacks a reliable timestamp. If any monitored column value changes, dbt generates a new version of the record.

Choose timestamp whenever the source system provides reliable update timestamps, and use check when working with legacy tables lacking audit columns.