ADA-C01 Question 347
Single answerUse the query acceleration serviceA data engineering team runs an ELT workload on a frequently used warehouse. Most jobs perform well, but a few ad hoc analytic queries scan very large tables and include highly selective filters. These occasional queries are significantly slower than expected, while the team wants to avoid resizing the warehouse because doing so would increase cost for the entire workload. As the Snowflake administrator, which action should you take to improve the performance of those eligible queries with the least impact on the rest of the workload?
- A
Enable the query acceleration service for the warehouse so Snowflake can offload portions of eligible scan-heavy queries to shared compute resources
- B
Create a multi-cluster warehouse so every query on the warehouse receives more compute when needed
- C
Increase the warehouse size permanently because query acceleration service is intended only for concurrency issues
- D
Enable Search Optimization Service on every large table in the database because it replaces the need for query acceleration service
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 accelerate eligible queries by using shared compute resources for portions of query processing, which is useful when only certain large, selective queries need help and the administrator wants to avoid upsizing the warehouse for the full workload. By contrast, multi-cluster warehouses address concurrency rather than accelerating a single query pattern, and permanently increasing warehouse size affects all workloads and cost. Search Optimization Service is a separate feature for specific table access patterns and should not be treated as a blanket replacement for QAS. This aligns with Snowflake best practices: choose the most targeted performance feature for the observed bottleneck, and use warehouse resizing or multi-cluster scaling only when the workload pattern actually requires those approaches.
- A. Correct.
Correct. Query Acceleration Service (QAS) is designed to improve the performance of eligible queries by offloading parts of scan and filter processing to shared compute resources. It is especially useful for selective queries over large tables when you want targeted acceleration without resizing the warehouse for all workloads. This fits the scenario because only a subset of queries is slow and the team wants minimal impact on overall warehouse cost.
- B. Incorrect.
Incorrect. A multi-cluster warehouse primarily helps with concurrency by adding clusters to handle more simultaneous queries. It does not specifically target the performance of a single long-running, scan-heavy query in the way QAS does. A candidate might choose this because it sounds like additional compute helps everything, but it is the wrong tool for this pattern.
- C. Incorrect.
Incorrect. Increasing warehouse size can improve query performance broadly, but it raises cost for all queries using the warehouse, not just the occasional eligible ones. The statement is also wrong because QAS is not only for concurrency issues; it is intended to accelerate certain query processing patterns, particularly scan-heavy selective workloads.
- D. Incorrect.
Incorrect. Search Optimization Service can improve performance for specific point lookup and selective access patterns, but it is a table-level optimization with different use cases, cost considerations, and maintenance implications. It does not replace QAS. A candidate might pick this because the scenario mentions selective filters, but Search Optimization and QAS are distinct features and one is not a universal substitute for the other.