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

Morality

To answer this well, it would be useful to understand some more context. What's the reason for wanting to delete records? – Altercation

I think we can write select query where not in (specify condition), this will exclude the records – Morality

@SQLpro docs.getdbt.com/docs/building-a-dbt-project/building-models – Cordelier

I commented on the answer, but also leaving this here: discourse.getdbt.com/t/… – Atone

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.

Cordelier

This is technically something you COULD do, but this probably isn't you SHOULD do. Deleting records is antithetical to the dbt framework of ELT. Anyone who thinks they need this solution should familiarize themselves with the dbt viewpoint, starting with this article: discourse.getdbt.com/t/…. – Atone

Thats an extremely narrow minded take on the potential uses of dbt. – Cordelier

It's actually the viewpoint of the developers of dbt... Do whatever you want, but tools often work best when used as intended. – Atone

The developers of a screwdriver intend it to be used to fix screws, but I can also use one to open a tin of paint. I can say from experience that the reality of releasing tools into the wild is that people will always find uses for them that the developers have not envisaged, that does not mean those uses are wrong. Of course if someone were starting a greenfields data project from scratch it absolutely makes sense to stick to the dbt developer's recommended best practices as far as possible, but the real world is not always that accommodating. – Cordelier

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.

Quiz

This is why I am here in 2023 - wished we had a few more detailed answers though – Hexagram