DBT for BigQuery - Why is there no delete+insert by a primary key?

Question

This is more of a theoretical question: I have a scenario where I wish to do a delete + insert from a source table to a target table in DBT. (Match by PK, delete existing records then insert).

DBT doesn't seem to support this incremental strategy for BigQuery (It does for Snowflake).

It instead offers an insert+overwrite by deleting and re-inserting a given partition. Which doesn't solve my specific need.

Is there a reasoning behind this?

Answer 1

I think you want merge, which should accomplish the same as a delete and insert, but with better performance. BQ docs on merge statements.

delete+insert is only supported for Snowflake to support an edge case where the PK of the table is not actually unique. source. On Snowflake merge is also preferred/more performant.