snowflake-cloud-data-platform - 雪花没有实现完整的SQL MERGE 语句？

Question

我正在尝试创建一个执行 MERGE 语句的 Snowflake 任务。
但是，Snowflake 似乎无法识别“when not match by target”或“when not match by source”语句。

create or replace task MERGE_TEAM_TOUCHPOINT
 warehouse = COMPUTE_WH
 schedule = '1 minute'
 when system$stream_has_data('TEAMTOUCHPOINT_CDC')
as
merge into dv.Team_Touchpoint as f using TeamTouchpoint_CDC as s
    on s.uniqueid = f.uniqueid
    when matched then 
        update set TEAMUNIQUEID = s.TEAMUNIQUEID,
                    TOUCHPOINTUNIQUEID = s.TOUCHPOINTUNIQUEID
    when not matched by target then 
                   insert (
                     ID,
                     UniqueID,
                     TEAMUNIQUEID,
                     TOUCHPOINTUNIQUEID                   
                   )
                 values (
                    s.ID,
                    s.UniqueID,
                    s.TEAMUNIQUEID,
                    s.TOUCHPOINTUNIQUEID
                 )
    when not matched by source then delete;

我怎样才能做到这一点？除了在javascript中创建一个存储过程来首先截断表然后插入临时表中的所有内容之外，真的没有其他方法吗？

Answer 1

队友建议的解决方法:

定义 MATCHED_BY_SOURCE基于完全连接，然后查看 a.col 或 b.col 是否为空:

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>
;

(与 https://stackoverflow.com/a/69095225/132438 相同)