I have a table that I need to update incrementally in snowflake. Today I use the following query:
MERGE INTO incremental_table t using ( with co as ( select distinct * from ( select COL_A, COL_B, COL_C, COL_D, COL_E, COL_F from source_table where COL_A is not null ) ) select * from co ) s ON s.COL_A = t.COL_A s.COL_B = t.COL_B s.COL_C = t.COL_C WHEN MATCHED AND s.COL_D < t.COL_D THEN UPDATE SET t.COL_D = s.COL_D WHEN MATCHED AND s.COL_E is not null AND t.COL_E is null THEN UPDATE SET t.COL_E = s.COL_E WHEN MATCHED AND s.COL_F is not null AND t.COL_F is null THEN UPDATE SET t.COL_F = s.COL_F WHEN NOT MATCHED THEN INSERT ( col_a, col_b, col_c, col_d, col_e, col_f ) VALUES ( s.col_a, s.col_b, s.col_c, s.col_d, s.col_e, s.col_f ) ;
I trying to figure out how to best model that in dbt? As you can see different columns get updated depending on the various (non unique key fields). Is there a way to add this statement to dbt and get it to increment the model, or do I need to break it up into a set of selects, one for each “WHEN MATCHED” and “WHEN NOT MATCHED” statement?
Any help or docs explaining that would be useful or how have others solved this?
Thanks in advance