COF-C03 Question 73
Single answerBI and reportingA retail company uses Snowflake as the backend for a dashboarding tool used by hundreds of business users throughout the day. The dashboard runs many short, repeated queries against the same sales summary tables. Users are complaining about slow response times during peak periods, even though the data only needs to be refreshed every hour. The Snowflake administrator wants to improve dashboard performance while minimizing compute cost and avoiding unnecessary changes to the BI tool. Which solution should be implemented?
- A
Create a materialized view on the frequently queried sales summary data and point the BI tool to that object
- B
Increase the size of the virtual warehouse permanently so all dashboard queries run faster at all times
- C
Use a larger result cache by increasing the cache retention period for the dashboard warehouse
- D
Schedule the dashboard queries to run through Snowpipe so results are precomputed before users log in
Show answer and explanation
Correct answer: A
Explanation
For BI and reporting workloads in Snowflake, the best optimization depends on the access pattern. In this scenario, many users are issuing short, repetitive queries against the same summary data, and the freshness requirement is only hourly. A materialized view is a strong fit because it can store and maintain precomputed results for expensive query patterns, improving dashboard responsiveness without requiring the BI tool to be redesigned. While scaling up the virtual warehouse can improve performance, it raises compute cost and is less targeted. Snowflake also provides caching features such as persisted query results, but these are system-managed and depend on identical query text and unchanged underlying data; they are not manually enlarged as described in the incorrect option. Snowpipe is specifically for loading data, not accelerating BI queries. Snowflake documentation and best practices for performance tuning emphasize choosing the right object design for repetitive reporting workloads and using warehouse scaling only when appropriate.
- A. Correct.
Correct. Materialized views can improve performance for repetitive queries on frequently accessed data by precomputing and maintaining query results in Snowflake. In a BI/reporting scenario where dashboards repeatedly query the same summarized data and the source data changes on a predictable schedule, a materialized view can reduce query latency while avoiding constant warehouse overprovisioning. This is a practical optimization when the dashboard workload is read-heavy and based on stable aggregation patterns.
- B. Incorrect.
Incorrect. Increasing warehouse size can improve performance, but making the warehouse permanently larger is usually a more expensive brute-force solution. It does not specifically address repeated access to the same summarized data, and it increases compute cost continuously, including during off-peak periods. This option may help symptomatically, but it is not the best fit for minimizing cost.
- C. Incorrect.
Incorrect. Snowflake's persisted query results are automatically managed and reused only when the exact same query is executed and the underlying data has not changed. Administrators do not configure a larger result cache or extend a cache retention period for a specific warehouse in the way described here. This option reflects a common misunderstanding about how Snowflake caching works.
- D. Incorrect.
Incorrect. Snowpipe is used for continuous data ingestion into Snowflake, not for scheduling or precomputing dashboard query results. It does not execute BI queries on a schedule for reporting acceleration. This distractor confuses data loading with query performance optimization.