I have one table where incremental data comes from on-prem database (oracle) along with column database_operation having values ‘I’ if new data been added to DB,‘U’ for any existing row been updated and ‘D’ for any deleted row from oracle database. I need to keep the same table in snowflake in sync with database. Usually I do this using snowflake streams but currently using dbt.
Using dbt incremental model I am able to achieve insert and update with help of merge query dbt perform on behind the scene. So how I can achieve delete operation on table in snowflake in incremental row is having database_operation value as ‘D’ .It should first check if primary key is same or different. If same then the row should get delete and if different the row should get added in snowflake table. anyone can help?
example from traditional merge query:
t1.column1=t2.column1 AND t1.column2=t2.column2
WHEN MATCHED and t2.“database_operation” IN (‘I’, ‘U’) THEN UPDATE
WHEN NOT MATCHED AND t2.“database_operation” <> ‘D’ THEN INSERT
WHEN MATCHED AND t2.“database_operation” = ‘’ THEN DELETE → need solution for this