COF-C03 Question 277
Single answer4.2 Optimize query performanceA retail company stores 8 TB of order history in a Snowflake table named SALES_FACT. Analysts frequently run dashboard queries that filter on ORDER_DATE ranges and REGION, but the queries have become slower as the table has grown. A Snowflake administrator reviews the query profile and notices a large amount of data is being scanned from micro-partitions for these filters. The dashboards run many times each hour, and the SQL text is similar but the date ranges vary. Which action would most directly improve query performance for this workload?
- A
Define a clustering key on ORDER_DATE and REGION for SALES_FACT
- B
Create a materialized view on SALES_FACT without any filtering or aggregation
- C
Increase the warehouse size permanently for all dashboard workloads
- D
Enable search optimization service only on columns returned in the SELECT list
Show answer and explanation
Correct answer: A
Explanation
This question focuses on a core Snowflake performance concept: micro-partition pruning. Snowflake automatically stores metadata about micro-partitions, and query performance improves when filters allow Snowflake to skip irrelevant partitions. If a large table is commonly filtered on columns such as ORDER_DATE and REGION, and query profile shows too much data scanned, clustering can improve how rows are co-located across micro-partitions and make pruning more effective. This is especially relevant for large tables with frequent range predicates. A larger warehouse can mask the symptom with more compute but does not optimize storage pruning. Materialized views are valuable for repeated expensive transformations, joins, or aggregations, but not as a general fix for unclustered scan-heavy filtering. Search Optimization Service is a different feature aimed at accelerating selective lookups and certain predicate patterns; it is not the best first choice for broad date-range dashboard filters on a large fact table. These recommendations align with Snowflake best practices on clustering keys, query profiling, and choosing the right optimization feature for the workload.
- A. Correct.
Correct. When a large table is frequently filtered by specific columns and the query profile shows excessive micro-partition scanning, defining a clustering key on those filter columns can improve pruning and reduce the amount of data scanned. In this scenario, ORDER_DATE and REGION are common filter predicates, so clustering the table on those columns is the most direct optimization.
- B. Incorrect.
Incorrect. A materialized view is most useful when it precomputes expensive joins, aggregations, or a filtered subset that is repeatedly queried. Creating one on the same base table without meaningful filtering or aggregation would usually add maintenance cost without directly addressing the micro-partition pruning problem described.
- C. Incorrect.
Incorrect. Increasing warehouse size may reduce elapsed time through more compute, but it does not directly solve poor pruning or excessive data scanning. It can also increase cost unnecessarily for a workload that is suffering from table organization issues rather than insufficient compute.
- D. Incorrect.
Incorrect. Search optimization service helps specific point lookup and selective predicate patterns, especially for highly selective searches, but enabling it only on columns in the SELECT list does not address filtering performance. The main issue here is scanning caused by filters on ORDER_DATE and REGION, which is better addressed by clustering in this scenario.