ADA-C01 Question 340
Single answerUse the search optimization serviceA retail company stores 8 TB of order history in a Snowflake table named ORDERS. Analysts run many interactive queries from dashboards that filter on highly selective equality predicates such as ORDER_ID, CUSTOMER_EMAIL, and SESSION_ID. The table is not well-clustered on these columns, and increasing warehouse size has not provided consistent latency improvements because most queries already scan only a small amount of data but still spend time locating matching micro-partitions. The administrator needs to improve response times for these selective lookups without redesigning the table or changing application SQL. Which action should the administrator take?
- A
Enable the search optimization service on the ORDERS table for the frequently searched columns
- B
Create a materialized view on the ORDERS table that selects all columns and rows
- C
Define a clustering key on ORDER_ID, CUSTOMER_EMAIL, and SESSION_ID and wait for automatic reclustering
- D
Increase the virtual warehouse from MEDIUM to 2XL for the dashboard queries
Show answer and explanation
Correct answer: A
Explanation
Search optimization service is the best fit when users run frequent, highly selective queries against large tables, especially with equality predicates on specific columns. Snowflake documents search optimization as a way to improve performance by enabling faster row access for supported query patterns, rather than relying solely on micro-partition pruning or more compute. This makes it a strong administrative choice when the goal is to speed up point-lookups without changing application SQL.
By contrast, clustering is more appropriate when ordering data can materially improve pruning, often for range-oriented access patterns, but it can be less efficient for several selective lookup columns and may require ongoing maintenance cost. Materialized views are useful for precomputed query results, but duplicating the full table is not a targeted optimization for sparse lookups. Warehouse scaling increases compute resources but does not directly address metadata-driven row location efficiency.
Best practice is to evaluate search optimization on the specific table and columns that support the selective predicates used most often, and to balance the expected performance gains against the additional maintenance cost of the service.
- A. Correct.
Correct. The search optimization service is designed to improve performance for highly selective point-lookups and similar predicates by maintaining persistent search access paths that help Snowflake locate relevant micro-partitions more efficiently. This is especially useful when query patterns involve equality searches on specific columns and when redesigning the table or SQL is not desirable.
- B. Incorrect.
Incorrect. A materialized view can help certain repeated query patterns, but creating one that effectively mirrors the entire base table is generally not the right solution for selective lookup acceleration. It adds storage and maintenance cost without specifically addressing the problem of quickly locating sparse matches across a large table.
- C. Incorrect.
Incorrect. Clustering can improve pruning for range and some filter patterns, but it requires physically reorganizing data over time and may not be the best fit for multiple high-cardinality equality lookup columns such as ORDER_ID, CUSTOMER_EMAIL, and SESSION_ID. It also does not meet the requirement as directly as search optimization for this use case.
- D. Incorrect.
Incorrect. Larger warehouses can reduce execution time for scan-heavy workloads, but they do not reliably solve lookup latency when the bottleneck is locating a small set of matching rows in a large table. In this scenario, the company has already observed inconsistent improvement from warehouse scaling.