ARA-C01 Question 425
Select 2Micro-partition pruningA retail company stores 8 TB of order history in a Snowflake table named ORDERS_FACT. Most analytics queries filter on ORDER_DATE and REGION. The table was loaded over time from many source files and has become poorly organized. A key dashboard query is shown below and has recently become much slower and scans far more data than expected:
SELECT SUM(NET_AMOUNT) FROM ORDERS_FACT WHERE TO_DATE(ORDER_TS) BETWEEN '2025-01-01' AND '2025-01-31' AND REGION = 'EMEA';
Query Profile shows low micro-partition pruning. The architect wants to improve pruning for this query pattern without changing the business result. Which TWO actions are most appropriate?
- A
Rewrite the predicate to avoid applying a function to the filtered column, for example by using ORDER_TS >= '2025-01-01' AND ORDER_TS < '2025-02-01', while keeping the REGION filter.
- B
Define a clustering key on expressions aligned to the common filter pattern, such as (TO_DATE(ORDER_TS), REGION), and allow Snowflake to reorganize micro-partitions over time.
- C
Increase the virtual warehouse size for the dashboard queries so Snowflake can prune more micro-partitions during execution.
- D
Replace the table with a materialized view that selects all columns from ORDERS_FACT; this by itself will guarantee better micro-partition pruning for all queries.
- E
Convert ORDERS_FACT to a temporary table before running the dashboard query so that fresh metadata is generated for each session.
Show answer and explanation
Correct answers: A, B
Explanation
This scenario tests practical understanding of how Snowflake uses micro-partition metadata to eliminate irrelevant data during query execution. Pruning is most effective when predicates can be evaluated directly against stored metadata and when data values commonly filtered together are organized into fewer, more selective micro-partitions.
Two issues are present here:
- The query applies TO_DATE() to ORDER_TS in the WHERE clause. Rewriting this as a half-open timestamp range is a common best practice because it preserves semantics while making the predicate more pruning-friendly.
- The table is described as poorly organized after many loads. For a large fact table queried repeatedly by ORDER_DATE and REGION, a clustering key aligned to those access patterns is the appropriate Snowflake design choice to improve pruning.
By contrast, warehouse scaling affects compute capacity, not pruning effectiveness. Materialized views are workload-specific and not a blanket solution, especially if defined as a broad SELECT * object. Temporary tables do not inherently improve micro-partition pruning.
Relevant Snowflake documentation and best practices include guidance on micro-partitions and data clustering, including how pruning leverages per-micro-partition metadata and how clustering keys can improve scan reduction for large tables with selective predicates.
- A. Correct.
Correct. Micro-partition pruning relies on metadata such as min/max values stored for columns in each micro-partition. Applying a function directly to a filtered column, such as TO_DATE(ORDER_TS), can make pruning less effective because Snowflake cannot use the raw column metadata as directly as it can with a sargable range predicate. Rewriting the filter as a timestamp range preserves the same business logic for January 2025 and typically improves pruning on ORDER_TS. Keeping REGION in the predicate further helps because Snowflake can evaluate pruning across multiple filtered columns.
- B. Correct.
Correct. Clustering is the main physical design feature in Snowflake for improving pruning on large tables with selective filter patterns. If the table is poorly organized and common queries filter by date and region, defining a clustering key aligned to those predicates can improve co-location of similar values within micro-partitions and improve pruning. Using an expression such as TO_DATE(ORDER_TS) is valid when queries commonly filter at the date level rather than full timestamp granularity. Snowflake then maintains clustering depth over time through reclustering/automatic clustering behavior, which can reduce the amount of scanned data for these workloads.
- C. Incorrect.
Incorrect. A larger warehouse can improve query execution throughput by providing more compute resources, but it does not change the physical layout of table data or the micro-partition metadata used for pruning. If pruning is poor, scaling up compute may reduce elapsed time somewhat, but it does not solve the root cause of excessive partitions being scanned.
- D. Incorrect.
Incorrect. A materialized view can improve performance for specific query patterns when its precomputed result set matches the workload, but simply creating a materialized view that selects all columns from the base table does not inherently guarantee better pruning for all queries. It also introduces maintenance cost and may not address the underlying issue of non-sargable predicates and poor clustering on the base data layout.
- E. Incorrect.
Incorrect. Temporary tables are session-scoped objects, but converting a permanent fact table to a temporary table does not provide a pruning benefit. Snowflake already maintains micro-partition metadata for regular tables. Creating a temporary copy would add unnecessary data movement and would not inherently improve partition elimination unless the data were deliberately reorganized, which is not what this option states.