can we convert delete SQL statements into DBT?

Question

I am trying to build a DBT model from SQL which has delete statements based on where clause.

Can any one please suggest me how to convert the below SQL delete statement into DBT model?

''' delete table_name where condition;

'''

Thanks

Answer 1

There's a couple of options for running DELETE statements in dbt:

• add a DELETE statement as a pre_hook or post_hook for an existing model
• create an operation macro to run a DELETE statement independently of a model

Note that unless your model materialization type is "incremental" it doesn't make much sense to delete from the model target.

Disclaimer: I haven't been using dbt for long so there might well be better ways of doing this, or reasons to not do it at all.
Not sure what your use case is but I've had to use DELETEs when retrofitting existing data warehouse logic into dbt. If you're starting from scratch with dbt then probably try and avoid a design that requires deleting data.

Answer 2

I have needed to implement deletes to comply with CCPA deletion requirements. Our raw layer is drop&rebuild daily, so if a row does not exist in raw, it will need to be deleted in downstream tables.

Stage layer is a set of views that rename and cast raw tables, and also create surrogate key as sha1(raw_table_business_key). Pre_hook for EDW incrementally loaded tables is something like:

delete from {{ this }} where skey not in 
(select skey from {{ ref('stage_view') }})

Yes, it absolutely restates history.