COF-C03 Question 279
Single answerQuery acceleration serviceA data engineering team runs a dashboard workload on a Snowflake virtual warehouse. Most queries finish quickly, but some queries that scan a large table and return only a small subset of rows show unpredictable latency spikes. The team wants to improve performance for these selective queries without simply resizing the warehouse for all workloads. Which action is the MOST appropriate?
- A
Enable Query Acceleration Service for the warehouse so Snowflake can offload eligible parts of selective scan queries to shared compute resources
- B
Convert the warehouse to a multi-cluster warehouse so every single query automatically uses more compute during execution
- C
Create a materialized view for every table referenced by the dashboard, because Query Acceleration Service only works with materialized views
- D
Enable Search Optimization Service on the warehouse, because it replaces Query Acceleration Service for all selective scan workloads
Show answer and explanation
Correct answer: A
Explanation
The best answer is to enable Query Acceleration Service on the warehouse. In Snowflake, QAS is intended to improve the performance of eligible queries by using shared compute resources, especially for queries with large scans and selective filters that return a small subset of rows. This makes it a practical choice when a team wants faster response times for certain queries without overprovisioning the warehouse for the entire workload. By contrast, multi-cluster warehouses are mainly for handling concurrent workloads, not accelerating a single eligible query. Materialized views are a separate optimization technique and are not a prerequisite for QAS. Search Optimization Service is also a separate feature intended for specific search-access patterns and should not be treated as a universal substitute for QAS. This aligns with Snowflake documentation on warehouse optimization features and service-specific best practices.
- A. Correct.
Correct. Query Acceleration Service (QAS) is designed to improve the performance of eligible queries, especially those with large scan operations and selective filters where only a small amount of data is returned. It can use shared compute resources to accelerate parts of query processing without requiring the customer to permanently resize the warehouse. This matches the scenario of intermittent latency on selective queries.
- B. Incorrect.
Incorrect. Multi-cluster warehouses primarily help with concurrency by adding clusters when many queries compete for resources. They do not specifically target the performance of a single selective scan query in the way Query Acceleration Service does. Someone might choose this option because adding compute sounds helpful, but the problem described is query acceleration for certain query patterns, not concurrency saturation.
- C. Incorrect.
Incorrect. Materialized views can improve performance in some cases, but Query Acceleration Service does not require materialized views. Creating materialized views for every table would also introduce unnecessary storage and maintenance costs and is not the direct solution described for selective scan acceleration. This distractor reflects the misconception that all performance tuning in Snowflake requires precomputed objects.
- D. Incorrect.
Incorrect. Search Optimization Service can help certain point lookup and highly selective search patterns, but it does not replace Query Acceleration Service for all selective scan workloads. These are separate features with different use cases. A candidate might choose this because both features can improve query performance, but the scenario specifically aligns with QAS behavior for eligible scan-heavy selective queries.