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_deletesconfiguration - 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.
- It queries the
sourcetable and gets a list of all currentuser_ids. - It queries the target
snapshottable and looks at all rows wheredbt_valid_to IS NULL(all currently active records). - It performs a
LEFT ANTI JOIN(or equivalent) to find the difference. - If it finds a
user_idin 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=Trueto yourconfig()block to fix this. - When enabled, dbt will detect missing rows and properly set their
dbt_valid_totimestamp, retiring the record.