COF-C03 Question 285
Single answerMaterialized viewsA retail analytics team stores 4 years of order data in a large fact table named ORDERS. Analysts repeatedly run dashboards that aggregate only recent, completed orders using queries similar to: SELECT CUSTOMER_ID, SUM(ORDER_TOTAL) FROM ORDERS WHERE ORDER_STATUS = 'COMPLETE' AND ORDER_DATE >= CURRENT_DATE - 30 GROUP BY CUSTOMER_ID. The team wants to improve query performance without changing the dashboard SQL significantly. Which approach best uses a Snowflake materialized view to meet this requirement?
- A
Create a materialized view on ORDERS that pre-filters ORDER_STATUS = 'COMPLETE' and pre-aggregates SUM(ORDER_TOTAL) by CUSTOMER_ID and ORDER_DATE so the optimizer can use it for compatible queries.
- B
Create a standard view with the same query text because Snowflake automatically persists and incrementally maintains all standard views in the background.
- C
Create a materialized view that includes CURRENT_DATE in its definition so the last 30 days are recalculated automatically each day inside the materialized view.
- D
Create a materialized view on top of another materialized view so recent completed orders can be layered and reused by multiple teams.
Show answer and explanation
Correct answer: A
Explanation
The best answer is Option 1 because Snowflake materialized views are designed to improve performance for repeated queries against a single base table by storing and maintaining precomputed results. They are especially useful when queries repeatedly access a small, selective subset of a large table or repeatedly perform expensive aggregations. In this scenario, pre-filtering completed orders and pre-aggregating by keys commonly used in reporting can allow the Snowflake optimizer to use the materialized view transparently for compatible queries, reducing compute work at query time. Key Snowflake best practices and documented constraints include: materialized views are created on a single base table, not on another view or materialized view; they are physically stored and maintained by Snowflake; and their definitions should avoid non-deterministic or time-variant logic such as CURRENT_DATE. Standard views, by contrast, are logical only and do not provide persisted precomputation. Candidates should also remember that materialized views incur maintenance cost, so they are most valuable when query acceleration benefits outweigh refresh overhead.
- A. Correct.
Correct. A materialized view is physically stored and maintained by Snowflake, and it is most effective when it precomputes expensive filtering and aggregation on a large base table that is queried repeatedly. Including ORDER_STATUS = 'COMPLETE' in the materialized view definition can reduce the maintained data set, and grouping by CUSTOMER_ID and ORDER_DATE allows compatible dashboard queries to benefit through optimizer rewrite and result reuse of the precomputed data. This is a practical pattern for selective, repeated workloads on a large table.
- B. Incorrect.
Incorrect. A standard view stores only the query definition, not precomputed data. Snowflake does not persist and incrementally maintain standard views the way it does materialized views. Queries against a standard view still compute results from underlying tables at runtime unless other optimizations apply. This option reflects the common misconception that all views behave like materialized views.
- C. Incorrect.
Incorrect. Materialized view definitions have restrictions and are intended to be based on deterministic expressions over a single base table. Using changing context functions such as CURRENT_DATE in the materialized view definition is not an appropriate pattern for a materialized view definition because the result changes with time rather than only with base-table changes. A better design is to materialize stable filtered/aggregated data and let user queries apply the rolling 30-day predicate.
- D. Incorrect.
Incorrect. In Snowflake, a materialized view must be created on a base table, not on another materialized view. This option is plausible because layered logical views are common in analytics architectures, but it is not supported for materialized views.