Snowflake增量加载copy_history数据时，含CTE的MERGE无法更新控制表

问题分析 #

你当前的MERGE语句存在几个关键问题导致无法更新控制表：

1. INSERT字段缺失：插入操作未指定source_entity字段，这会导致新插入的记录缺少匹配标识，后续MERGE无法正确匹配，也不符合表结构要求。
2. 无增量时无输出：当CopyHistoryDelta没有增量数据时，USING子查询返回空结果，MERGE不会执行任何更新或插入操作，无法维持控制表的时间戳状态。
3. 时间戳逻辑不准确：使用CURRENT_TIMESTAMP作为更新时间戳，会导致控制表记录的时间与copy_history中实际的增量边界不一致，可能引发重复加载或漏加载。

修正后的解决方案 #

调整逻辑，以增量数据的最大LAST_LOAD_TIME作为控制表的更新值，同时处理无增量和首次加载的场景，确保MERGE语句稳定执行：

WITH LastLoad AS (
    -- 处理首次加载：若控制表无记录，默认取1970-01-01以全量拉取数据
    SELECT COALESCE(MAX(LAST_TIMESTAMP), '1970-01-01'::TIMESTAMP) AS last_load_time
    FROM edw_dev.meta.adf_load_control
    WHERE source_entity = 'COPY_HISTORY'
),
MaxDeltaTime AS (
    -- 获取本次增量数据的最大LAST_LOAD_TIME；若无增量，沿用原时间戳
    SELECT COALESCE(MAX(ch.LAST_LOAD_TIME), (SELECT last_load_time FROM LastLoad)) AS new_last_time
    FROM snowflake.account_usage.copy_history ch
    WHERE ch.LAST_LOAD_TIME > (SELECT last_load_time FROM LastLoad)
)
MERGE INTO edw_dev.meta.adf_load_control ut
USING (
    -- 明确指定source_entity，确保匹配和插入的正确性
    SELECT 
        'COPY_HISTORY' AS source_entity,
        new_last_time
    FROM MaxDeltaTime
) AS new_data
ON ut.source_entity = new_data.source_entity
WHEN MATCHED THEN
    UPDATE SET LAST_TIMESTAMP = new_data.new_last_time
WHEN NOT MATCHED THEN
    -- 插入时补全source_entity字段
    INSERT (source_entity, LAST_TIMESTAMP)
    VALUES (new_data.source_entity, new_data.new_last_time);

关键修改说明 #

• 首次加载处理：LastLoad中用COALESCE设置默认时间，确保控制表无记录时能全量拉取copy_history数据。
• 无增量兼容：MaxDeltaTime中用COALESCE保证即使没有增量数据，也能返回原时间戳，MERGE会正常执行更新（维持原时间）或插入（首次加载场景）。
• 准确时间戳：用增量数据的最大LAST_LOAD_TIME作为控制时间，确保下次增量加载的边界准确，避免数据重复或遗漏。
• 字段完整性：INSERT操作明确指定source_entity字段，符合表结构要求，保证后续MERGE能正确匹配记录。

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