COF-C03 Question 95
Single answerData clusteringA retail company stores 8 TB of order history in a Snowflake table named ORDERS. Analysts frequently run queries that filter on ORDER_DATE ranges and COUNTRY, but rarely select all rows. The table receives a continuous stream of new records throughout the day. Query profiles show that many queries scan far more micro-partitions than expected. The company wants to improve pruning performance without manually re-clustering after each load. Which action should the Snowflake administrator take?
- A
Define a clustering key on (ORDER_DATE, COUNTRY) and enable Automatic Clustering for the table
- B
Create a search optimization service on every column in the table and disable micro-partition pruning
- C
Convert the table to a temporary table so that new rows are physically sorted before queries run
- D
Create a materialized view that selects all columns from ORDERS without filters, and query the view instead
Show answer and explanation
Correct answer: A
Explanation
Snowflake stores table data in micro-partitions and uses metadata to prune partitions during query execution. When large tables are frequently filtered on certain columns, defining a clustering key can improve pruning by increasing the likelihood that similar values are grouped together in fewer micro-partitions. For a table with ongoing DML or continuous loads, Automatic Clustering is the recommended way to maintain clustering over time instead of relying on manual reclustering. This is especially appropriate when query patterns repeatedly use selective filters such as date ranges and geographic attributes. Search Optimization Service is a different optimization feature intended for particular lookup/search use cases, not a general substitute for clustering. Materialized views can help in some scenarios, but not when the core issue is poor pruning on the base table. See Snowflake documentation on clustering keys, micro-partitions, clustering depth, and Automatic Clustering for best practices.
- A. Correct.
Correct. A clustering key on columns commonly used in selective filters, such as ORDER_DATE and COUNTRY, can improve micro-partition pruning by organizing data so related values are stored closer together. Because the table is continuously loaded, enabling Automatic Clustering is the practical way to maintain clustering depth over time without manual reclustering operations. This is the standard Snowflake approach for large, frequently queried tables with selective predicates.
- B. Incorrect.
Incorrect. Search Optimization Service is designed for specific point lookup and selective search patterns, not as a replacement for clustering in broad range-filter scenarios. Also, micro-partition pruning is a core Snowflake optimization and is not something administrators disable to improve performance. This option combines a real feature with an invalid action, making it a plausible but incorrect distractor.
- C. Incorrect.
Incorrect. Temporary tables do not provide automatic physical sorting of new rows for query optimization, nor do they solve long-term clustering needs for a large shared analytics table. In addition, converting an important persistent order-history table to a temporary table would be operationally inappropriate because temporary tables are session-scoped.
- D. Incorrect.
Incorrect. A materialized view that simply mirrors all columns from the base table without changing the access path or pre-aggregating/filtering data is unlikely to address the pruning problem. Materialized views are most beneficial when they precompute a commonly reused subset, projection, join, or aggregation. They are not the primary solution for poor micro-partition pruning caused by suboptimal clustering.