DAA-C01 Question 184
Select 2Use search optimization service and virtual warehouse features such as the query acceleration servicesA retail analytics team has a 12 TB SALES_FACT table that is clustered by ORDER_DATE. Analysts run thousands of dashboard queries per day. Two query patterns are causing performance complaints: (1) highly selective lookups for a single ORDER_ID or CUSTOMER_EMAIL, and (2) ad hoc aggregation queries that scan large date ranges but return only a small subset of rows after applying complex filters. The team wants to improve performance without redesigning the entire schema. Which combination of actions would best address these two patterns in Snowflake?
- A
Enable Search Optimization Service on SALES_FACT for the columns used in highly selective equality lookups, such as ORDER_ID and CUSTOMER_EMAIL.
- B
Enable Query Acceleration Service for the virtual warehouse used by the analysts to improve eligible scan-heavy queries with selective filters.
- C
Create a materialized view on the entire SALES_FACT table with all columns so both lookup and aggregation queries are automatically accelerated.
- D
Increase the virtual warehouse's AUTO_SUSPEND value so the warehouse stays running longer and query execution latency is reduced for all query types.
- E
Recluster SALES_FACT on CUSTOMER_EMAIL only, because clustering on lookup columns replaces the need for Search Optimization Service and Query Acceleration Service.
Show answer and explanation
Correct answers: A, B
Explanation
The best answer is to use Snowflake features that match the two distinct access patterns. Search Optimization Service is appropriate for highly selective lookup queries on large tables, such as equality searches on ORDER_ID or CUSTOMER_EMAIL. Query Acceleration Service is a warehouse-level capability that can improve performance for eligible queries that scan large volumes of data but return relatively few rows after filtering or aggregation. Together, these features address both the point-lookup and scan-heavy analytic patterns without requiring a full schema redesign. By contrast, materialized views should be targeted to specific reusable computations, not used as a blanket copy of an entire large fact table. AUTO_SUSPEND tuning can help manage resume behavior and cost, but it does not materially improve the execution plan for slow queries. Finally, relying only on reclustering is a common misconception: clustering can help partition pruning for some workloads, but it is not a substitute for Search Optimization Service and does not provide the benefits of Query Acceleration Service. These recommendations align with Snowflake documentation on Search Optimization Service and Query Acceleration Service best practices.
- A. Correct.
Correct. Search Optimization Service is designed to improve performance for highly selective point-lookups and similar access patterns, especially when queries filter on specific values in large tables. In this scenario, equality predicates on ORDER_ID and CUSTOMER_EMAIL are good candidates. Because the table is large and primarily clustered by ORDER_DATE, selective lookups on different dimensions may otherwise require scanning many micro-partitions.
- B. Correct.
Correct. Query Acceleration Service can help eligible queries that need to process large amounts of data but ultimately return a small subset after selective filtering or aggregation. It is a virtual warehouse feature intended to accelerate parts of scan-heavy queries by offloading work to shared compute resources. This aligns well with the ad hoc aggregation pattern described.
- C. Incorrect.
Incorrect. Materialized views can help some repeated query patterns, but creating one on the entire fact table with all columns is not a practical or appropriate solution here. It would be expensive, hard to maintain, and would not directly address the specific point-lookup pattern as effectively as Search Optimization Service. Materialized views are most useful when they precompute a targeted subset, projection, or aggregation that is repeatedly queried.
- D. Incorrect.
Incorrect. Increasing AUTO_SUSPEND may reduce cold-start delays if a warehouse frequently resumes, but it does not accelerate the execution of already-running queries. The scenario focuses on query runtime for specific workloads, not just resume latency. This option addresses operational behavior, not the root performance issues.
- E. Incorrect.
Incorrect. Reclustering only on CUSTOMER_EMAIL is unlikely to be the best overall solution for a 12 TB fact table with major date-range analytics. Clustering strategy should reflect broad pruning value across common query patterns, and changing it to a lookup-oriented column could harm other workloads. Also, clustering does not replace Search Optimization Service for highly selective lookups, and it does not provide the same function as Query Acceleration Service for eligible scan-heavy queries.