Deletes

One of the biggest differences between a procedural transformation and how dbt models data is that dbt, in general, will never destroy data. While there are ways to execute hard DELETEs in dbt that are outside of the scope of this article, the general best practice for handling deleted data is to just use soft deletes, and filter out soft-deleted data in a final transformation.

Let’s consider a simple example query:

DELETE FROM stg_orders WHERE order_status IS NULL

In a dbt model, you’ll need to first identify the records that should be deleted and then filter them out. There are really two primary ways you might translate this query:

SELECT * FROM {{ ref('stg_orders') }} WHERE order_status IS NOT NULL

This first approach just inverts the logic of the DELETE to describe the set of records that should remain, instead of the set of records that should be removed. This ties back to the way dbt declaratively describes datasets. You reference the data that should be in a dataset, and the table or view gets created with that set of data.

Another way you could achieve this is by marking the deleted records, and then filtering them out. For example:

WITH

soft_deletes AS (

    SELECT
        *,
        CASE
            WHEN order_status IS NULL THEN true
            ELSE false
        END AS to_delete

    FROM {{ ref('stg_orders') }}

)

SELECT * FROM soft_deletes WHERE to_delete = false

This approach flags all of the deleted records, and the final SELECT filters out any deleted data, so the resulting table contains only the remaining records. It’s a lot more verbose than just inverting the DELETE logic, but for complex DELETE logic, this ends up being a very effective way of performing the DELETE that retains historical context.

It’s worth calling out that while this doesn’t enable a hard delete, hard deletes can be executed a number of ways, the most common being to execute a dbt macros via as a run-operation, or by using a post-hook to perform a DELETE statement after the records to-be-deleted have been marked. These are advanced approaches outside the scope of this guide.