ARA-C01 Question 407
Single answerSearch optimization serviceA retail company stores 8 TB of order history in a Snowflake table named ORDERS. The table is clustered by ORDER_DATE because most analytics are time-based. A customer support application now runs thousands of highly selective point-lookups each hour using queries such as SELECT * FROM ORDERS WHERE ORDER_ID = ? and SELECT * FROM ORDERS WHERE CUSTOMER_EMAIL = ?. These lookups are slow because the predicates are not aligned with the clustering key. The architect needs to improve lookup performance without reclustering the table on non-analytic columns or creating multiple redundant copies of the data. Which solution is the MOST appropriate?
- A
Enable Search Optimization Service on the ORDERS table for the lookup columns used in selective equality predicates.
- B
Create a materialized view on ORDERS clustered by ORDER_ID and CUSTOMER_EMAIL so all point-lookups are served from the view.
- C
Increase the virtual warehouse size for the support application so more micro-partitions can be scanned in parallel.
- D
Replace the table clustering key from ORDER_DATE to CUSTOMER_EMAIL so support queries become faster, and rely on result cache for analytics.
Show answer and explanation
Correct answer: A
Explanation
Search Optimization Service is intended for tables where query patterns include highly selective predicates that are not well served by the current physical organization of data. In Snowflake, clustering improves pruning when queries align with the clustering key, but a table optimized for one access pattern, such as ORDER_DATE analytics, may still perform poorly for unrelated point-lookups like ORDER_ID or CUSTOMER_EMAIL. Search Optimization Service addresses this by maintaining additional metadata structures that help Snowflake identify relevant micro-partitions more efficiently for supported search patterns.
This makes it a strong fit when an architect must preserve the current clustering strategy for analytics while also accelerating selective operational lookups on the same table. Compared with changing the clustering key, it avoids degrading the primary analytic workload. Compared with warehouse scaling, it addresses the data access path rather than simply adding more compute. Compared with materialized views, it is generally a more direct and maintainable choice for selective lookup acceleration without duplicating data access structures unnecessarily.
This aligns with Snowflake best practices: use clustering to support large scan-heavy range patterns that benefit from micro-partition ordering, and use Search Optimization Service for highly selective lookups on columns commonly used in equality, substring, or similar supported search predicates, depending on the workload and edition/features enabled.
- A. Correct.
Correct. Search Optimization Service is designed to improve performance for highly selective queries, including equality lookups on specific columns, without requiring the base table to be reclustered around those access patterns. In this scenario, the table remains clustered for analytic workloads on ORDER_DATE while Search Optimization can accelerate point-lookups on ORDER_ID and CUSTOMER_EMAIL. This aligns with the requirement to avoid redundant copies of data and to support a secondary access pattern efficiently.
- B. Incorrect.
Incorrect. A materialized view can help some repeated query patterns, but this is not the most appropriate solution here. The support workload consists of many selective point-lookups on the base table, and creating a materialized view specifically to duplicate access paths for lookup columns adds maintenance overhead and additional storage. It also does not fit as cleanly as Search Optimization Service for ad hoc selective equality searches across a large table.
- C. Incorrect.
Incorrect. Scaling up the warehouse may reduce elapsed time somewhat by increasing compute available for scanning, but it does not solve the root issue: too many irrelevant micro-partitions must still be scanned because the data is not organized for these predicates. Search Optimization Service targets partition elimination for selective access patterns; warehouse scaling only brute-forces the scan at a higher cost.
- D. Incorrect.
Incorrect. Changing the clustering key to CUSTOMER_EMAIL would likely harm the existing time-based analytic workloads that currently benefit from clustering on ORDER_DATE. It also would not optimally support both ORDER_ID and CUSTOMER_EMAIL simultaneously, and relying on result cache is not a durable design strategy for a high-volume application workload with many distinct parameter values.