ARA-C01 Question 215
Single answerTablesA retail company stores 8 TB of order data in a Snowflake table named ORDERS. Most dashboards filter on ORDER_DATE and REGION, while fraud analysts frequently look up individual ORDER_ID values. The table receives a continuous stream of inserts throughout the day and is queried by many concurrent BI users. Query performance has degraded as data volume has grown, and the architects want to improve performance without adding unnecessary maintenance overhead. Which action is the BEST choice?
- A
Define a clustering key on (ORDER_DATE, REGION) for the ORDERS table and allow Snowflake to maintain clustering as data changes
- B
Add a search optimization service on ORDER_ID to accelerate highly selective point-lookups by fraud analysts
- C
Create a materialized view that selects all columns from ORDERS without filtering or aggregation, so all queries can use the materialized data
- D
Convert ORDERS to a temporary table so that metadata operations are faster and concurrent dashboard queries scan less data
Show answer and explanation
Correct answer: B
Explanation
The best answer is to add Search Optimization Service on ORDER_ID. Snowflake search optimization is intended for highly selective predicate access patterns, such as point lookups on large tables, and is a strong fit when users need to retrieve a very small subset of rows quickly. In contrast, clustering is typically considered when large tables are frequently filtered by one or more columns and improved micro-partition pruning justifies the additional maintenance cost. Because this table is continuously inserted into and the requirement emphasizes improving performance without unnecessary maintenance overhead, clustering on ORDER_DATE and REGION is not the strongest single recommendation. Materialized views should be used selectively for repeated expensive computations, not as full-table copies. Temporary tables are not appropriate for persistent enterprise reporting datasets. These recommendations align with Snowflake best practices for choosing between clustering, materialized views, and search optimization based on actual query access patterns.
- A. Incorrect.
This is plausible because clustering can help prune micro-partitions when queries commonly filter on the clustering columns. However, for a table with continuous inserts, clustering adds maintenance cost and is not necessarily the best first choice here. The scenario highlights two workload patterns: broad dashboard filters on ORDER_DATE and REGION, and highly selective ORDER_ID lookups. Since the question asks for the BEST single action with minimal maintenance overhead, clustering on two columns may improve some dashboard queries but does not directly target the selective ORDER_ID lookups and can introduce ongoing reclustering overhead as new data lands.
- B. Correct.
Correct. Search Optimization Service is designed to improve performance for highly selective queries, including point lookups on equality predicates such as ORDER_ID = ?. In this scenario, fraud analysts perform frequent individual ORDER_ID lookups against a large, continuously changing table. Search optimization can accelerate those queries without requiring full table redesign. Compared with clustering, it is a more targeted feature for selective access patterns and avoids relying on clustering depth for a high-cardinality lookup column.
- C. Incorrect.
This is incorrect. A materialized view is most beneficial when it precomputes expensive transformations such as aggregations, projections, filters, or joins that are repeatedly queried. Creating a materialized view that simply mirrors all columns from the base table provides little practical benefit and adds maintenance cost, especially on a frequently changing 8 TB table. It would not be an efficient or recommended design for this workload.
- D. Incorrect.
This is incorrect. Temporary tables are session-scoped and intended for transient intermediate data, not for persistent production fact tables serving BI workloads. Converting a large shared table to a temporary table would break the required persistence and multi-session access patterns. Temporary tables do not reduce the amount of data scanned for dashboard queries in the way suggested.