DAA-C01 Question 177
Single answerLeverage partition pruningA retail analytics team stores 4 years of point-of-sale data in a large Snowflake table named SALES_FACT. The table has columns ORDER_TS (TIMESTAMP_NTZ), ORDER_DATE (DATE), STORE_ID, REGION, and NET_AMOUNT. Most analyst queries filter on recent time periods, such as the last 7 or 30 days. A dashboard query is running slowly:
SELECT STORE_ID, SUM(NET_AMOUNT) FROM SALES_FACT WHERE TO_DATE(ORDER_TS) BETWEEN '2025-01-01' AND '2025-01-31' GROUP BY STORE_ID;
The team wants to improve performance specifically by increasing the likelihood of micro-partition pruning, without changing the business result. Which change is the BEST option?
- A
Rewrite the filter to use the native DATE column directly: WHERE ORDER_DATE BETWEEN '2025-01-01' AND '2025-01-31'
- B
Wrap the predicate in a subquery so Snowflake can optimize it after aggregation
- C
Replace BETWEEN with IN over all dates in January 2025 because IN lists are pruned more efficiently than range predicates
- D
Cast STORE_ID to VARCHAR in the SELECT list so Snowflake reads fewer micro-partitions
Show answer and explanation
Correct answer: A
Explanation
The best answer is to filter directly on ORDER_DATE. Snowflake stores metadata for each micro-partition, including column-level min/max values and other statistics, which it uses for partition pruning. Queries prune best when predicates are sargable in practice for Snowflake's metadata evaluation, that is, when they reference the stored column directly rather than wrapping it in a function. In this scenario, TO_DATE(ORDER_TS) forces evaluation of an expression and can limit Snowflake's ability to eliminate irrelevant micro-partitions efficiently. Using ORDER_DATE preserves the business logic while improving the chance that only January 2025 partitions are scanned. This aligns with Snowflake best practices for query performance: use selective filters on columns commonly used in predicates, avoid unnecessary transformations in filter conditions, and design tables or derived columns to support common access patterns.
- A. Correct.
Correct. Partition pruning in Snowflake relies on micro-partition metadata such as min/max values for columns. Filtering directly on ORDER_DATE allows Snowflake to use that metadata more effectively. In contrast, applying a function to ORDER_TS in the predicate, such as TO_DATE(ORDER_TS), can reduce pruning effectiveness because the filter is on an expression rather than directly on the stored column with metadata. Using a dedicated DATE column that aligns with common filter patterns is a practical way to improve pruning.
- B. Incorrect.
Incorrect. A subquery does not inherently improve micro-partition pruning. Pruning is primarily determined by how well the WHERE predicate can be evaluated against micro-partition metadata before scanning data. Wrapping the logic in a subquery after aggregation does not address the main issue, which is the function applied to ORDER_TS in the filter.
- C. Incorrect.
Incorrect. Snowflake can prune effectively on range predicates when they align with column metadata. Replacing a date range with a long IN list is not a general best practice for pruning and may make the query less readable and harder to maintain. The real optimization opportunity is to filter directly on a column whose stored values and metadata match the predicate.
- D. Incorrect.
Incorrect. The SELECT list does not drive partition pruning for this query, and casting STORE_ID affects only result projection, not which micro-partitions must be scanned. Pruning depends on selective predicates in the WHERE clause, especially on columns with useful clustering characteristics or natural ordering.