# Handling hard-deletes from source tables in Snapshots

**URL:** <https://discourse.getdbt.com/t/handling-hard-deletes-from-source-tables-in-snapshots/1005>\
**Category:** Archive\
**Created:** [March 30, 2020, 1:55am UTC](https://discourse.getdbt.com/t/handling-hard-deletes-from-source-tables-in-snapshots/1005 "2020-03-30T01:55:13Z")\
**Posts on this page:** 1\
**Showing post:** 2

<div class="post-metadata">

**Author:** ![ddaltonrtr](https://avatars.discourse-cdn.com/v4/letter/d/2bfe46/32.png) [@ddaltonrtr](https://discourse.getdbt.com/u/ddaltonrtr)\
**Post date:** [April 1, 2020, 6:06pm UTC](https://discourse.getdbt.com/t/handling-hard-deletes-from-source-tables-in-snapshots/1005/2 "2020-04-01T18:06:47Z")

</div>

Hi,

This is a great question and here’s the way we have gone about handling hard deletes:

1. Make a table with the view of your data (ex. schema.current\_table), but add a pre-hook that creates a table based on what is currently in prod (ex. `CREATE OR REPLACE TABLE schema.previous_table AS SELECT * FROM schema.current_table`). The table created via the pre-hook will help you identify deletes.
2. Create a table that has the deleted records by using the MINUS operator (disclosure: I use Snowflake). The new table will be called something like schema.table\_deleted\_records.

```auto
SELECT
   column_a
  , column_b
FROM schema.table_previous

MINUS
SELECT
  column_a
  , column_b
FROM schema.table_current

```

1. Then in another table, union the data and add a column is\_deleted (FALSE for current data, TRUE for deleted\_records).

```auto
    SELECT
      column_a
     , column_b
    , TRUE AS is_deleted
    FROM schema.previous_table
    INNER JOIN schema.table_deleted_records
       ON << unique key >>

    UNION

    SELECT
     column_a
     , column_b
     , FALSE AS is_deleted
    FROM schema.current_table

```

1. Then, you will run your snapshots on the table created in Step 3 with the check strategy (we know that it is best to use a timestamp, so we do that elsewhere for tables that don’t have a hard delete).

This is just what has worked for us and is by no means the only way to go about it. For example, you could join based on the historical snapshot table as you mentioned. I think the `is_deleted` column is better than overwriting the dbt\_valid\_to column.

---

_[View the full topic](https://discourse.getdbt.com/t/handling-hard-deletes-from-source-tables-in-snapshots/1005)._