COF-C03 Question 281
Single answerSearch optimization serviceA retail company stores 8 TB of order history in a Snowflake table named SALES_FACT. Analysts frequently run highly selective queries such as: SELECT * FROM SALES_FACT WHERE CUSTOMER_ID = 'C123456'; and SELECT * FROM SALES_FACT WHERE ORDER_ID IN ('O1001','O1002'); The table is not clustered, and the queries usually return only a few rows but still scan a large amount of data. The team wants to improve performance for these point-lookups without redesigning the table. Which action should they take?
- A
Enable search optimization on SALES_FACT for the equality predicates used in the queries
- B
Create a materialized view on SALES_FACT without a filtering clause so all lookups use the view automatically
- C
Increase the warehouse size because Search Optimization Service only helps full table scans
- D
Define a clustering key on every column in SALES_FACT so point-lookups avoid scanning micro-partitions
Show answer and explanation
Correct answer: A
Explanation
Search Optimization Service is intended for selective query patterns where a table is large but queries return very few rows, such as equality searches, substring/regex patterns in supported cases, and certain join lookups. In Snowflake documentation, search optimization is described as a mechanism that can significantly reduce scanned data for point-lookups on large tables. This makes it a strong fit for queries like WHERE CUSTOMER_ID = ... or WHERE ORDER_ID IN (...), especially when the team wants better performance without redesigning storage or relying on broad clustering strategies. Warehouse scaling adds compute but does not improve data pruning logic, and materialized views are better suited to repeated precomputed query results rather than generalized selective lookup acceleration.
- A. Correct.
Correct. Search Optimization Service is designed to improve performance for highly selective queries, especially point-lookups and equality predicates on large tables where only a small number of rows match. In this scenario, predicates on CUSTOMER_ID and ORDER_ID are classic candidates. Enabling search optimization can reduce the amount of data that must be scanned without requiring a table redesign.
- B. Incorrect.
Incorrect. A materialized view can improve performance for some repeated query patterns, but creating a broad materialized view over the same table without targeted filtering or aggregation does not directly solve the selective point-lookup problem described here. It also introduces maintenance cost and is not the primary feature intended for accelerating highly selective equality searches.
- C. Incorrect.
Incorrect. Increasing warehouse size may reduce runtime by adding compute, but it does not address the root cause: too much data being scanned for highly selective lookups. Also, the statement that Search Optimization Service only helps full table scans is false; it is specifically intended to optimize selective access patterns.
- D. Incorrect.
Incorrect. Clustering can help prune micro-partitions for range and ordered access patterns, but defining a clustering key on every column is not practical or recommended. In addition, clustering is not the best fit for ad hoc point-lookups across columns like CUSTOMER_ID and ORDER_ID. Search Optimization Service is the more appropriate solution for this use case.