Snowflake动态表拆分CTEs测试与官方建议不符的疑问

我最初创建了包含CTE的单动态表final_tab_v1，语句如下：

CREATE DYNAMIC TABLE final_tab_v1
TARGET_LAG = '5 minutes'
AS
With tab_1 as (select fields from db.schema.table_1)
,tab_2 as (select fields from tab_1 left join db.schema.table_2)
,tab_3 as (select fields from tab_2 left join db.schema.table_3)
,tab_4 as (select fields from tab_3 left join db.schema.table_4)
select * from tab_4

根据Snowflake官方建议：

将大型公共表表达式(CTE)拆分为多个小部分，为每个部分创建一个动态表。避免单个动态表承载过多的聚合或关联操作。

我基于6400万条原始数据，使用中型仓库做了拆分测试：将原CTE拆分为三个依赖的动态表，依次完成左连接：

动态表1（dt1） #

CREATE DYNAMIC TABLE tab_1
TARGET_LAG = DOWNSTREAM
AS
select fields from db.schema.table_1
left join db.schema.table_2

动态表2（dt2） #

CREATE DYNAMIC TABLE tab_2
TARGET_LAG = DOWNSTREAM
AS
select fields from tab_1
left join db.schema.table_3

动态表3（dt3） #

CREATE DYNAMIC TABLE final_tab_v2
TARGET_LAG = '5 minutes'
AS
select fields from tab_2
left join db.schema.table_4

测试结果显示：单动态表版本首次创建加载耗时4分32秒；拆分后的三个动态表首次创建耗时分别为2分33秒、1分40秒、2分51秒，总耗时反而更长。由此产生疑问：测试是否存在问题？是否误解了官方建议？这类场景是否不该拆分CTE为多个动态表？

问题分析与解答 #

1. 官方建议的核心是增量刷新，而非首次全量加载
   官方建议针对的是动态表的长期增量刷新性能，而非首次全量创建的速度。单动态表首次全量是一次性执行整个CTE逻辑，集中利用计算资源；而拆分后的动态表因为存在链式依赖（dt1完成后才触发dt2，dt2完成后触发dt3），首次全量是串行执行，总耗时自然更长。但后续当源表有增量数据更新时，拆分后的动态表只会重新计算受影响的环节：比如仅table_1有数据变化时，只会重新刷新dt1，dt2和dt3仅需处理dt1的增量输出，而单动态表需要重新执行整个CTE的全量或更大范围的增量计算，长期来看增量刷新的效率会更高。

2. 测试场景的局限性
   本次测试仅覆盖了首次全量创建的场景，没有模拟后续增量刷新的真实业务场景，这才是官方建议要解决的核心问题。如果你的业务场景中动态表需要频繁处理增量数据，拆分的优势会逐渐体现。

3. 拆分的合理性取决于CTE复杂度
   如果原CTE只是简单的左连接链，拆分的首次性能损耗可能比较明显；但如果CTE中包含大量聚合、窗口函数、复杂过滤等计算逻辑，拆分后每个动态表可以缓存中间结果，避免重复计算，无论是首次全量还是增量刷新，收益都会更显著。

4. 资源利用的优化空间
   拆分后的动态表如果是非链式依赖的中间步骤，可以配置并行刷新，但本次测试的左连接是链式依赖，只能串行执行。如果业务允许，可以评估是否调整中间表的依赖关系，或者使用更大规格的仓库来缩短首次创建的耗时。

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