Snowflake表数据校验：哈希对比动态SQL仅返回语句问题排查

问题核心 #

你当前的代码仅生成对比查询的SQL字符串，但没有实际执行它，所以看不到对比结果。另外代码里还有几处细节错误，比如表名引用错误、行关联逻辑不合理等，会导致对比结果不准确。

修正后的完整方案 #

修正后的可执行动态SQL #

使用Snowflake的EXECUTE IMMEDIATE执行生成的对比语句，同时修复代码中的错误：

-- Step 1: 提取两张表的列信息（schema_1.table_1 和 schema_2.table_2）
WITH column_list_schema_1 AS (
    SELECT COLUMN_NAME
    FROM INFORMATION_SCHEMA.COLUMNS
    WHERE TABLE_SCHEMA = 'schema_1'  
      AND TABLE_NAME = 'table_1'
),
column_list_schema_2 AS (
    SELECT COLUMN_NAME
    FROM INFORMATION_SCHEMA.COLUMNS
    WHERE TABLE_SCHEMA = 'schema_2'
      AND TABLE_NAME = 'table_2'
),
-- Step 2: 生成对比用的动态SQL片段
dynamic_sql AS (
    SELECT 
        LISTAGG(
            -- 修正NULL值对比逻辑，用IS DISTINCT FROM替代!=
            'CASE WHEN h."' || COLUMN_NAME || '" IS DISTINCT FROM s."' || COLUMN_NAME || '" THEN ''' || COLUMN_NAME || ''' END AS "' || COLUMN_NAME || '"', 
            ', '
        ) WITHIN GROUP (ORDER BY COLUMN_NAME) AS column_comparisons,
        -- 生成单列哈希，用于拼接行哈希
        LISTAGG(
            'MD5(CAST(h."' || COLUMN_NAME || '" AS STRING))', ', '
        ) WITHIN GROUP (ORDER BY COLUMN_NAME) AS schema_1_hash,
        LISTAGG(
            'MD5(CAST(s."' || COLUMN_NAME || '" AS STRING))', ', '
        ) WITHIN GROUP (ORDER BY COLUMN_NAME) AS schema_2_hash
    FROM column_list_schema_1  -- 修复原代码错误：替换column_list_heroku为正确表名
    WHERE COLUMN_NAME IN (SELECT COLUMN_NAME FROM column_list_schema_2)
)
-- Step 3: 生成并执行最终对比查询
EXECUTE IMMEDIATE (
    SELECT 
        'SELECT h.row_id AS h_row_id, 
                s.row_id AS s_row_id, 
                h.parent_id AS parent_id, 
                MD5(CONCAT(' || ds.schema_1_hash || ')) AS h_row_hash, 
                MD5(CONCAT(' || ds.schema_2_hash || ')) AS s_row_hash, 
                CASE WHEN MD5(CONCAT(' || ds.schema_1_hash || ')) = MD5(CONCAT(' || ds.schema_2_hash || ')) 
                     THEN ''Match'' ELSE ''Mismatch'' END AS comparison_result, 
                ' || ds.column_comparisons || '
         FROM 
             -- 用业务唯一键排序生成行号，保证行匹配稳定
             (SELECT *, ROW_NUMBER() OVER (ORDER BY parent_id) AS row_id FROM schema_1.table_1) h 
         FULL OUTER JOIN 
             (SELECT *, ROW_NUMBER() OVER (ORDER BY parent_id) AS row_id FROM schema_2.table_2) s 
         ON h.parent_id = s.parent_id  -- 用业务唯一键关联，替代原不合理的row_id关联
         WHERE MD5(CONCAT(' || ds.schema_1_hash || ')) != MD5(CONCAT(' || ds.schema_2_hash || ')) 
            OR h.row_id IS NULL 
            OR s.row_id IS NULL;'  -- 捕获只在单表存在的行
    FROM dynamic_sql ds
);

关键修正与优化说明 #

• 执行动态SQL：用EXECUTE IMMEDIATE()包裹生成的SQL字符串，让Snowflake直接执行并返回对比结果，而不是只输出语句文本
• 修复表名错误：将原代码中错误的column_list_heroku替换为column_list_schema_1
• 合理关联行：原代码用无排序的ROW_NUMBER()生成的行号关联不可靠（无排序时行号顺序随机），改为用业务唯一键parent_id关联，同时生成行号时按该字段排序，保证行匹配的稳定性
• 正确处理NULL值：用IS DISTINCT FROM替代!=，可以准确识别NULL与非NULL值的差异
• 捕获缺失行：WHERE子句添加h.row_id IS NULL OR s.row_id IS NULL，避免漏掉只在其中一张表存在的行

额外优化建议 #

如果表数据量较大，可以先快速验证整体一致性，再定位差异行：

-- 验证全表哈希是否一致
SELECT MD5(CONCAT_AGG(MD5(CAST(* AS STRING)))) AS schema1_full_hash FROM schema_1.table_1;
SELECT MD5(CONCAT_AGG(MD5(CAST(* AS STRING)))) AS schema2_full_hash FROM schema_2.table_2;

如果全表哈希一致，说明数据完全相同，无需再执行差异查询；如果不一致，再用上面的动态SQL定位具体差异行。

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