Incremental model with deleted source rows

Help
1

I want to upgrade our dbt run to be incremental, but our source data has an is_deleted column to indicate that a row was actually deleted.

For our current run, we just filter those out, but if I want to make it incremental, I’m not finding any good way to delete rows from the target table that have been flagged.

Can anyone point me in the right direction?

1 Like
2

It depends on the adapter you’re using, but it seems to me it can be solved with a merge incremental strategy: https://docs.getdbt.com/docs/build/incremental-strategy#supported-incremental-strategies-by-adapter.

Note: @Dagner Fonseca (Cognitivo.ai) originally posted this reply in Slack. It might not have transferred perfectly.

3

I haven’t find a clause within dbt to perform delete on merge statements, so I think maybe you should use delete+insert strategy.

Note: @Dagner Fonseca (Cognitivo.ai) originally posted this reply in Slack. It might not have transferred perfectly.

4

This might be wrong, but if we’ve deleted a row from our source table, I don’t know if the delete plus insert will know to delete it.

5

<@U017LTDJK8A>
Delete records are end dated with the merge using scd2 methodology.

Note: @Nick Pinfold originally posted this reply in Slack. It might not have transferred perfectly.

6

<@U06KL2TALG6>, to my knowledge, merge commands produced by dbt only inserts or updates records on the target.

Is there a clause to perform deletes based on a condition?

Note: @Dagner Fonseca (Cognitivo.ai) originally posted this reply in Slack. It might not have transferred perfectly.

7

It’s a logical delete so end dating the record.
If you are after an scd1 style best to use insert only with a start date and the js deleted flag in an incremental
Then separate model with an analytics to get latest records and exclude the deletes

Note: @Nick Pinfold originally posted this reply in Slack. It might not have transferred perfectly.

8

To delete the records which are flagged after the incremental run, use the sql delete query in the post-hook of the model config block. Below block is for your reference:
{{
config(
post-hook=“delete from where is_deleted=‘True’”
)
}}

Note: @Manikanta Divvela originally posted this reply in Slack. It might not have transferred perfectly.

9

<@U06KL2TALG6>, the request from Adam Pitzer trough Community Discourse was to have a hard delete on the target.

<@U08HV94UQF2> presentes a workaround solution I’ve seen at some threads before, but as I see, dbt merge should implement delete over a condition that requires delete of records on target. It seems to be a cleaner solution as post-hook, IMO, was intended to handle specific operations in some databases like memory free or transaction close operations.

Note: @Dagner Fonseca (Cognitivo.ai) originally posted this reply in Slack. It might not have transferred perfectly.