Snowflake snowsql merge statement for change tracking table - when not matched by target/source not allowed

Question

I am trying to write a query logic to capture all changes from the source.

Merge should compare the keys in source and target and process the data accordingly. Below is an example, how can we achieve this in SQL server.

MERGE <target_table> [AS TARGET]
USING <table_source> [AS SOURCE]
ON <search_condition>
[WHEN MATCHED 
   THEN <merge_matched> ]
[WHEN NOT MATCHED [BY TARGET]
   THEN <merge_not_matched> ]
[WHEN NOT MATCHED BY SOURCE
   THEN <merge_matched> ];

In snowflake, I get below error while trying something similar, Error SQL compilation error: syntax error line 7 at position 17 unexpected 'by'. syntax error line 8 at position 17 unexpected 'by'.

Is there a way in snowflake to handle 'when not matched by target' and 'when not matched by source' logic?

Thank you.

Terrigenous

#66490037 – Micmac

Answer 1

A workaround suggested by a teammate:

• Define MATCHED_BY_SOURCE based on a full join, and look if a.col or b.col are null:

merge into TARGET t
using (
    select <COLUMN_LIST>,
    iff(a.COL is null, 'NOT_MATCHED_BY_SOURCE', 'MATCHED_BY_SOURCE') SOURCE_MATCH,
    iff(b.COL is null, 'NOT_MATCHED_BY_TARGET', 'MATCHED_BY_TARGET') TARGET_MATCH
    from SOURCE a
    full join TARGET b
    on a.COL = b.COL
) s
on s.COL = t.COL
when matched and s.SOURCE_MATCH = 'NOT_MATCHED_BY_SOURCE' then
<DO_SOMETHING>
when matched and s.TARGET_MATCH = 'NOT_MATCHED_BY_TARGET' then
<DO_SOMETHING_ELSE>
;

Somewise

Thank you @Felipe Hoffa – Terrigenous

Answer 2

In my case the answer suggested by @Felipe Hoffa was not working. It said to me than I could not execute an insert on a "when matched" clause.

Here is the work-aorund I found that corrects this (assuming your PK is "the_id" field):

merge into old_table t
using (
    select n.the_id as the_id_new, n.the_value as the_value_new, o.the_id as the_id_old, o.the_value as the_value_old,
    iff(n.the_id is null, false, true) is_in_new_data
    from new_table n
    full old_table o
    on n.the_id = o.the_id
) s
on t.the_id = s.the_id_old
when matched and s.is_in_new_data then
-- Behaviour when a row is in both new and old data. Ex:
update set t.the_value = s.the_value_new
when matched and not s.is_in_new_data then
-- Behaviour when a row is in old data but not in new one. Ex:
delete
when not matched then
-- Behaviour when a row is in new data but not in the old one. Ex:
    insert (the_id, the_value) values (s.the_id_new, s.the_value_new)
;

Pharyngeal