Answer-first summary for fast verification
Answer: Add a clustering key to the table.
The Query Profile showing all micro-partitions being scanned indicates poor pruning efficiency, where Snowflake cannot eliminate irrelevant micro-partitions during query execution. Adding a clustering key (Option B) organizes data within micro-partitions based on the clustering key columns, which directly improves pruning when queries filter on those columns. This reduces the number of micro-partitions scanned, optimizing query performance. Option A (creating a view) does not address the pruning issue, as views are logical constructs that do not physically reorganize data. Option C (adding a LIMIT clause) limits the result set but does not reduce the underlying scan effort. Option D (adding Dynamic Data Masking) is for security and does not impact query performance or pruning.
Author: LeetQuiz Editorial Team
Ultimate access to all questions.
No comments yet.
A query with a WHERE clause is performing slower than anticipated. The Query Profile indicates that all micro-partitions are being scanned.
How can this query be optimized?
A
Create a view on the table.
B
Add a clustering key to the table.
C
Add a LIMIT clause to the query.
D
Add a Dynamic Data Masking policy to the table.