COF-C03 Question 114
Single answerMaterializedA retail analytics team runs the same dashboard query thousands of times per day against a large SALES table. The query filters on ORDER_DATE and REGION, then computes SUM(NET_AMOUNT) grouped by REGION and PRODUCT_CATEGORY for the last 30 days. Data is continuously loaded into SALES throughout the day. The team wants to reduce query latency for this repeated workload without changing the dashboard SQL. Which Snowflake feature is the best fit?
- A
Create a materialized view on SALES that precomputes the filtered and aggregated result set used by the dashboard query
- B
Create a standard view on SALES with the same query, because Snowflake automatically stores view results for reuse across sessions
- C
Enable Search Optimization Service on the SALES table, because it pre-aggregates SUM(NET_AMOUNT) by REGION and PRODUCT_CATEGORY
- D
Replace the dashboard query with a temporary table that users manually refresh each morning
Show answer and explanation
Correct answer: A
Explanation
The best answer is to create a materialized view. In Snowflake, materialized views physically store precomputed query results and are automatically maintained based on changes to the base table. They are most useful when the same or similar query patterns are executed repeatedly and the cost of maintenance is justified by query performance gains. This scenario fits that pattern: frequent dashboard queries, repeated aggregation, filtering on recent data, and a need for lower latency.
A standard view would not help because it does not store data. Search Optimization Service is useful for improving access paths for highly selective filters, but it does not replace precomputed aggregations. A manually refreshed temporary table is operationally fragile and would not remain current for continuously loaded data.
This aligns with Snowflake best practices: use materialized views selectively for repeated, expensive query patterns over a single base table, and weigh improved query performance against ongoing maintenance cost and storage consumption.
- A. Correct.
Correct. A materialized view is designed for repeated queries over the same base table patterns, especially when precomputing expensive filtering, projection, and aggregation can reduce runtime. Snowflake automatically maintains the materialized view as the base table changes, making it well suited when data is continuously loaded and the dashboard SQL should benefit from optimizer rewrite without major application changes.
- B. Incorrect.
Incorrect. A standard view stores only the query definition, not precomputed results. Each query against a standard view still executes against the underlying base table data. While Snowflake has result caching, that is not the same as a standard view persisting computed results, and cache reuse depends on query text and data changes. This option reflects the misconception that all views materialize results.
- C. Incorrect.
Incorrect. Search Optimization Service can improve selective point lookup and certain predicate search patterns, but it does not pre-aggregate measures such as SUM(NET_AMOUNT) by dimensions. It is not a substitute for a materialized view when the goal is to accelerate repeated aggregation queries.
- D. Incorrect.
Incorrect. A temporary table manually refreshed each morning would quickly become stale because SALES is updated throughout the day. It also requires operational effort and changes to user workflows. This does not meet the requirement to reduce latency for a continuously changing dataset without changing the dashboard behavior.