A Data Engineer is trying to improve the performance of a long-running query by clustering the base table. What information from the output of the system function will verify CLUSTERING_INFORMATION…

Question

Options

• AThe average number of overlapping micro-partitions in the table is high.
• BThe total number of constant micro-partitions in the table is high.
• CThe average overlap depth of each micro-partition in the table is high.
• DThe total number of constant micro-partitions in the table is low.

• A
  9% (5)
• B
  4% (2)
• C
  16% (9)
• D
  71% (40)

Explanation

Option D is correct because in Snowflake's SYSTEM$CLUSTERING_INFORMATION, a low count of constant micro-partitions - partitions where the clustering key's min and max values are identical - indicates the clustering process has successfully redistributed data so that partitions span meaningful, non-overlapping ranges of the clustering key rather than being fragmented into single-value chunks, which optimizes range-based query pruning.

Options A and C are both wrong for the same reason: high average overlapping micro-partitions (A) and high average overlap depth (C) are both symptoms of poor clustering, meaning Snowflake must scan more micro-partitions per query - exactly what clustering is meant to fix. You want these values low after clustering.

Option B is wrong because a high constant micro-partition count would signal excessive fragmentation - each partition holds only one distinct clustering key value - which means clustering reorganized the data too granularly rather than optimally grouping rows into prunable, range-bounded partitions.

Memory tip: Think of overlap as the enemy. After clustering, you want depth and overlaps to be LOW (like a neat stack of sorted cards with no interleaving). Constant partitions being LOW means the data is spread efficiently across ranges, not broken into countless single-value silos. When in doubt: low overlap + low constant = clustering success.

No community discussion yet for this question.