A query containing a WHERE clause is running longer than expected. The Query Profile shows that all micro-partitions being scanned How should this query be optimized?

The correct answer is B. Add a clustering key to the table. When a Query Profile shows 100% of micro-partitions are being scanned despite a WHERE clause filter, it means Snowflake cannot prune any micro-partitions - the data is not organized in a way that aligns with the filter column. Adding a clustering key on the filter column…

Performance Management

Question

Options

ACreate a view on the table.
BAdd a clustering key to the table
CAdd a limit clause to the query.
DAdd a Dynamic Data Masking policy to the table.

How the community answered

(31 responses)

A
6% (2)
B
74% (23)
C
16% (5)
D
3% (1)

Explanation

When a Query Profile shows 100% of micro-partitions are being scanned despite a WHERE clause filter, it means Snowflake cannot prune any micro-partitions - the data is not organized in a way that aligns with the filter column. Adding a clustering key on the filter column reorganizes micro-partitions so their min/max metadata aligns with the filter, enabling aggressive pruning. Creating a view (A) does not change the physical data layout. A LIMIT clause (C) reduces output rows but not the data scanned. Data masking (D) is a security feature with no effect on scan performance.

Topics

#Query Optimization#Clustering Key#Micro-partitions#Performance Tuning

Community Discussion

No community discussion yet for this question.

Full COF-C02 Practice