ARA-C01 Question 406
Single answerSearch optimization serviceA retail company stores 12 TB of clickstream data in a Snowflake table named EVENT_LOG. The table is clustered by EVENT_DATE because most reporting queries scan date ranges. A new fraud-detection workload issues thousands of highly selective queries per hour such as: SELECT * FROM EVENT_LOG WHERE SESSION_ID = ? AND EVENT_DATE >= CURRENT_DATE - 7. The query profile shows that the warehouse spends time scanning many micro-partitions even though only a few rows match each SESSION_ID. The architect needs to improve latency for these point-lookups without redesigning the table or creating additional copies of the data. What is the best solution?
- A
Enable Search Optimization Service on EVENT_LOG for the SESSION_ID column.
- B
Create a materialized view on EVENT_LOG filtered to the last 7 days and query that view instead.
- C
Increase the virtual warehouse size so more micro-partitions can be scanned in parallel for each lookup.
- D
Recluster EVENT_LOG on SESSION_ID instead of EVENT_DATE.
Show answer and explanation
Correct answer: A
Explanation
Search Optimization Service is a Snowflake performance feature intended for workloads where queries return a small number of rows from large tables using highly selective predicates, including equality searches on specific columns. In this scenario, EVENT_LOG is already organized to support date-range analytics, but the new fraud workload depends on fast lookup by SESSION_ID. Because the query profile shows excessive micro-partition scanning for very selective predicates, enabling search optimization on the lookup column is the most appropriate architectural choice. This preserves the current table design for reporting while accelerating the new access pattern. Best-practice guidance from Snowflake documentation is to use Search Optimization Service when standard micro-partition pruning is insufficient for selective lookups, especially on large tables with frequent point-search queries. Warehouse scaling improves compute throughput but does not improve selectivity. Materialized views are better suited to precomputed query results for repeated patterns, and reclustering would force a compromise between competing access paths.
- A. Correct.
Correct. Search Optimization Service is designed to accelerate highly selective point-lookup and selective predicate queries by maintaining additional metadata that helps Snowflake locate relevant micro-partitions more efficiently. In this scenario, queries filter on a specific SESSION_ID and return very few rows from a very large table, which is a strong fit for search optimization. It also meets the constraint of avoiding table redesign or duplicate data structures. Configuring it specifically for SESSION_ID targets the workload causing the problem.
- B. Incorrect.
Incorrect. A materialized view can help some repeated query patterns, but this option does not directly solve the point-lookup problem as well as Search Optimization Service. It also introduces an additional physical structure to maintain and only covers the last 7 days, which may not align with all query variations. Moreover, the main issue is efficient pruning for highly selective equality predicates on SESSION_ID, which is exactly what Search Optimization Service is intended to address.
- C. Incorrect.
Incorrect. Increasing warehouse size may reduce elapsed time somewhat through more parallelism, but it does not address the root cause: too many micro-partitions are being scanned for selective lookups. This approach can increase cost without proportionate benefit for point queries. Search Optimization Service is generally the more targeted optimization when query profiles show scanning overhead for highly selective predicates.
- D. Incorrect.
Incorrect. Reclustering on SESSION_ID could improve pruning for SESSION_ID lookups, but it would likely harm the existing date-range reporting workload that currently benefits from clustering on EVENT_DATE. It also represents a table design tradeoff rather than a targeted optimization. The scenario explicitly asks for a solution that improves selective lookups without redesigning the table, making Search Optimization Service the better answer.