SnowPro Advanced Data Engineer

By Snowflake · Question Mode

QuestionQ52

Design and Manage Snowflake Resources and Performance

A table, TABLE SALES, is reloaded every day and is used by many users to support multiple data-reporting tools. Users report poor query performance against the table.

This is the clustering information for TABLE SALES:

\{  
  "cluster_by_keys": "LINEAR(SALES_DT)",  
  "total_partition_count": 10798,  
  "total_constant_partition_count": 0,  
  "average_overlaps": 10797.0,  
  "average_depth": 10798.0,  
  "partition_depth__histogram": \{  
    "00000": 0,  
    "00001": 0,  
    "00002": 0,  
    "00003": 0,  
    "00004": 0,  
    "00005": 0,  
    "00006": 0,  
    "00007": 0,  
    "00008": 0,  
    "00009": 0,  
    "00010": 0,  
    "00011": 0,  
    "00012": 0,  
    "00013": 0,  
    "00014": 0,  
    "00015": 0,  
    "00016": 0,  
    "16384": 10798  
  \},  
  "clustering_errors": []  
\}

Which step will improve micro-partition performance and optimize query performance?

Explanation

Very high micro-partition overlap and depth, together with zero constant micro-partitions, indicate that the table is not well clustered on SALES_DT; effective partition pruning is therefore unlikely. Rewriting the entire table once with INSERT OVERWRITE while ordering rows by SALES_DT rebuilds its micro-partitions in clustering-key order. This reduces overlap and depth for existing data, so date-selective reporting queries can prune more micro-partitions. Snowflake documents that high overlap and depth indicate poor clustering and that reclustering reduces micro-partition scanning and improves query performance.

Learn more

Community Discussion

No comments yet. Be the first to start the discussion!