ARA-C01 Question 424
Single answerMicro-partition pruningA retail company stores 8 TB of order history in a Snowflake table named FACT_ORDERS. Analysts most frequently run queries for recent data using predicates such as WHERE ORDER_DATE BETWEEN '2025-01-01' AND '2025-01-31' AND REGION = 'EMEA'. Query Profile shows a high percentage of partitions scanned, and the architect confirms the table was loaded over time from many sources with no deliberate ordering. The team wants to improve query performance by increasing micro-partition pruning without changing query results. Which action is the BEST recommendation?
- A
Define a clustering key on (ORDER_DATE, REGION) and allow Snowflake to reorganize the table so micro-partitions have tighter value ranges for the commonly filtered columns.
- B
Create a search optimization service on every column in FACT_ORDERS because search optimization replaces micro-partition pruning for range predicates.
- C
Increase the virtual warehouse size from Medium to 2XL so Snowflake can scan all micro-partitions faster and therefore prune more partitions.
- D
Create a materialized view that selects all columns from FACT_ORDERS without filters, because materialized views automatically recluster base table micro-partitions.
Show answer and explanation
Correct answer: A
Explanation
The best answer is to improve clustering for the columns used most often in selective filters. Snowflake stores metadata for each micro-partition, including column value ranges, which enables pruning when a query predicate cannot possibly match data in certain partitions. When rows are inserted in a disorderly way across common filter columns, many micro-partitions end up with overlapping ranges, forcing Snowflake to scan more partitions. A clustering key on frequently filtered columns such as ORDER_DATE and REGION can improve co-location of similar values and reduce overlap, increasing pruning effectiveness. This aligns with Snowflake guidance on clustering large tables with selective, repeated query filters. By contrast, warehouse resizing improves compute throughput but not pruning logic; Search Optimization Service is for different access patterns and should be applied selectively; and a full-copy materialized view does not inherently solve the micro-partition layout issue on the base table.
- A. Correct.
Correct. Micro-partition pruning relies on Snowflake metadata such as minimum and maximum values stored for columns in each micro-partition. Because the table was loaded without useful ordering, ORDER_DATE and REGION values are likely spread across many micro-partitions, reducing pruning effectiveness. Defining an appropriate clustering key on the columns most commonly used in selective filters can improve clustering depth over time and allow Snowflake to skip more micro-partitions for queries like the one shown. This is a standard design approach when large tables are frequently filtered on specific columns and natural clustering is poor.
- B. Incorrect.
Incorrect. Search Optimization Service can help certain highly selective point lookup and substring/search patterns, but it does not replace micro-partition pruning and is not the primary recommendation for broad range predicates like
BETWEENon a large fact table. Applying it to every column would also be unnecessarily expensive and is not a best-practice response to poor pruning caused by weak clustering. - C. Incorrect.
Incorrect. A larger warehouse may reduce elapsed time by adding compute, but it does not improve the percentage of micro-partitions pruned. Pruning is a data layout and metadata optimization, not a warehouse sizing feature. Someone might choose this because bigger warehouses can make scans faster, but the scenario specifically asks how to increase micro-partition pruning.
- D. Incorrect.
Incorrect. A materialized view can improve performance for some repeated query patterns, but creating one that simply selects all columns with no filtering or aggregation does not automatically improve pruning on the base table. Materialized views maintain their own storage and can have their own pruning behavior, but they are not a substitute for improving clustering of the underlying large table in this scenario.