COF-C03 Question 284
Single answerMaterialized viewsA retail analytics team has a very large SALES table that receives frequent inserts throughout the day. Most dashboard queries filter on REGION = 'EMEA' and aggregate daily revenue by PRODUCT_ID. Query latency is inconsistent during peak hours, so the team wants to improve performance without changing the dashboard SQL. Which solution is the MOST appropriate?
- A
Create a materialized view that precomputes the filtered and aggregated result set for REGION = 'EMEA' by PRODUCT_ID and sales date.
- B
Create a standard view on SALES with the existing dashboard query and rely on result cache to ensure consistent performance for all users.
- C
Convert the SALES table into a temporary table so that repeated dashboard queries scan less data during business hours.
- D
Create a stream on SALES so dashboard queries can read only changed rows and avoid scanning the base table.
Show answer and explanation
Correct answer: A
Explanation
The best answer is to create a materialized view tailored to the common dashboard access pattern. In Snowflake, materialized views are useful when queries repeatedly access a small subset or a transformed subset of a large base table, especially when the computation is expensive and reused often. Snowflake maintains the materialized view automatically as the base table changes, and the optimizer may transparently use it even when users query the base table directly, which is important here because the dashboard SQL should remain unchanged. Standard views do not improve performance by themselves, and result cache is opportunistic rather than a design-time performance solution for frequently changing data. Streams are for change tracking and data pipelines, not query acceleration. This aligns with Snowflake best practices and documentation for materialized views: use them selectively for repeated, expensive query patterns over large tables, while considering the maintenance cost caused by ongoing DML on the base table.
- A. Correct.
Correct. A materialized view stores precomputed query results and is automatically maintained by Snowflake as the base table changes. For a large fact table with repeated queries that filter on a selective predicate such as REGION = 'EMEA' and perform aggregation, a materialized view can reduce the amount of data scanned and improve response time. Because Snowflake's optimizer can transparently rewrite queries to use a compatible materialized view, this approach aligns well with the requirement to improve performance without changing the dashboard SQL.
- B. Incorrect.
Incorrect. A standard view does not store data; it simply stores the query definition. Result cache can help only when the exact same query result is reusable under the right conditions, such as unchanged underlying data and compatible session settings. In this scenario, the base table receives frequent inserts, which can reduce cache reusability. Result cache also does not guarantee consistent performance for all users and all executions.
- C. Incorrect.
Incorrect. Temporary tables are session-scoped objects and do not inherently improve scan performance for repeated dashboard queries against a production analytics workload. Converting a persistent shared fact table into a temporary table would break normal multi-session access patterns and does not address the need for precomputed filtered aggregations.
- D. Incorrect.
Incorrect. Streams track change data capture information for tables and views; they are intended for downstream processing of inserts, updates, and deletes, not for accelerating BI dashboard queries. A stream would not automatically make dashboard queries read only the changed rows or replace the need to aggregate historical sales data efficiently.