Snowflake中CTAS建表语句为何忽略ORDER BY子句

执行的SQL命令如下：

drop table if exists metrics_done;
create table metrics_done as select * from metrics where end_morning='2022-03-31' order by LOG_INFO desc;

该现象的产生原因如下：

• 关系模型的基础定义决定了数据库表是无序的元组集合，不存在和表绑定的固有存储顺序、默认返回顺序。只要查询语句不主动加ORDER BY子句，返回结果的顺序完全由执行时的分区扫描路径、任务调度逻辑决定，和数据写入时的顺序没有强绑定关系。
• Snowflake是MPP架构的分布式云数仓，CTAS任务会被拆分到多个计算节点并行执行：每个节点仅处理自身负责的数据分片，写入操作也是多节点并行落盘到不同的微分区，SELECT语句中写的ORDER BY不会触发全局排序后按序写入存储的逻辑，自然无法维持全局有序的结果。
• Snowflake查询优化器会自动裁剪无收益的计算步骤：由于CTAS的目标是生成表数据，而表本身不要求有序，优化器判断CTAS内的ORDER BY不会给后续查询带来确定性收益时，会直接忽略该排序逻辑，减少不必要的计算资源消耗。

正确实现需求的方案：如果需要查询时获得按LOG_INFO降序的结果，必须在查询语句中显式添加ORDER BY LOG_INFO desc，这是唯一能稳定保证返回顺序的方式；如果是为了提升该字段相关查询的性能，可以在建表时通过CLUSTER BY LOG_INFO设置聚类键，让Snowflake自动维护微分区内的数据排序布局，不要依赖CTAS中的ORDER BY固定表内数据顺序。

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