ADA-C01 Question 321
Single answerIdentify impact and solutions if data pruning is not happeningA Snowflake administrator is investigating a dashboard query against a 12 TB SALES_FACT table. The query typically filters on ORDER_DATE and REGION, but recent query profile results show that almost all micro-partitions are being scanned even when a selective date range is provided. The table receives frequent batch updates and has no defined clustering key. Users report increasing latency and warehouse consumption for these filtered queries. Which action would MOST directly improve pruning for this workload?
- A
Define a clustering key on ORDER_DATE and REGION, then allow Snowflake to recluster the table over time
- B
Increase the virtual warehouse size so more micro-partitions can be scanned in parallel
- C
Create a materialized view on the entire SALES_FACT table without changing table organization
- D
Suspend and resume the warehouse before running the query so metadata is refreshed
Show answer and explanation
Correct answer: A
Explanation
When pruning is not happening, Snowflake must scan many more micro-partitions than necessary, which increases query latency and warehouse credit consumption. In this scenario, the filtered query predicates are ORDER_DATE and REGION, but nearly all micro-partitions are still scanned. That strongly suggests poor clustering depth or high overlap of values across micro-partitions, often caused by repeated DML such as batch updates. The most direct corrective action is to align table organization with the filter pattern by defining a clustering key on the columns frequently used in selective predicates. Snowflake documentation on micro-partitions, data clustering, and clustering keys explains that pruning depends on metadata and value distribution across micro-partitions. Warehouse resizing can add compute but does not reduce scanned data. Materialized views can be useful in targeted designs, but they are not the primary fix for a base table suffering from poor pruning unless the workload is specifically rewritten to use the MV. Best practice is to review query profile metrics, micro-partitions scanned, and clustering information, then choose clustering only when the workload and table size justify its maintenance cost.
- A. Correct.
Correct. Snowflake data pruning relies on micro-partition metadata such as min/max values and overlap. If frequent batch updates have degraded the natural clustering of the table, rows for many ORDER_DATE and REGION values may be spread across many micro-partitions, reducing pruning effectiveness. Defining an appropriate clustering key on the commonly filtered columns can improve co-location of related rows and reduce the number of micro-partitions scanned for selective predicates. This directly addresses the pruning problem described.
- B. Incorrect.
Incorrect. Increasing warehouse size may reduce elapsed runtime through more compute parallelism, but it does not improve pruning. The query would still scan roughly the same set of micro-partitions, so compute consumption may remain high or even increase. This is a performance scaling action, not a data organization fix.
- C. Incorrect.
Incorrect. A materialized view can help some query patterns, but creating one on the entire base table without addressing organization does not directly solve poor pruning on the base table. It also introduces maintenance cost. Materialized views are typically beneficial when they precompute a filtered, aggregated, or projected subset that matches a recurring workload. The scenario specifically asks for the most direct way to improve pruning for the filtered workload on the large fact table.
- D. Incorrect.
Incorrect. Suspending and resuming a warehouse does not refresh table micro-partition metadata in a way that improves pruning. Pruning decisions are based on Snowflake's storage metadata and table organization, not on warehouse session state. This option reflects a common misconception that compute restarts can resolve storage layout issues.