ADA-C01 Question 269
Single answer3.5 Perform queries in Snowflake.A Snowflake administrator is investigating why a reporting query against a 4 TB fact table is slow during peak business hours. The BI tool issues the same parameterized SELECT statement hundreds of times per hour, but with different literal filter values. The administrator wants to reduce elapsed time and warehouse load without changing the BI tool or the table design. Which action is the most appropriate to improve performance for this workload?
- A
Create a search optimization service on the fact table columns that are frequently used in highly selective equality predicates.
- B
Suspend and resume the warehouse more frequently so the local disk cache is refreshed for each execution.
- C
Rewrite the query to use RESULT_SCAN so each new execution reuses the previous query result even when filter values differ.
- D
Enable Query Acceleration Service because it speeds up any repeated query regardless of selectivity or scan pattern.
Show answer and explanation
Correct answer: A
Explanation
The best answer is to create a Search Optimization Service on the columns used in highly selective filters. For repeated BI queries that target a very small subset of rows in a very large table, Search Optimization can significantly reduce the amount of data Snowflake must scan. This is especially useful when the same query shape is executed repeatedly with different filter values, because persisted query results are not reusable across different result sets.
Why the other options are less appropriate:
- Warehouse suspend/resume does not improve cache efficiency; it removes local cache benefits.
- RESULT_SCAN is for accessing a previous query's result set by query ID, not for making semantically different queries reuse old results.
- Query Acceleration Service can help eligible queries, but it is not the primary targeted solution for highly selective equality searches on large tables.
This aligns with Snowflake best practices for query performance tuning: use the result cache when possible, preserve warehouse cache when useful, and apply Search Optimization Service for selective lookup patterns on large tables. Relevant Snowflake documentation includes guidance on query performance optimization, persisted query results, Search Optimization Service, and Query Acceleration Service.
- A. Correct.
Correct. Search Optimization Service is designed to improve the performance of selective point-lookups and similar predicates on large tables, especially when queries repeatedly search for small subsets of data. In this scenario, the same query pattern is executed many times with different literal values, so persisted query results would not consistently apply. Because the administrator cannot change the BI tool or redesign the table, adding search optimization on the frequently filtered columns is an appropriate administrative action to reduce scan work and improve response time.
- B. Incorrect.
Incorrect. Suspending and resuming a warehouse clears its local disk cache rather than improving reuse. Frequent suspend/resume cycles would typically hurt, not help, workloads that benefit from warm cache. This option reflects a common misconception that restarting compute improves query performance. In Snowflake, cache reuse is generally beneficial when the same warehouse continues to process similar data access patterns.
- C. Incorrect.
Incorrect. RESULT_SCAN allows a session to query the results of a previous query by referencing its query ID, but it does not make new executions with different predicate values automatically reuse old results. Persisted query result reuse in Snowflake is only applicable when the query text and underlying conditions match the result cache requirements. Because the BI tool runs the statement with different literals, prior results are not interchangeable for new parameter values.
- D. Incorrect.
Incorrect. Query Acceleration Service can improve some scan-heavy queries by offloading portions of processing, but it is not a universal fix for all repeated queries, and it is not specifically intended to optimize highly selective lookups with changing literal values. This option is plausible because QAS is a performance feature, but it does not address the core pattern as directly as Search Optimization Service.