Snowflake中基于order_id和sku_id的近2天数据Upsert方案咨询

问题背景 #

源表与目标表均包含order_id、sku_id、status、updated_date字段，需执行以下Upsert操作：

• 仅处理源表近2天的记录
• 当order_id+sku_id维度匹配时，更新目标表的status和updated_date为源表对应值
• 插入order_id+sku_id维度下的全新记录

原MERGE语句未得到预期结果，原代码如下：

merge target_table as t using
(select order_id, sku_id, status, updated_date
 from source_table 
 where date(updated_date) >= current_date -2) as s
on s.order_id = t.order_id and s.sku_id = t.sku_id
when matched then update set
t.order_id = s.order_id,
t.sku_id = s.sku_id,
t.status = s.status,
t.updated_date = s.updated_date
when not matched then 
insert (
order_id,
sku_id,
status,
updated_date
)
values(
s.order_id,
s.sku_id,
s.status,
s.updated_date
)
;

原代码问题分析 #

1. 匹配逻辑缺陷：原MERGE的ON条件仅匹配order_id+sku_id，但源表中存在同一维度组合的多条记录（如o1+s1有2条），Snowflake的MERGE会对每条源表匹配记录执行更新，最终目标表中该维度只会保留最后一条更新的结果，无法实现插入多条同维度记录的需求。
2. 冗余更新字段：更新order_id和sku_id属于无效操作，因为这两个字段是匹配条件，值必然相等。

正确解决方案 #

要实现需求（允许目标表存在同一order_id+sku_id的多条记录），需调整逻辑，以下两种方案遵循Snowflake最佳实践：

方案1：CREATE OR REPLACE TABLE 高效覆盖（优先推荐） #

如果目标表可以被替换，直接合并有效数据后重建表是最高效的方式，利用Snowflake列存储特性，性能远超逐行MERGE：

CREATE OR REPLACE TABLE target_table AS
-- 保留目标表中未被源表近2天数据覆盖的历史记录
SELECT t.*
FROM target_table t
LEFT JOIN (
    SELECT DISTINCT order_id, sku_id
    FROM source_table
    WHERE DATE(updated_date) >= CURRENT_DATE - 2
) s_match ON t.order_id = s_match.order_id AND t.sku_id = s_match.sku_id
WHERE s_match.order_id IS NULL
-- 合并源表近2天的所有记录
UNION ALL
SELECT order_id, sku_id, status, updated_date
FROM source_table
WHERE DATE(updated_date) >= CURRENT_DATE - 2;

方案2：MERGE + 匹配条件扩展（适合增量更新场景） #

如果必须保留目标表历史且不能全量替换，可扩展匹配条件，仅更新目标表中同维度的旧记录，同时插入源表新记录：

MERGE INTO target_table t
USING (
    SELECT order_id, sku_id, status, updated_date
    FROM source_table
    WHERE DATE(updated_date) >= CURRENT_DATE - 2
) s
ON t.order_id = s.order_id 
AND t.sku_id = s.sku_id
AND DATE(t.updated_date) < CURRENT_DATE - 2 -- 仅更新非近2天的历史匹配记录
WHEN MATCHED THEN
    UPDATE SET t.status = s.status, t.updated_date = s.updated_date
WHEN NOT MATCHED THEN
    INSERT (order_id, sku_id, status, updated_date)
    VALUES (s.order_id, s.sku_id, s.status, s.updated_date);

最佳实践建议 #

• 优先选CREATE OR REPLACE TABLE：Snowflake的列存储架构下，重建表的IO效率远高于MERGE的逐行匹配，尤其适合数据量较大的场景。
• 过滤条件优化：使用DATE_TRUNC('day', updated_date) >= DATEADD('day', -2, CURRENT_DATE)替代DATE(updated_date) >= CURRENT_DATE -2，若updated_date有索引，性能会更优。
• 精简更新字段：MERGE的UPDATE语句只更新需要变更的字段（status和updated_date），避免更新匹配条件中的字段。

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