Snowflake中50TB万亿级大表的查询优化求助

• 大表：50TB、1.2万亿行、14列，每日插入约160亿行，清理90天前数据，按date_col聚类（仅90个唯一值，插入有序，清理高效）
• 小表：450MB、1亿行
• 每周执行一次关联查询，预期结果集500亿行；当前用4XL仓库耗时60-70分钟，目标切换到2XL/3XL仓库以缩短耗时、降低信用消耗
• 已尝试：单独按高基数关联列（3.7亿唯一值，单值对应行数几条到数千条）聚类无效，按(date_col, 高基数列)聚类也无效果；Python存储过程因数据量过大无法存入DataFrame

select distinct
    t1.id,
    t1.segment_id,
    t2.device_id,
    case when t2.id_type = 'abc' then 1 else 0 end as device_type,
    any_value(date_trunc('minute', t2.timestamp)) as drive_time,
    any_value(t2.latitude) as latitude,
    any_value(t2.longitude) as longitude,
    any_value(t2.ip_address) as ip_address
from 
    small_table t1 
left join 
    big_table t2 on t2.high_cardinality_col_tried_for_clustering = t1.equivalent_col
                 and t2.date_col >= t1.date_col
group by 
    all;

• 移除冗余的DISTINCT：当前查询已使用GROUP BY ALL，SELECT列均为分组列或聚合函数，DISTINCT完全多余，会增加额外排序去重开销，直接删除即可。
• 调整聚类键顺序为(高基数列, date_col)：之前尝试的(date_col, 高基数列)顺序不合理，高基数列在前的聚类键会优先将相同关联键值的行聚集到同一微分区，配合date_col的过滤条件，能让Snowflake更精准地修剪微分区。虽然数据未按高基数列插入，但可以在非高峰时段手动触发增量聚类：

  ALTER TABLE big_table CLUSTER BY (high_cardinality_col_tried_for_clustering, date_col);
  

• 启用搜索优化服务：针对高基数关联列开启搜索优化，Snowflake会创建轻量级索引，直接定位到包含目标关联键值的微分区，避免全表扫描。执行语句：

  ALTER TABLE big_table ADD SEARCH OPTIMIZATION ON (high_cardinality_col_tried_for_clustering);
  

  该服务会产生额外存储成本，但能大幅减少远程I/O和扫描的微分区数量，信用消耗的降低通常能抵消存储开销。
• 预处理小表，提前过滤大表范围：先对小表做聚合，提取每个关联键对应的最小date_col，生成临时表后再与大表关联，让Snowflake能利用大表的date_col聚类做全局修剪：

  -- 生成预处理临时表
  CREATE OR REPLACE TEMP TABLE small_table_preprocessed AS
  SELECT equivalent_col, MIN(date_col) AS min_date
  FROM small_table
  GROUP BY equivalent_col;
  
  -- 优化后的关联查询
  SELECT
      t1.id,
      t1.segment_id,
      t2.device_id,
      CASE WHEN t2.id_type = 'abc' THEN 1 ELSE 0 END AS device_type,
      ANY_VALUE(DATE_TRUNC('minute', t2.timestamp)) AS drive_time,
      ANY_VALUE(t2.latitude) AS latitude,
      ANY_VALUE(t2.longitude) AS longitude,
      ANY_VALUE(t2.ip_address) AS ip_address
  FROM small_table t1
  JOIN small_table_preprocessed tp ON t1.equivalent_col = tp.equivalent_col
  LEFT JOIN big_table t2 
      ON t2.high_cardinality_col_tried_for_clustering = tp.equivalent_col
      AND t2.date_col >= tp.min_date
      AND t2.date_col >= t1.date_col
  GROUP BY ALL;
  

• 确认小表广播配置：确保会话级参数AUTO_SMALL_TABLE_BROADCAST = TRUE（默认开启），让Snowflake自动将小表广播到仓库的所有节点，避免大表数据 shuffle，提升关联效率。
• 仓库配置优化：使用多集群仓库（如3XL，设置最小集群数1，最大集群数3），开启自动缩放，让仓库在查询高峰自动扩容，空闲时缩容，平衡性能与信用消耗。
• 利用结果缓存（可选）：如果每周查询的小表数据变化不大，可以开启会话级参数USE_CACHED_RESULT = TRUE，或者将查询结果存入永久表，后续分析直接复用，避免重复计算。

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