Snowflake流场景：RANK函数与自连接取最新记录的效率对比

问题背景 #

使用场景：从源表实时变更数据捕获（CDC）至Snowflake Stream，通过Task定期消费该Stream，将变更记录合并（INSERT/UPDATE）至目标表，最终实现目标表与源表完全一致。
问题背景：当源表中同一主键（如ID）存在多条更新记录时，需从变更Stream中提取每个ID对应的最新修改记录（即updated_timestamp最大的记录）来执行目标表更新。

现有两种提取方法 #

方法1：使用RANK窗口函数 #

select * from (
select *, RANK() OVER(PARTITION BY ID ORDER BY updated_timestamp desc) as rnk
from STREAM
) X
where rnk = 1

方法2：使用子查询与自连接 #

select A.*
from STREAM A
join (select ID, max(updated_timestamp) AS max_updated_timestamp
from STREAM B
GROUP BY ID
) B
ON A.ID = B.ID
AND A.updated_timestamp = B.max_updated_timestamp

海量数据集场景下的效率分析 #

在海量数据场景中，RANK窗口函数的方法通常效率更高、耗时更短，核心原因如下：

• 单次扫描完成计算：窗口函数仅需对Stream数据集做一次全扫描，在扫描过程中同步完成分区、排序和排名计算，后续仅需过滤排名为1的记录。而自连接方法需要先扫描一次数据集做分组聚合（计算每个ID的最大时间戳），再扫描第二次并与聚合结果做关联，两次扫描的IO开销在数据量极大时会被显著放大。
• 优化器适配性更好：Snowflake的查询优化器对窗口函数有专门的优化逻辑，能充分利用数据的分区键、排序键减少数据shuffle和计算成本。自连接的关联操作在海量数据下会带来更高的内存和CPU消耗，尤其是当数据分布不均时，关联的性能损耗会更明显。
• 分区扫描优势放大：样本测试中RANK方法扫描分区更少的特性，在海量数据下会进一步体现——窗口函数可以在单个分区内完成排名计算，无需跨分区做聚合后再关联，大幅降低了数据移动的开销。

需要注意：如果同一ID存在多条updated_timestamp完全相同的最新记录，RANK会返回所有匹配记录（排名相同），自连接方法也会得到同样结果。若业务需要仅保留一条，可将RANK替换为ROW_NUMBER()，此时结果一致且窗口函数的效率优势依然存在。

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