ADA-C01 Question 531
Select 3Materialized viewsA retail company stores clickstream events in a large Snowflake table named EVENTS, which receives continuous INSERTs throughout the day. Analysts repeatedly run a dashboard query that filters on EVENT_DATE and REGION and calculates a small set of pre-aggregated metrics. To reduce dashboard latency, an administrator proposes creating a materialized view on EVENTS. Before implementation, the administrator wants to confirm which statements about Snowflake materialized views are correct for this use case. Which TWO statements are correct?
- A
Snowflake automatically maintains the materialized view as the base table changes, and the maintenance consumes compute resources.
- B
A materialized view can be created over a query that joins EVENTS to a DIM_REGION table, which is recommended for dashboard acceleration.
- C
Query rewrite can allow a user's query on the base table to benefit from the materialized view even if the user does not explicitly reference the materialized view.
- D
Because the base table receives frequent INSERTs, the materialized view will not be usable until a manual REFRESH command is issued after each load.
- E
Materialized views are generally most beneficial when the view is selective or pre-computes expensive expressions/aggregations that are queried repeatedly.
Show answer and explanation
Correct answers: A, C, E
Explanation
The correct answers are 1, 3, and 5. In Snowflake, materialized views are automatically maintained as data changes in the base table, which improves query performance but adds maintenance cost. They are especially effective for repetitive workloads that benefit from precomputed selective subsets, expressions, or aggregations. Snowflake can also transparently rewrite eligible queries on the base table to use the materialized view, allowing existing dashboards to benefit without query changes. However, Snowflake materialized views are more limited than in some other database systems: they are based on a single table and are not intended for general join-based definitions. Also, unlike platforms that require explicit refresh operations, Snowflake materialized views do not need manual refresh after each data load. These points align with Snowflake documentation and best practices for evaluating materialized view benefit versus maintenance overhead on frequently changing tables.
- A. Correct.
Correct. Snowflake materialized views are maintained automatically when data in the base table changes. This maintenance is not free; it consumes compute resources managed by Snowflake and contributes to cost. In a high-ingest table such as EVENTS, administrators should weigh faster query performance against ongoing maintenance overhead.
- B. Incorrect.
Incorrect. Snowflake materialized views are defined on a single base table and do not support arbitrary join-based definitions such as joining EVENTS to DIM_REGION. This is a common misconception because many database platforms allow broader MV definitions, but Snowflake's implementation is intentionally more restrictive.
- C. Correct.
Correct. Snowflake's optimizer can use query rewrite to satisfy eligible queries against the base table using a materialized view, even when the query does not explicitly name the materialized view. This is one of the key operational benefits because BI tools and analysts may continue querying the base table while still benefiting from the precomputed results when the optimizer determines it is valid and efficient.
- D. Incorrect.
Incorrect. Snowflake does not require a manual REFRESH command after each load for materialized views. Automatic maintenance is part of the feature. Someone might choose this option if they are familiar with systems where MVs must be manually refreshed, but that is not how Snowflake materialized views operate.
- E. Correct.
Correct. Materialized views are most useful when they reduce repeated work, such as selective filtering, expensive expression evaluation, or repeated aggregations over large base tables. They are less compelling when maintenance cost from frequent DML outweighs the performance gain, so this is exactly the kind of tradeoff an administrator should evaluate for dashboard workloads.