Snowflake中亿级表联合查询去重ID并优先取表2数据的高效方案

针对你提到的数十亿条记录的大表合并需求——去重ID且重复时优先保留table_2的数据，最高效的实现方式分为两种，具体选择取决于你的表结构和数据分布：

方案一：优先取table_2 + 补充table_1中独有的数据 #

这种方式直接拆分逻辑，避免全表排序，性能最优，尤其适合id列带有聚类键（CLUSTER BY）或主键的场景：

-- 先取table_2的全部数据
SELECT id, col_1, col_2 FROM table_2
UNION ALL
-- 再取table_1中ID不存在于table_2的数据
SELECT t1.id, t1.col_1, t1.col_2
FROM table_1 t1
WHERE NOT EXISTS (
    SELECT 1 FROM table_2 t2 
    WHERE t2.id = t1.id
);

优势： #

• 利用Snowflake的微分区修剪（Micro-Partition Pruning），当id是聚类键时，NOT EXISTS只会扫描table_1中未在table_2出现的分区，大幅减少数据扫描量
• 无需全表排序或窗口函数计算，资源消耗远低于其他方案

方案二：合并后用窗口函数筛选优先级数据 #

如果你的表没有合适的聚类键，或者需要更灵活的优先级规则，可以用窗口函数实现：

SELECT id, col_1, col_2
FROM (
    -- 给table_2标记更高优先级（1），table_1标记次优先级（2）
    SELECT id, col_1, col_2, 1 AS priority FROM table_2
    UNION ALL
    SELECT id, col_1, col_2, 2 AS priority FROM table_1
) combined
-- 按ID分组，取优先级最高的第一条数据
QUALIFY ROW_NUMBER() OVER (PARTITION BY id ORDER BY priority) = 1;

注意： #

• 这种方式需要扫描两张表的全部数据，然后进行分组排序，对于数十亿条记录的大表，资源消耗会比方案一大，仅在方案一无法有效利用分区修剪时使用

关键优化点 #

• 确保id列设置为聚类键（CLUSTER BY）：Snowflake会按id对数据分区，查询时能快速定位需要扫描的分区
• 避免使用NOT IN：NOT IN在存在NULL值时会返回意外结果，且优化器对NOT EXISTS的处理效率更高

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