ADA-C01 Question 338
Single answerRecommend the use of materialized viewsA retail company stores a 12 TB SALES_FACT table in Snowflake. The table receives frequent micro-batch inserts throughout the day, but updates and deletes are rare. Several BI dashboards run every few minutes and repeatedly execute queries that filter on ORDER_DATE and REGION, then aggregate NET_SALES by PRODUCT_ID. Query profiles show large scan volumes against SALES_FACT even after clustering improvements. The Administrator needs to recommend a solution that improves response time for these repetitive queries without requiring application SQL changes. Which action is the BEST recommendation?
- A
Create a materialized view on SALES_FACT that precomputes the commonly used filter/projection and aggregation pattern for ORDER_DATE, REGION, PRODUCT_ID, and NET_SALES.
- B
Create a standard view on SALES_FACT with the same SELECT statement, because Snowflake automatically persists and refreshes standard view results for repeated queries.
- C
Enable Search Optimization Service on all columns of SALES_FACT, because it is designed to accelerate large aggregations over frequently changing fact tables better than materialized views.
- D
Increase the warehouse size for the BI dashboards, because warehouse scaling is the only way to reduce scan cost for repeated aggregation queries on a large table.
Show answer and explanation
Correct answer: A
Explanation
Materialized views in Snowflake are best recommended when a workload repeatedly queries the same derived subset or aggregation of a large base table, especially when the base table changes primarily through inserts and the materialized view can be maintained efficiently. They are useful for improving performance and can be leveraged by the optimizer automatically, which is important when SQL changes are undesirable. By contrast, standard views do not store data, Search Optimization Service is aimed at accelerating highly selective lookup/search patterns rather than broad repeated aggregations, and simply enlarging the warehouse increases compute consumption without eliminating repeated scan/aggregation overhead. Snowflake documentation and best practices emphasize evaluating materialized views for repetitive, expensive query patterns on large tables while also considering maintenance cost from ongoing base-table changes.
- A. Correct.
Correct. A materialized view is appropriate when queries repeatedly access a subset or transformed representation of a base table and the result can be incrementally maintained. In this scenario, the workloads are highly repetitive, the base table is large, and the table is mostly append-only with rare updates/deletes. Those characteristics align well with materialized views, which can improve performance by precomputing and storing query results and allowing the optimizer to use the materialized view automatically, helping without changing application SQL.
- B. Incorrect.
Incorrect. A standard view stores only the query definition, not precomputed data. It does not persist or automatically refresh result data for reuse in the same way as a materialized view. Someone might choose this because both objects abstract SQL logic, but only materialized views physically store maintained results.
- C. Incorrect.
Incorrect. Search Optimization Service is most useful for selective point lookups and certain predicate search patterns, not as the primary recommendation for repeated large aggregations over a fact table. It does not replace precomputed aggregate storage. A candidate might pick this because the scenario mentions filtering columns, but the main bottleneck is repeated scan and aggregation work, which materialized views address more directly.
- D. Incorrect.
Incorrect. Increasing warehouse size may reduce elapsed time by adding compute, but it does not reduce the repeated scanning and aggregation work at the storage/query plan level, nor is it the best design recommendation when the same query pattern runs continuously. It can also increase cost without addressing the root cause. Warehouse scaling is a tuning option, not the best first recommendation for this pattern.