Incremental Materialization - How to update a group of rows based on group id

Help
,
1

The problem I’m having

I am looking for a kind of incremental materialization, in which the new data for a group of rows should replace that group of rows in the destination table, using group id.

Destination table
--group_id  id  value
--g1.   id_1   12
--g1.  id_2   34
--g1.   id_3   55

New data (id_3 is deleted)
--group_id  id  value
--g1.   id_1   333
--g1.  id_2   564

Desired results
--group_id  id  value
--g1.   id_1   333
--g1.  id_2   564

Actual results
--group_id  id  value
--g1.   id_1   333
--g1.  id_2   564
--g3.   id_3   55

The context of why I’m trying to do this

In the sale order data that I am working with, there are order_id and order_index colums, and the surrogated unique_id is a concat of order_id and order_index. I am using incremental materialization to save cost, and it works most of the time. However, there’re issues when an order_index is deleted in raw data. This incremental materialization fails to update correctly.

What I’ve already tried

I am using my surrogated uniqued_id in the config but the result table is like.

Actual final table (the id_3 is still there)
--group_id  id  value
--g1.   id_1   333
--g1.  id_2   564
--g3.   id_3   55

Some example code or error messages

2

{{
config(
materialized=‘incremental’,
unique_key=‘group_id’,
incremental_strategy =‘delete+insert’
)
}}

you can try delete+insert incremental strategy

3

Thank you Surya for your reply. I thought with delete+insert strategy, a primary key is required. In this case, group_id is not primary key because it’s not unique.