COF-C03 Question 280
Single answerSearch optimization serviceA retail company stores 8 TB of order history in a Snowflake table named ORDERS. Business analysts frequently run highly selective queries such as SELECT * FROM ORDERS WHERE ORDER_ID = 'A12345' and SELECT * FROM ORDERS WHERE CUSTOMER_EMAIL = 'user@example.com'. The table is not clustered, and most queries scan a large amount of data even though they return only a few rows. The data engineering team wants to improve query performance for these selective lookups without rewriting the queries or reorganizing the table data. Which Snowflake feature should they implement?
- A
Enable Search Optimization Service on the ORDERS table for the relevant lookup columns
- B
Create a materialized view on the ORDERS table containing all columns
- C
Increase the size of the virtual warehouse used by analysts
- D
Define a clustering key on ORDER_ID and CUSTOMER_EMAIL and recluster the full table immediately
Show answer and explanation
Correct answer: A
Explanation
Search Optimization Service is the best fit for large tables that are queried with highly selective predicates, such as equality searches on identifiers, email addresses, or other lookup columns. It is intended to reduce the amount of data scanned for these queries and is commonly used when users need faster point-lookups without changing application SQL. By contrast, increasing warehouse size adds compute but does not improve data pruning logic; materialized views are useful for precomputed query patterns but are not the primary solution for arbitrary selective lookups on a large table; and clustering keys are better suited to improving pruning for specific data distribution and range-based access patterns, while also requiring ongoing maintenance. Snowflake documentation describes Search Optimization Service as a performance optimization for selective queries on large tables, including point lookups and other supported predicate patterns.
- A. Correct.
Correct. Search Optimization Service is designed to improve performance of highly selective point lookups and similar queries on large tables, especially when the predicate targets specific values and only a small number of rows should be returned. It can be added to a table and configured for relevant columns without requiring query rewrites or physically reorganizing the table in the way clustering does.
- B. Incorrect.
Incorrect. A materialized view can improve performance for some repeated query patterns, but creating one that contains all columns from a large base table is typically not the right solution for selective row lookups. It also introduces storage and maintenance overhead and does not directly address the stated requirement as effectively as Search Optimization Service.
- C. Incorrect.
Incorrect. A larger virtual warehouse may reduce elapsed time by adding compute, but it does not solve the root cause of scanning large amounts of data for highly selective predicates. The question asks for a feature that improves these selective lookups without query changes or table reorganization, which aligns with Search Optimization Service rather than simply adding more compute.
- D. Incorrect.
Incorrect. Clustering can help prune micro-partitions for some query patterns, especially range filters on well-correlated columns, but it requires maintaining physical organization and is not the best fit for this requirement. The scenario explicitly says the team does not want to reorganize table data. Search Optimization Service is the more targeted solution for selective equality lookups.