Skip to main content

Hard Deletes in Snapshots

In previous modules, we learned how dbt Snapshots handle UPDATES. When a row changes in the source system (e.g., Arjun moves from Mumbai to Delhi), dbt detects the change, expires the old row, and inserts a new one.

But what happens if a row is completely deleted from the source system?

For example, what if a customer in our Indian e-commerce app formally requests their account to be deleted under privacy laws? The software engineering team runs a DELETE FROM app_users WHERE user_id = 101; command.

This is called a Hard Delete.

Topics Covered​

  • Default Snapshot behavior for Deletes
  • The invalidate_hard_deletes configuration
  • How dbt detects missing rows

1. Default Snapshot Behavior​

By default, dbt Snapshots ignore hard deletes.

If Arjun (User 101) is deleted from the source database on Tuesday, and you run dbt snapshot on Wednesday, dbt will simply look at the source table, realize Arjun isn't there, and do nothing.

In your Snowflake snapshot table, Arjun's record will still have a dbt_valid_to of NULL. According to your data warehouse, Arjun is still an active, current customer! This will cause massive discrepancies between your Tableau reports and the actual application.

2. The invalidate_hard_deletes Configuration​

To fix this, you must explicitly tell dbt to watch out for missing rows. You do this by adding invalidate_hard_deletes=True to your snapshot configuration block.

-- snapshots/snp_app_users.sql

{% snapshot snp_app_users %}

{{
config(
target_database='BEEMS_PROD',
target_schema='SNAPSHOTS',
unique_key='user_id',
strategy='timestamp',
updated_at='last_modified_at',

-- Add this line!
invalidate_hard_deletes=True
)
}}

SELECT * FROM {{ source('ecommerce_app', 'users') }}

{% endsnapshot %}

3. How does it work under the hood?​

When you run dbt snapshot with this setting enabled, dbt performs a slightly more expensive operation.

  1. It queries the source table and gets a list of all current user_ids.
  2. It queries the target snapshot table and looks at all rows where dbt_valid_to IS NULL (all currently active records).
  3. It performs a LEFT ANTI JOIN (or equivalent) to find the difference.
  4. If it finds a user_id in the snapshot table that no longer exists in the source table, it knows a Hard Delete occurred!

The Result​

dbt will update Arjun's record in the snapshot table. It will set dbt_valid_to to the exact timestamp that the dbt snapshot command was run.

Now, your data analysts know exactly when Arjun's account ceased to exist, and any downstream WHERE dbt_valid_to IS NULL filters will correctly exclude him from active user metrics.

Summary​

  • By default, dbt snapshots do not track hard deletes.
  • If a row is deleted from the source, dbt leaves it as "active" in the snapshot table.
  • You must add invalidate_hard_deletes=True to your config() block to fix this.
  • When enabled, dbt will detect missing rows and properly set their dbt_valid_to timestamp, retiring the record.