COF-C03 Question 74
Single answerBI and reportingA retail company uses Snowflake as the data platform for a BI dashboard that is queried every few minutes throughout the day by hundreds of business users. The dashboard queries are repetitive and mostly target the same aggregated sales tables. The company wants to improve dashboard response time while minimizing compute cost, without changing the BI tool. Which Snowflake feature should be implemented first?
- A
Enable and rely on the persisted query result cache for repeated dashboard queries
- B
Create a materialized view on every table used by the dashboard
- C
Increase the virtual warehouse size permanently so every dashboard query finishes faster
- D
Convert the aggregated sales tables to external tables so the BI tool can read them more efficiently
Show answer and explanation
Correct answer: A
Explanation
For BI and reporting workloads in Snowflake, repeated dashboard queries often benefit from caching before more invasive optimization is considered. Snowflake supports persisted query results, which can return cached results for identical queries when underlying data has not changed and other reuse conditions are met. This is especially relevant for dashboards that refresh frequently with the same SQL. Materialized views can also improve performance, but they are more targeted and should be evaluated based on query patterns and maintenance cost. Simply scaling up a warehouse increases spend, and external tables are designed for querying externally stored data rather than accelerating standard BI reporting on Snowflake-managed tables. This aligns with Snowflake best practices for optimizing repetitive analytic queries and controlling compute consumption.
- A. Correct.
Correct. Snowflake's persisted query results can return results for repeated identical queries without re-executing them, which is highly effective for BI/reporting workloads that issue the same SQL repeatedly. This can significantly improve response times and reduce compute usage because the warehouse does not need to reprocess the query when cached results are reused. This is often the first optimization to consider for repetitive dashboard queries.
- B. Incorrect.
Incorrect. Materialized views can help specific workloads, especially where expensive aggregations or filtering are repeatedly computed from base tables, but creating them on every table is not a best practice and may increase maintenance cost. They should be used selectively after confirming a query pattern benefits from them. For repetitive identical dashboard queries, persisted query results are usually the simpler and more cost-effective first step.
- C. Incorrect.
Incorrect. Increasing warehouse size may reduce query runtime, but making the warehouse permanently larger increases compute cost and does not address the fact that many BI queries are repetitive and may be served from cache. This option improves brute-force performance rather than optimizing the workload efficiently.
- D. Incorrect.
Incorrect. External tables are used to query data stored outside Snowflake, such as in cloud object storage. They are not a performance optimization for internal Snowflake BI reporting tables and typically would not improve dashboard responsiveness for this scenario.