DAA-C01 Question 178
Single answerLeverage partition pruningA retail analytics team stores 4 years of order history in a large Snowflake table named ORDERS with columns ORDER_ID, CUSTOMER_ID, ORDER_TS, ORDER_DATE, STATUS, REGION, and TOTAL_AMOUNT. Most dashboards query only the last 7 days of completed orders. Query profiles show high bytes scanned even when the result set is small. The data engineering team wants to improve performance by increasing micro-partition pruning without changing the business result. Which change is the BEST option?
- A
Rewrite dashboard queries to filter directly on ORDER_DATE using a range predicate such as WHERE ORDER_DATE >= CURRENT_DATE - 7 AND STATUS = 'COMPLETE'
- B
Replace the date filter with WHERE DATE_TRUNC('DAY', ORDER_TS) >= CURRENT_DATE - 7 so Snowflake can align pruning to day boundaries
- C
Cast the column in the predicate using WHERE TO_DATE(ORDER_TS) >= CURRENT_DATE - 7 because explicit conversion improves metadata matching
- D
Create a view that selects only completed orders from ORDERS and point dashboards to the view without changing the query predicates
Show answer and explanation
Correct answer: A
Explanation
Snowflake stores table data in micro-partitions and records metadata such as value ranges for columns. During query execution, Snowflake can skip scanning micro-partitions whose metadata proves they cannot satisfy a predicate. To leverage partition pruning, write predicates that reference base columns directly and avoid wrapping those columns in functions when possible. In this scenario, using ORDER_DATE with a direct range predicate is the best change because it preserves the ability to compare the predicate against micro-partition metadata efficiently. By contrast, predicates like DATE_TRUNC(ORDER_TS) or TO_DATE(ORDER_TS) operate on the column value and can reduce pruning opportunities. Creating a view alone does not change physical storage or pruning behavior. This aligns with Snowflake best practices for query performance and micro-partition pruning: use selective filters on columns with useful metadata and prefer simple range predicates over expressions on filtered columns.
- A. Correct.
Correct. Partition pruning in Snowflake is most effective when predicates can be evaluated against micro-partition metadata, such as min/max values for columns. Filtering directly on ORDER_DATE with a simple sargable range predicate allows Snowflake to eliminate micro-partitions that fall outside the last 7 days. Including STATUS = 'COMPLETE' may also help if the column has useful clustering characteristics, but the main improvement here is avoiding expressions on the filtered date column.
- B. Incorrect.
Incorrect. Applying DATE_TRUNC to the column ORDER_TS introduces an expression on the column side of the predicate, which can reduce Snowflake's ability to prune micro-partitions efficiently compared with filtering directly on a stored date or timestamp column using a plain range predicate. Although aligning to day boundaries sounds beneficial, the function on the column is the problem.
- C. Incorrect.
Incorrect. Casting ORDER_TS with TO_DATE in the WHERE clause still applies a function to the column, which can limit pruning effectiveness. This is a common misconception: explicit conversion does not inherently improve metadata matching for pruning. In many cases, it makes pruning less effective than using a native column with a direct range filter.
- D. Incorrect.
Incorrect. A view does not physically reorganize data or change how micro-partition metadata is stored. If the underlying dashboard queries still use non-prunable predicates, bytes scanned will remain high. Views can simplify logic, but they do not by themselves improve partition pruning.