How to delete rows from a destination table and insert new rows into destination table from a different source table?

Help
1

The problem I’m having

I have many source tables and a destination table, lets call them Destination and Src_1.

I would like to delete rows from Destination where Destination.SOURCE_NAME = Src_1.SOURCE_NAME and insert all rows from Src_1 if Src_1.LATEST_CHANGE is > the latest LATEST_CHANGE from Destination table.

The context of why I’m trying to do this

Currently trying to replicate a process that updates the Destination table in python but using dbt instead.

Not sure how to implement this as I’m confused how to apply incremental models when it involves different source tables and 1 destination table.

2

Rather than deleting data, you’ll probably want to use an incremental model with a unique key. That way if the unique key is already in the table, then it will UPDATE instead of DELETE + INSERT.

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

1 Like
3

Would you be able to provide some test code for this??