Snowflake Merge操作忽略重复行 解决DML重复错误

问题场景 #

现有两张表：

src表 #

dest表 #

dest表已有一行与src表第一行完全相同，执行Merge操作时触发以下错误：

100090 (42P18): Duplicate row detected during DML action
Row Values: ["123abc", "xxyy", "MM", "2022-12-14T00:00:00.000Z", "2022-10-20T00:00:00.000Z", "2023-01-17T00:00:00.000Z", "paint", "pink", 3.3, "dd"]

当前使用的Merge语句 #

merge into cleaned_delivery dest
using ( 
SELECT distinct
  RECORD_CONTENT:ID::varchar AS ID,
  RECORD_CONTENT:model::varchar AS model,
  RECORD_CONTENT:accy::varchar AS accy,
  RECORD_CONTENT:delivery_date::varchar AS delivery_date,
  RECORD_CONTENT:ETA::varchar AS ETA,
  RECORD_CONTENT:call_off::varchar AS call_off,
  RECORD_CONTENT:department::varchar AS department,
  RECORD_CONTENT:style::varchar AS style, 
  RECORD_CONTENT:duration::float AS duration,    
  RECORD_CONTENT:plant::varchar AS plant

FROM raw_delivery where RECORD_CONTENT:ID

) as src (ID, model, accy, delivery_date, ETA, call_off, department, style, duration, plant)
on dest.ID = src.ID and dest.model = src.model and dest.accy = src.accy 
when matched
then update set
  dest.delivery_date = src.delivery_date,
  dest.ETA = src.ETA,
  dest.call_off = src.call_off,
  dest.department = src.department,
  dest.style = src.style,
  dest.duration = src.duration
when not matched then insert (ID, model, accy, delivery_date, ETA, call_off, department, style, duration, plant)
  values (ID, model, accy, delivery_date, ETA, call_off, department, style, duration, plant);

已尝试的无效修改 #

在when matched后添加差异判断条件，但报错依旧：

when matched and (src.delivery_date != dest.delivery_date or src.ETA!= dest.ETA or src.call_off!= dest.call_off)

解决方案 #

错误原因 #

报错核心是src中存在多条匹配dest同一条记录的行，Merge操作要求每个目标行最多被匹配一次。当前src里3条行的ID+model+accy完全相同，会同时匹配dest的同一行，触发冲突。原DISTINCT无法解决，因为这3条行的call_off字段不同，会被保留为独立行。

具体修复步骤 #

1. 对src数据做唯一化处理
   通过GROUP BY按匹配键（ID、model、accy）分组，聚合其他字段（比如取最新的call_off日期），确保每个匹配键对应唯一一行：

   SELECT 
     RECORD_CONTENT:ID::varchar AS ID,
     RECORD_CONTENT:model::varchar AS model,
     RECORD_CONTENT:accy::varchar AS accy,
     MAX(RECORD_CONTENT:delivery_date::varchar) AS delivery_date,
     MAX(RECORD_CONTENT:ETA::varchar) AS ETA,
     MAX(RECORD_CONTENT:call_off::varchar) AS call_off, -- 按业务需求选择聚合逻辑，比如取最新日期
     MAX(RECORD_CONTENT:department::varchar) AS department,
     MAX(RECORD_CONTENT:style::varchar) AS style, 
     MAX(RECORD_CONTENT:duration::float) AS duration,    
     MAX(RECORD_CONTENT:plant::varchar) AS plant
   FROM raw_delivery 
   WHERE RECORD_CONTENT:ID IS NOT NULL
   GROUP BY ID, model, accy
   

   如果需要更复杂的行选择逻辑（比如取最新插入的行），也可以用窗口函数ROW_NUMBER()来筛选唯一行。

2. 优化Merge的更新判断逻辑
   保留字段差异判断，仅在src和dest字段不同时执行更新，避免无意义操作。

完整修复后的Merge语句 #

merge into cleaned_delivery dest
using ( 
  SELECT 
    RECORD_CONTENT:ID::varchar AS ID,
    RECORD_CONTENT:model::varchar AS model,
    RECORD_CONTENT:accy::varchar AS accy,
    MAX(RECORD_CONTENT:delivery_date::varchar) AS delivery_date,
    MAX(RECORD_CONTENT:ETA::varchar) AS ETA,
    MAX(RECORD_CONTENT:call_off::varchar) AS call_off,
    MAX(RECORD_CONTENT:department::varchar) AS department,
    MAX(RECORD_CONTENT:style::varchar) AS style, 
    MAX(RECORD_CONTENT:duration::float) AS duration,    
    MAX(RECORD_CONTENT:plant::varchar) AS plant
  FROM raw_delivery 
  WHERE RECORD_CONTENT:ID IS NOT NULL
  GROUP BY ID, model, accy
) as src (ID, model, accy, delivery_date, ETA, call_off, department, style, duration, plant)
on dest.ID = src.ID and dest.model = src.model and dest.accy = src.accy 
when matched and (
  dest.delivery_date != src.delivery_date 
  or dest.ETA != src.ETA 
  or dest.call_off != src.call_off
  or dest.department != src.department
  or dest.style != src.style
  or dest.duration != src.duration
)
then update set
  dest.delivery_date = src.delivery_date,
  dest.ETA = src.ETA,
  dest.call_off = src.call_off,
  dest.department = src.department,
  dest.style = src.style,
  dest.duration = src.duration
when not matched then insert (ID, model, accy, delivery_date, ETA, call_off, department, style, duration, plant)
  values (ID, model, accy, delivery_date, ETA, call_off, department, style, duration, plant);

内容的提问来源于stack exchange，提问作者somedude