ARA-C01 Question 399
Single answerClusteringA retail company stores 12 TB of order history in a Snowflake table named FACT_ORDERS. The table receives continuous inserts throughout the day and is queried heavily by dashboards that typically filter on ORDER_DATE ranges and REGION, with occasional joins to small dimension tables. Query profiles show a large percentage of partitions scanned for date-range filters, and average dashboard latency has increased. The architect wants to improve pruning for the common filters without introducing unnecessary maintenance cost. Which action is the BEST recommendation?
- A
Define a clustering key on (ORDER_DATE, REGION) for FACT_ORDERS and monitor clustering depth to verify that pruning improves
- B
Create a search optimization service on all columns in FACT_ORDERS because it is the primary feature for range filters on large fact tables
- C
Convert FACT_ORDERS to a temporary table so that Snowflake rewrites micro-partitions more frequently and improves pruning
- D
Increase the warehouse size used by dashboards because larger warehouses automatically reorganize table data for better micro-partition pruning
Show answer and explanation
Correct answer: A
Explanation
For large Snowflake tables with frequent filtering on a stable set of columns, clustering keys can improve micro-partition pruning, especially for range predicates such as dates. In this scenario, ORDER_DATE is the dominant filter and REGION is also commonly used, making a clustering key on those columns a strong fit. Because the table is continuously loaded, the architect should also evaluate the maintenance tradeoff, typically by reviewing clustering information and query performance over time. Snowflake best practices emphasize using clustering selectively for very large tables where query pruning benefits outweigh the cost of maintaining clustered data. Search Optimization Service is better suited to selective lookup-style access patterns rather than being the default answer for large date-range scans. Warehouse sizing affects compute available for query processing but does not change micro-partition organization. Relevant Snowflake documentation includes guidance on clustered tables, clustering keys, and micro-partition pruning, as well as separate documentation on Search Optimization Service use cases.
- A. Correct.
Correct. Clustering is appropriate when a large table is frequently filtered on specific columns, especially range predicates such as ORDER_DATE and additional selective filters like REGION. Defining a clustering key on commonly used filter columns helps align micro-partition metadata with query predicates, improving pruning and reducing the number of partitions scanned. For a continuously loaded large fact table, the architect should also monitor clustering information such as clustering depth/overlap and balance the performance gain against automatic clustering maintenance cost.
- B. Incorrect.
Incorrect. Search Optimization Service is designed primarily for highly selective point lookups and certain search patterns, not as the primary optimization for broad range filtering on very large fact tables. For repeated range predicates on date columns, clustering is usually the more appropriate design choice. This option reflects a common misconception that search optimization replaces clustering for all filtering workloads.
- C. Incorrect.
Incorrect. Temporary tables do not provide a mechanism for improving physical organization for pruning in this way. Making the table temporary would also change data lifecycle and session visibility semantics, which is unrelated to the performance problem. Snowflake does not rewrite micro-partitions more frequently simply because a table is temporary.
- D. Incorrect.
Incorrect. A larger warehouse can improve query execution speed through more compute, but it does not reorganize the table's micro-partition layout or improve pruning by itself. Pruning depends on micro-partition metadata and table organization relative to filter predicates. This option confuses compute scaling with storage layout optimization.