SOL-C01 · Question #83
You are tasked with optimizing a Snowflake query that frequently uses the 'SALES' table. This table has a large number of rows, and users often filter the data based on the 'ORDER DATE column. You dec
The correct answer is A. The 'ORDER_DATE column has low cardinality, making clustering ineffective. B. The automatic clustering process has not yet completed re-clustering the table after the clustering C. The queries being executed are not filtering on the 'ORDER DATE column, thus not leveraging the. Clustering keys improve Snowflake performance by physically organizing micro-partitions so the engine can prune irrelevant ones during scans - but three conditions must be met for this to work. A is correct because low cardinality on ORDER_DATE (few distinct values) means many ro
Question
You are tasked with optimizing a Snowflake query that frequently uses the 'SALES' table. This table has a large number of rows, and users often filter the data based on the 'ORDER DATE column. You decide to create a clustering key on the 'ORDER DATE column. After creating the clustering key, you notice that the query performance has not improved as expected. What are the potential reasons for this lack of improvement?
Options
- AThe 'ORDER_DATE column has low cardinality, making clustering ineffective.
- BThe automatic clustering process has not yet completed re-clustering the table after the clustering
- CThe queries being executed are not filtering on the 'ORDER DATE column, thus not leveraging the
- DThe table is too small for clustering to provide a significant performance benefit.
- EThe table is not being maintained correctly, leading to high micro-partition fragmentation.
How the community answered
(43 responses)- A56% (24)
- D16% (7)
- E28% (12)
Explanation
Clustering keys improve Snowflake performance by physically organizing micro-partitions so the engine can prune irrelevant ones during scans - but three conditions must be met for this to work. A is correct because low cardinality on ORDER_DATE (few distinct values) means many rows share the same value, so Snowflake cannot effectively prune micro-partitions - the whole point of clustering. B is correct because after a clustering key is created, Snowflake's automatic clustering service must complete background re-clustering of existing data before any pruning benefit is realized - this takes time. C is correct because clustering only helps queries that filter on the clustered column; if the slow queries filter on different columns entirely, the clustering key is irrelevant to their execution.
Options D and E are wrong because the question explicitly states the table has a large number of rows (ruling out D), and E describes a maintenance failure after clustering is operational - not a root cause in the immediate post-creation scenario described here.
Memory tip: Use the acronym WUT - clustering fails when the column has the Wrong selectivity (low cardinality), the reclustering is Unfinished, or the Transactions/queries don't actually filter on that column.
Topics
Community Discussion
No community discussion yet for this question.