ARA-C01 Question 395
Single answerQuery acceleration serviceA retail company runs an ad hoc analytics workload on a 2 TB SALES_FACT table using a Medium virtual warehouse. Most queries are selective and scan only a small subset of rows, for example filtering by REGION, PRODUCT_CATEGORY, and a narrow ORDER_DATE range. During peak business hours, analysts report inconsistent response times. The architect wants to improve performance for these selective scan-heavy queries without permanently increasing warehouse size. Which action is the most appropriate?
- A
Enable Query Acceleration Service for the warehouse to offload eligible portions of selective scan and aggregation work to shared compute resources
- B
Convert the virtual warehouse to a multi-cluster warehouse so each individual query gets additional compute for scan processing
- C
Create a materialized view on the entire SALES_FACT table without changing query patterns, because Query Acceleration Service only helps joins
- D
Increase the auto-suspend setting so the warehouse stays warm longer, which directly accelerates selective query scans
Show answer and explanation
Correct answer: A
Explanation
The best answer is to enable Query Acceleration Service. Snowflake documents QAS as a feature that can accelerate eligible queries by offloading portions of query processing, especially for selective queries that scan a small portion of a large table and often include filters and aggregations. This makes it a strong fit when the goal is to improve query latency without continuously paying for a larger warehouse. In contrast, multi-cluster warehouses are designed mainly for concurrency scaling rather than accelerating a single query. Auto-suspend tuning is operationally useful for cost and startup behavior but does not improve scan execution performance. Materialized views can help in specific repeated query patterns, but they are not a general substitute for QAS in ad hoc selective analytics workloads. Architects should also validate eligibility and benefit using query profiles and Snowflake guidance on QAS usage and supported query patterns.
- A. Correct.
Correct. Query Acceleration Service (QAS) is designed to improve performance for eligible queries by offloading parts of scan and aggregation processing to shared compute resources. It is particularly useful when queries are selective and access a small amount of data relative to the table size. In this scenario, the company wants better performance for selective analytical queries without permanently resizing the warehouse, which aligns well with QAS.
- B. Incorrect.
Incorrect. A multi-cluster warehouse primarily helps with concurrency by adding clusters to handle more simultaneous queries; it does not make a single query execute faster in the way described here. The scenario focuses on improving response time for eligible selective queries, not resolving queueing caused by concurrency saturation.
- C. Incorrect.
Incorrect. This option is based on a misconception. Query Acceleration Service is not limited to joins; it can benefit eligible scan and aggregation work. Also, creating a materialized view on an entire large fact table without a clear query pattern may introduce maintenance cost and may not address the broad, variable ad hoc filter patterns described.
- D. Incorrect.
Incorrect. Auto-suspend affects warehouse idle-time behavior and cost efficiency, not the execution speed of selective scan-heavy queries. Keeping a warehouse warm may reduce startup latency after resume, but it does not directly accelerate the internal processing of eligible queries.