COF-C03 Question 261
Single answerInefficient pruningA retail company stores 4 years of order history in a large Snowflake table named ORDERS. Analysts frequently run queries such as:
SELECT * FROM ORDERS WHERE TO_DATE(ORDER_TS) = '2024-11-15' AND REGION = 'EMEA';
Although the table is clustered naturally by load time, query profiles show that a very high percentage of micro-partitions are scanned even when filtering on a single day. The team wants to reduce inefficient pruning without changing the business result. Which action is the BEST choice?
- A
Rewrite the predicate to filter directly on ORDER_TS using a timestamp range, for example ORDER_TS >= '2024-11-15' AND ORDER_TS < '2024-11-16'
- B
Increase the virtual warehouse size so Snowflake can prune more micro-partitions in parallel
- C
Create a view on ORDERS that selects only REGION = 'EMEA' rows and have analysts query the view instead
- D
Use LIMIT 1000 in analyst queries so Snowflake scans fewer micro-partitions before applying the filter
Show answer and explanation
Correct answer: A
Explanation
This scenario tests understanding of micro-partition pruning in Snowflake. Snowflake stores metadata such as min/max column values for each micro-partition and uses that metadata to skip partitions that cannot satisfy a predicate. Pruning works best when predicates are written directly against the underlying column values. Wrapping a column in a function like TO_DATE(ORDER_TS) can make pruning less effective because Snowflake may not be able to use the raw micro-partition metadata as efficiently as it can with a direct range predicate. Rewriting the condition to a timestamp range is a common best practice for improving pruning while preserving results. Warehouse size affects compute resources, not pruning selectivity. Views do not change physical storage, and LIMIT does not reduce scan requirements for predicate evaluation. This aligns with Snowflake guidance on query performance, micro-partitions, and clustering/pruning best practices.
- A. Correct.
Correct. Applying a function such as TO_DATE() to a column in the filter can reduce Snowflake's ability to use micro-partition metadata effectively for pruning. Rewriting the predicate as a sargable range on the base column ORDER_TS allows Snowflake to compare the stored min/max metadata for micro-partitions more directly, which typically improves pruning. This preserves the business meaning of filtering to a single calendar day.
- B. Incorrect.
Incorrect. Increasing warehouse size can improve execution speed through more compute, but it does not fix the underlying pruning problem. Micro-partition pruning is driven by table metadata and query predicates, not by warehouse size. A larger warehouse may scan the same unnecessary partitions faster, but it does not inherently make pruning more selective.
- C. Incorrect.
Incorrect. A view does not reorganize underlying micro-partitions or improve pruning by itself. If the base table still requires evaluation of TO_DATE(ORDER_TS) and the data layout remains the same, the same pruning inefficiency can persist. A view may simplify query patterns, but it is not the best direct fix for this issue.
- D. Incorrect.
Incorrect. LIMIT reduces the number of rows returned, not the amount of data Snowflake may need to scan to identify qualifying rows. Snowflake still must evaluate the filter conditions, so LIMIT generally does not solve inefficient micro-partition pruning for this scenario.