ADA-C01 Question 530
Single answerMaterialized viewsA retail company has a 15 TB SALES_FACT table that is continuously loaded throughout the day. Analysts repeatedly run dashboards that filter on REGION, ORDER_DATE, and PRODUCT_CATEGORY and aggregate NET_SALES. Query latency is becoming inconsistent during peak load. The Snowflake administrator is considering a materialized view to improve performance while controlling unnecessary maintenance cost. Which action is the best choice?
- A
Create a materialized view on SALES_FACT that pre-filters recent data and pre-computes the commonly used aggregations for REGION, ORDER_DATE, and PRODUCT_CATEGORY, then monitor whether dashboard queries are automatically rewritten to use it.
- B
Create a materialized view that includes all columns from SALES_FACT so any dashboard query can be satisfied from the materialized view without scanning the base table.
- C
Replace the dashboards with a standard view because standard views store query results and therefore reduce both compute usage and maintenance overhead compared with materialized views.
- D
Create a secure materialized view because secure materialized views do not incur background maintenance costs and are optimized specifically for high-frequency DML on the base table.
Show answer and explanation
Correct answer: A
Explanation
For Snowflake materialized views, the best practice is to use them selectively for repeated, expensive query patterns on large tables, especially when queries commonly filter and aggregate on the same columns. They are maintained automatically as the base table changes, so administrators must balance query acceleration against maintenance cost. A narrowly focused materialized view that pre-filters or pre-aggregates the data needed by dashboards is usually more effective than materializing the entire base table. Snowflake's optimizer can automatically rewrite compatible queries to use the materialized view, which is why monitoring query behavior after deployment is important. By contrast, standard views are only stored SQL definitions and do not precompute results. Relevant Snowflake documentation and guidance emphasize materialized views for selective query acceleration, automatic maintenance tradeoffs, and optimizer query rewrite behavior.
- A. Correct.
Correct. Materialized views in Snowflake are most effective when they cover a targeted subset of a large base table and precompute expensive expressions or aggregations used repeatedly by many queries. In this scenario, the repeated dashboard predicates and aggregations make a selective materialized view a good fit. Snowflake can automatically rewrite eligible queries to use the materialized view, improving latency without requiring users to query it directly. Limiting the scope of the materialized view also helps control maintenance cost, since materialized views are maintained as the base table changes.
- B. Incorrect.
Incorrect. Creating a materialized view over all columns of a very large fact table is generally not a good design choice. It increases storage and maintenance overhead and reduces the benefit of using a materialized view selectively. Materialized views are intended for targeted acceleration, not as a full duplicate of the base table for every possible query pattern.
- C. Incorrect.
Incorrect. A standard view does not store data and does not persist query results for reuse in the same way a materialized view maintains precomputed results. Standard views are logical definitions only, so they do not by themselves reduce scan cost or improve performance for repeated workloads. This option reflects the misconception that all views cache data.
- D. Incorrect.
Incorrect. While secure views and secure materialized views can be used for data protection requirements, security does not eliminate the cost of maintaining a materialized view. Materialized views still require background maintenance as underlying base-table data changes. Also, they are not specifically designed to be cheaper or better for high-frequency DML; in fact, frequent changes can increase maintenance cost.