COF-C03 Question 115
Single answerMaterializedA retail analytics team has a large SALES table that receives continuous inserts throughout the day. Analysts frequently run the following query to support near-real-time dashboards:
SELECT store_id, sale_date, SUM(net_amount) FROM sales WHERE sale_date >= CURRENT_DATE - 7 AND status = 'COMPLETE' GROUP BY store_id, sale_date;
The team wants to reduce query latency without changing the analysts' SQL. A Snowflake architect is evaluating whether to create a materialized view on SALES. Which statement best describes the most appropriate expectation for this solution?
- A
Create a materialized view with the same filtering and aggregation logic; Snowflake can automatically use it to accelerate compatible queries against the base table, but maintenance costs will increase as new rows are inserted.
- B
Create a materialized view and instruct analysts to query it directly, because Snowflake does not rewrite queries on the base table to use materialized views.
- C
Create a materialized view because it is refreshed only when analysts query it, so it adds no background maintenance overhead during data loads.
- D
Create a materialized view because it can include any SQL construct from a standard view, including joins across multiple base tables and complex subqueries, making it ideal for this dashboard.
Show answer and explanation
Correct answer: A
Explanation
The best answer is Option 1 because it captures both the main benefit and the main tradeoff of Snowflake materialized views in a realistic scenario. Materialized views can precompute expensive filtering and aggregation logic on a single base table and may be used automatically by the query optimizer to speed up compatible queries without changing user SQL. This makes them well suited for repetitive dashboard queries. However, unlike standard views, materialized views incur maintenance cost as underlying data changes. In a high-ingest table such as SALES, architects must weigh faster query performance against additional compute and storage usage for keeping the materialized view current. Snowflake documentation and best practices emphasize both automatic query rewrite eligibility and the ongoing maintenance overhead of materialized views, as well as their SQL limitations compared with standard views.
- A. Correct.
Correct. Snowflake materialized views are designed to improve performance for repeated queries over the same precomputed result, especially when the base table is large and the query pattern is stable. Snowflake's optimizer can transparently rewrite eligible queries on the base table to use the materialized view, so analysts may not need to change their SQL. However, because the SALES table is continuously receiving inserts, Snowflake must maintain the materialized view in the background, which increases compute and storage costs. This tradeoff is a key consideration when deciding whether a materialized view is appropriate.
- B. Incorrect.
Incorrect. This reflects a common misconception. In Snowflake, the optimizer can automatically use a materialized view to satisfy compatible queries issued against the base table. Directly querying the materialized view is possible, but it is not required for performance benefits in eligible cases.
- C. Incorrect.
Incorrect. Snowflake materialized views are not refreshed only on demand by analyst queries. They are maintained by Snowflake as the underlying base table changes, which means DML activity such as frequent inserts can create ongoing maintenance overhead. Assuming there is no background cost is inaccurate and can lead to poor architectural decisions.
- D. Incorrect.
Incorrect. Snowflake materialized views are more restricted than standard views. They do not support all SQL constructs that a normal view can use, and they are intended for a narrower set of query patterns. Treating them as a drop-in replacement for any complex view definition, especially with broad support for joins and complex subqueries, is not accurate for SnowPro Core-level knowledge.