Snowflake动态表无法更新行及存在重复记录的解决方法

问题分析 #

• 原动态表仅直接读取源表全量数据，未做去重和最新记录筛选处理，导致重复记录留存
• 未基于记录的更新顺序判断同ID下的最新NAME值，无法实现更新覆盖效果

修改后的动态表创建语句 #

CREATE OR REPLACE DYNAMIC TABLE names
TARGET_LAG = '1 minute'
WAREHOUSE = "COM_WH"
SELECT ID, NAME
FROM (
    SELECT 
        ID, 
        NAME,
        -- 按ID分组，按源表记录的更新时间倒序排序，标记每条记录的序号
        ROW_NUMBER() OVER (PARTITION BY ID ORDER BY SOURCE_TABLE.UPDATE_TIMESTAMP DESC) AS rn
    FROM SOURCE_TABLE
)
WHERE rn = 1;

关键说明 #

• 窗口函数ROW_NUMBER()：按ID分组后，对每组内的记录按时间倒序排序，rn=1会筛选出每个ID对应的最新记录，自动去重并覆盖旧值
• 源表时间列要求：如果源表没有自带UPDATE_TIMESTAMP（记录创建/更新时间）列，建议给源表新增该列，设置默认值为CURRENT_TIMESTAMP()，确保每次插入或更新记录时自动填充时间，这样才能准确判断最新记录
• 动态表刷新逻辑：修改后的查询会在每次动态表刷新时，重新计算每个ID的最新记录，自动同步源表的更新操作，满足期望的输出结果

替代方案（源表无法新增时间列时） #

若无法修改源表结构，可利用动态表刷新时间做近似判断（准确性略差）：

CREATE OR REPLACE DYNAMIC TABLE names
TARGET_LAG = '1 minute'
WAREHOUSE = "COM_WH"
SELECT ID, NAME
FROM (
    SELECT 
        ID, 
        NAME,
        ROW_NUMBER() OVER (PARTITION BY ID ORDER BY CURRENT_TIMESTAMP() DESC) AS rn
    FROM SOURCE_TABLE
)
WHERE rn = 1;

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