如何在Snowflake中验证源表行值转目标表列的数据一致性

源表（假设表名为 source_table） #

目标表（假设表名为 target_table） #

方法1：反向转换后直接对比 #

把目标表拆回源表的行结构，再和源表做差集，就能快速找出不匹配的数据：

WITH target_unpivoted AS (
    -- 把目标表的apple列拆成行
    SELECT ID, 'apple' AS "Column", apple AS value FROM target_table
    UNION ALL
    -- 把目标表的banana列拆成行
    SELECT ID, 'banana' AS "Column", banana AS value FROM target_table
)
-- 找出源表有但目标表反向转换后没有的行，以及反过来的情况
SELECT '仅源表存在' AS 差异类型, ID, "Column", value FROM source_table
EXCEPT ALL
SELECT '仅源表存在' AS 差异类型, ID, "Column", value FROM target_unpivoted
UNION ALL
SELECT '仅目标表存在' AS 差异类型, ID, "Column", value FROM target_unpivoted
EXCEPT ALL
SELECT '仅目标表存在' AS 差异类型, ID, "Column", value FROM source_table;

如果查询结果为空，说明两行结构的数据完全一致；有结果的话，直接就能看到不匹配的内容。

方法2：聚合统计验证 #

通过分组聚合，对比同维度下的数值集合是否一致：

WITH source_agg AS (
    -- 源表按ID、列名分组，把对应的值按顺序拼成数组
    SELECT 
        ID,
        "Column",
        ARRAY_AGG(value ORDER BY value) AS 源表值集合
    FROM source_table
    GROUP BY ID, "Column"
),
target_agg AS (
    -- 目标表按ID分组，把apple、banana列的值分别拼成数组
    SELECT 
        ID,
        ARRAY_AGG(apple ORDER BY apple) AS apple值集合,
        ARRAY_AGG(banana ORDER BY banana) AS banana值集合
    FROM target_table
    GROUP BY ID
)
-- 关联对比聚合结果
SELECT 
    s.ID,
    s."Column",
    s.源表值集合,
    CASE s."Column" WHEN 'apple' THEN t.apple值集合 ELSE t.banana值集合 END AS 目标表值集合,
    CASE WHEN s.源表值集合 = CASE s."Column" WHEN 'apple' THEN t.apple值集合 ELSE t.banana值集合 END 
         THEN '一致' ELSE '不一致' END AS 验证状态
FROM source_agg s
JOIN target_agg t ON s.ID = t.ID;

只要所有行的「验证状态」都是一致，就说明转换过程没有丢数或错数。

方法3：逐行精准匹配（已知行对应关系时用） #

如果确定目标表的行顺序和源表同ID同列的行顺序完全对应（比如源表apple的10、8对应目标表两行的apple值），可以给行加序号后逐行对比：

WITH source_ranked AS (
    -- 给源表同ID同列的行加序号
    SELECT 
        ID,
        "Column",
        value,
        ROW_NUMBER() OVER (PARTITION BY ID, "Column" ORDER BY value) AS 行序号
    FROM source_table
),
target_ranked AS (
    -- 给目标表的行加序号
    SELECT 
        ID,
        apple,
        banana,
        ROW_NUMBER() OVER (PARTITION BY ID ORDER BY apple) AS 行序号
    FROM target_table
)
-- 按ID和行序号关联，逐行对比值
SELECT 
    s.ID,
    s.行序号,
    s."Column",
    s.value AS 源表值,
    CASE s."Column" WHEN 'apple' THEN t.apple ELSE t.banana END AS 目标表值,
    CASE WHEN s.value = CASE s."Column" WHEN 'apple' THEN t.apple ELSE t.banana END 
         THEN '一致' ELSE '不一致' END AS 验证状态
FROM source_ranked s
JOIN target_ranked t ON s.ID = t.ID AND s.行序号 = t.行序号;

这种方法能精准定位到哪一行的哪个值出现了不匹配。

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