ADA-C01 Question 346
Single answerUse the query acceleration serviceA Snowflake administrator supports a reporting workload that runs on a MEDIUM virtual warehouse. Several dashboard queries scan a very large sales fact table but return only a small subset of rows based on highly selective filters. Business users report occasional long runtimes during peak periods, but the administrator does not want to resize the warehouse because most other queries already meet their SLA. Which action is the most appropriate to improve the performance of those eligible selective scan queries while minimizing changes to the rest of the workload?
- A
Enable the Query Acceleration Service on the warehouse and configure an appropriate scale factor.
- B
Convert the warehouse to a multi-cluster warehouse in Maximized mode so every query gets more compute.
- C
Create a search optimization service on the entire sales fact table because it replaces the need for Query Acceleration Service in all selective query cases.
- D
Increase the warehouse statement timeout so long-running selective queries can complete without interruption.
Show answer and explanation
Correct answer: A
Explanation
The best answer is to enable Query Acceleration Service (QAS) on the warehouse. QAS is intended to accelerate eligible queries that scan large amounts of data but return relatively few rows, often with selective filters and aggregations. This makes it a strong fit when only some queries in a workload need help and the administrator wants to avoid resizing the warehouse for all activity. By contrast, multi-cluster warehouses are mainly for handling concurrency and reducing queueing, not for selectively accelerating scan-heavy query fragments. Search Optimization Service is a separate feature that can be valuable for specific lookup and predicate access patterns, but it does not generally replace QAS and should not be treated as interchangeable. Snowflake documentation describes QAS as a warehouse-level feature that uses shared compute resources to accelerate eligible queries, with behavior influenced by the configured scale factor and query eligibility.
- A. Correct.
Correct. Query Acceleration Service (QAS) is designed to improve the performance of eligible queries by offloading portions of scan and aggregation processing to shared compute resources. It is particularly useful for queries with highly selective filters and large scans, where only a subset of queries need acceleration. Enabling QAS on the warehouse with a suitable scale factor allows the administrator to target these workloads without resizing the warehouse for all queries.
- B. Incorrect.
Incorrect. A multi-cluster warehouse primarily addresses concurrency by adding clusters when many queries run at the same time. It does not specifically target the execution characteristics of selective scan queries. In this scenario, the problem is not that every query needs more compute or that queuing is the main issue; rather, a subset of eligible queries needs faster execution.
- C. Incorrect.
Incorrect. Search Optimization Service can improve certain point lookup and selective predicate patterns, but it is not a universal replacement for Query Acceleration Service. These are different features with different use cases, costs, and eligibility patterns. Choosing search optimization for the entire fact table may be unnecessarily expensive and may not be the most appropriate first action when the goal is to accelerate eligible selective scan queries at the warehouse level.
- D. Incorrect.
Incorrect. Increasing statement timeout does not improve query performance; it only allows queries to run longer before being canceled. This addresses neither the root cause nor the requirement to improve response time for business users.