DAA-C01 Question 183
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 with high-cardinality columns such as ORDER_ID, CUSTOMER_ID, and SKU_ID. Analysts run two common workloads: (1) dashboard queries that filter on a single CUSTOMER_ID or small lists of ORDER_ID values and must return in seconds, and (2) ad hoc aggregation queries that scan large portions of the table and sometimes spill into long runtimes. The team wants to improve performance without redesigning the table. Which TWO actions are the most appropriate?
- A
Enable Search Optimization Service on the SALES_FACT table for the selective lookup columns used in point-lookups, such as CUSTOMER_ID and ORDER_ID.
- B
Enable Query Acceleration Service for the virtual warehouse used by the analysts to improve eligible scan-heavy and selective query processing.
- C
Create a clustering key on every column in SALES_FACT so both point lookups and broad aggregations automatically become optimal.
- D
Convert the virtual warehouse to a multi-cluster warehouse in Auto-scale mode to accelerate each individual long-running query even when only one query runs at a time.
- E
Materialize all analyst queries as permanent tables, because Search Optimization Service cannot help equality predicates on large tables.
Show answer and explanation
Correct answers: A, B
Explanation
The best answer is to combine Search Optimization Service for highly selective point-lookups with Query Acceleration Service for eligible queries that process large data volumes. Search Optimization Service is well suited for queries that filter on high-cardinality columns with equality, substring, or small lookup-style predicates, enabling much faster access paths on large tables. Query Acceleration Service is a warehouse-level capability that can improve performance for certain eligible queries by offloading parts of scan and filtering work, helping reduce runtime for selective or scan-heavy workloads without changing table design. By contrast, multi-cluster warehouses are mainly for concurrency scaling, not speeding up a single query, and clustering every column is not a realistic or effective optimization strategy. These recommendations are consistent with Snowflake guidance on using Search Optimization Service for highly selective access patterns and using Query Acceleration Service as a warehouse feature for eligible query acceleration.
- A. Correct.
Correct. Search Optimization Service is designed to improve performance for highly selective queries, including point lookups and small IN-list searches on large tables. In this scenario, filters on high-cardinality columns such as CUSTOMER_ID and ORDER_ID are strong candidates because analysts need very fast response times for dashboard-style lookups. This aligns with best practice: apply search optimization selectively to columns involved in highly selective predicates rather than broadly to every column.
- B. Correct.
Correct. Query Acceleration Service (QAS) can help accelerate eligible queries by offloading portions of scan and filter processing, especially for queries with selective filters and large scan volumes. It is a virtual warehouse feature intended to improve performance without requiring schema redesign. In this scenario, it is an appropriate complement to Search Optimization Service because the team also has ad hoc queries that can run for a long time over large data volumes.
- C. Incorrect.
Incorrect. Clustering keys can improve pruning for certain access patterns, but creating a clustering key on every column is not a valid best practice and would be costly and ineffective. Clustering is not a substitute for Search Optimization Service on highly selective point-lookups, and excessive clustering dimensions can reduce clustering effectiveness while increasing maintenance cost.
- D. Incorrect.
Incorrect. Multi-cluster warehouses primarily address concurrency by adding clusters when many queries run at the same time. They do not primarily speed up a single long-running query when only one cluster is active and one query is executing. This option reflects a common misconception that multi-cluster automatically improves per-query latency regardless of concurrency.
- E. Incorrect.
Incorrect. Search Optimization Service can help equality predicates and highly selective lookups on large tables, which is exactly one of the main use cases in this scenario. Materializing all analyst queries as permanent tables is operationally heavy, may create data freshness issues, and is not the recommended first response here.