COF-C03 Question 276
Single answer4.2 Optimize query performanceA retail company stores 4 years of order history in a large Snowflake table named SALES_FACT. Analysts frequently run queries such as:
SELECT customer_id, SUM(order_total) FROM sales_fact WHERE order_date BETWEEN '2024-11-01' AND '2024-11-30' AND region = 'WEST' GROUP BY customer_id;
The table is loaded continuously in timestamp order, and query history shows that similar reports scan far more data than expected. The company wants to improve performance for these selective queries without changing the SQL used by analysts. Which action is the best choice?
- A
Define a clustering key on columns commonly used for selective filtering, such as (order_date, region), to improve micro-partition pruning
- B
Create a materialized view on the entire SALES_FACT table without a filter so all future queries read from the materialized view automatically
- C
Convert SALES_FACT to a temporary table so Snowflake caches the data more efficiently for repeated report queries
- D
Increase the virtual warehouse size for all analyst workloads, because larger warehouses eliminate the need for partition pruning
Show answer and explanation
Correct answer: A
Explanation
Snowflake query performance often depends on how effectively it can prune micro-partitions. When a large table is queried with selective filters, especially on columns such as dates or regions, clustering can improve the physical organization of data and reduce unnecessary scanning. In this scenario, the table is loaded continuously in timestamp order, but the workload filters on both order_date and region. If the existing micro-partition layout does not align well with these access patterns, defining a clustering key on commonly filtered columns can improve pruning and performance without requiring analysts to rewrite their SQL.
This aligns with Snowflake best practices for large tables with selective, repeated filter patterns. By contrast, simply increasing warehouse size adds compute but does not address excessive data scanning. Materialized views can help in some cases, but they should be used selectively for well-defined repeated query patterns because they incur storage and maintenance costs. Temporary tables are unrelated to optimizing shared reporting queries on a persistent fact table.
Relevant Snowflake documentation and best practices include topics on micro-partitions, clustering keys, and query profile analysis for understanding bytes scanned and pruning effectiveness.
- A. Correct.
Correct. For large tables with selective predicates, defining an appropriate clustering key can improve micro-partition pruning and reduce the amount of data scanned. In this scenario, analysts regularly filter by order_date and region, so clustering on these columns can help Snowflake organize data to make pruning more effective. Because the company does not want to change analyst SQL, improving data layout is a strong fit.
- B. Incorrect.
Incorrect. Materialized views can improve performance for specific repeated query patterns, but creating one on the entire base table without a targeted aggregation or filter is usually not an efficient optimization. It also does not mean all future queries will automatically benefit in a meaningful way. Materialized views are best when they precompute expensive, frequently reused subsets or aggregations, and they introduce maintenance cost.
- C. Incorrect.
Incorrect. Temporary tables are session-scoped objects used for short-lived data, not a performance optimization for a large shared fact table. Converting a production fact table to temporary would make it unavailable outside the session and does not improve pruning behavior or long-term query performance.
- D. Incorrect.
Incorrect. A larger warehouse can provide more compute and may reduce execution time in some cases, but it does not eliminate the importance of micro-partition pruning. If queries are scanning much more data than necessary, improving pruning is usually a better optimization than simply adding compute. Scaling up can increase cost while leaving inefficient data access patterns in place.