ADA-C01 Question 341
Single answerUse the search optimization serviceA retail company stores 8 TB of order history in a Snowflake table named ORDERS. Analysts frequently run highly selective queries from dashboards such as: SELECT * FROM ORDERS WHERE CUSTOMER_ID = 'C123456'; and SELECT * FROM ORDERS WHERE ORDER_ID IN ('O1001','O1002');
The table is loaded continuously throughout the day, and clustering has not significantly improved these point-lookups because the searched values are scattered across many micro-partitions. The administrator needs to improve query performance for these selective equality predicates without changing application SQL. Which action should the administrator take?
- A
Enable the search optimization service on the ORDERS table, targeting the columns commonly used in selective equality lookups such as CUSTOMER_ID and ORDER_ID.
- B
Create a materialized view on ORDERS that selects all columns, because materialized views automatically accelerate all point-lookups without additional maintenance cost.
- C
Increase the virtual warehouse size for the dashboard workload, because search optimization only helps full table scans and not selective filters.
- D
Recluster the ORDERS table on LOAD_TIMESTAMP, because any clustering key will eliminate the need for search optimization for point-lookups.
Show answer and explanation
Correct answer: A
Explanation
Search optimization service is the best fit when users run highly selective queries against large tables and the sought values are distributed across many micro-partitions, limiting normal pruning benefits. It is commonly used for point-lookups with predicates such as '=' and 'IN', and can be enabled at the table level with optional column-specific configuration to control cost and scope. In contrast, simply increasing warehouse size adds compute cost without improving metadata-based access efficiency, and materialized views are not intended as a blanket solution for all selective lookup patterns on large mutable tables. Snowflake documentation and best practices position search optimization as a targeted performance feature for selective query access patterns where clustering is insufficient or impractical.
- A. Correct.
Correct. The search optimization service is designed to improve performance of highly selective queries, including equality predicates and IN-list lookups, especially when matching rows are sparse across many micro-partitions. It can be added without changing application SQL and is well suited for large tables where point-lookups are otherwise expensive.
- B. Incorrect.
Incorrect. A materialized view is not a universal substitute for search optimization. Creating a materialized view that selects all columns from a large, continuously changing table would add maintenance overhead and may not be an efficient or practical way to accelerate arbitrary point-lookups. Materialized views are best when they precompute a useful subset, projection, or aggregation for repeated query patterns.
- C. Incorrect.
Incorrect. A larger warehouse may reduce runtime by adding compute, but it does not address the underlying pruning problem for highly selective predicates scattered across micro-partitions. Also, search optimization is specifically intended to improve selective access patterns, not only full scans.
- D. Incorrect.
Incorrect. Clustering can help when data can be physically organized around frequently filtered columns, but clustering on LOAD_TIMESTAMP does not help queries filtering by CUSTOMER_ID or ORDER_ID. Even clustering on the lookup columns may be ineffective when values are randomly distributed and continuously updated. This is the scenario where search optimization is often more appropriate.