A table, SALES, is reloaded daily, and is used by many users to support multiple data TABLE reporting tools. Users report poor performance when querying the table. This is the clustering information…

Question

Options

• AIncorporate additional pruning options when the table is being accessed by reporting queries.
• BIdentify and purge duplicate data in the table.
• CUse the ORDER BY SALES_DT parameter when inserting the data into the table.
• DPerform a one-time insert overwrite with the ORDER BY SALES_DT parameter.

• A
  9% (3)
• B
  3% (1)
• C
  3% (1)
• D
  84% (27)

Explanation

Option D is correct because the clustering information reveals a severely degraded table: average_depth of 10798 (equal to total partitions) and all partitions landing in the "16384+" histogram bucket means every partition overlaps with every other - Snowflake cannot prune anything. A one-time INSERT OVERWRITE ... ORDER BY SALES_DT physically rewrites and reorganizes all micro-partitions in sorted order, restoring effective clustering in a single operation without ongoing cost.

Why the distractors fail:

• A - Adding query-side pruning hints doesn't fix the underlying physical disorder; Snowflake's pruning only works when partitions are actually clustered.
• B - Duplicate data affects correctness and storage, not micro-partition overlap; the histogram shows a clustering problem, not a data quality problem.
• C - ORDER BY on future inserts helps new data but leaves the existing 10,798 disordered partitions untouched, so historical queries remain slow.

Memory tip: Think of it as "nuke and rebuild" - when average_depth ≈ total_partition_count, the table is maximally unclustered and needs a full rewrite (INSERT OVERWRITE), not a patch. The histogram bucket "16384" holding all partitions is your signal that nothing can be pruned.

No community discussion yet for this question.