ADA-C01 Question 339
Single answerRecommend the use of materialized viewsA retail company stores 8 TB of sales data in a single FACT_SALES table. The table receives continuous micro-batch INSERTs every few minutes, but historical rows are rarely updated or deleted. Analysts run hundreds of dashboard queries each hour that repeatedly filter on REGION, SALE_DATE, and PRODUCT_CATEGORY, and aggregate revenue by day. Query profiles show that most runtime is spent scanning the same subset of rows from FACT_SALES. The company wants to reduce dashboard latency without requiring analysts to change their SQL. Which solution should the Snowflake administrator recommend?
- A
Create a materialized view on FACT_SALES that precomputes the commonly used filtered/aggregated result set for REGION, SALE_DATE, and PRODUCT_CATEGORY.
- B
Create a standard view on FACT_SALES with the same query logic, because Snowflake automatically persists and maintains the result set for standard views.
- C
Enable Search Optimization Service on FACT_SALES, because it is the best fit for repeated aggregate queries over the same filtered subset and does not require any maintenance cost.
- D
Create a dynamic table to replace FACT_SALES for dashboard queries, because dynamic tables transparently accelerate all queries against the base table without requiring downstream query changes.
Show answer and explanation
Correct answer: A
Explanation
Materialized views are best recommended when a workload repeatedly queries a large table using the same filter and aggregation patterns, and especially when the base table changes mostly through INSERTs rather than frequent UPDATE/DELETE activity. In this scenario, the dashboard workload repeatedly scans the same subset of FACT_SALES and computes similar aggregates, making a materialized view a strong fit. Snowflake maintains materialized views incrementally and can automatically rewrite compatible queries to use them, which is valuable when users should not need to change SQL. By contrast, standard views are only logical abstractions; they do not persist results. Search Optimization Service is useful for selective row access patterns, but it is not primarily intended to precompute repeated aggregations. Dynamic tables are for pipeline-style materialization with managed refresh semantics, not transparent query acceleration of an existing base table. These recommendations align with Snowflake documentation and best practices for choosing between materialized views, standard views, search optimization, and dynamic tables based on workload shape and maintenance behavior.
- A. Correct.
Correct. A materialized view is appropriate when many queries repeatedly access the same derived subset or aggregation from a large base table, especially when the base table is frequently appended to but not heavily updated. Snowflake stores and incrementally maintains the materialized view, which can reduce scan and compute cost for recurring dashboard workloads. In addition, Snowflake's optimizer can automatically rewrite eligible queries to use the materialized view, helping performance without forcing analysts to change their SQL.
- B. Incorrect.
Incorrect. A standard view stores only the query definition, not precomputed data. Each query against the view still executes against the underlying base table at runtime unless separate result caching happens to apply. This option reflects the common misconception that logical views behave like persisted summaries.
- C. Incorrect.
Incorrect. Search Optimization Service is designed primarily to improve highly selective point-lookups and certain predicate patterns, such as equality, substring, or semi-structured access, rather than repeated aggregate workloads over a common derived subset. It can help some selective filters, but it is not the primary recommendation here for precomputing recurring aggregations. Also, it does have an additional cost, so the claim about no maintenance cost is wrong.
- D. Incorrect.
Incorrect. Dynamic tables are used to materialize the results of a query pipeline on a defined refresh schedule/target lag, typically for data transformation workflows. They are not a transparent acceleration feature for existing queries against the base table, and using one would generally require downstream objects or queries to target the dynamic table explicitly. That does not meet the requirement of improving performance without analyst query changes.