ADA-C01 Question 320
Single answerIdentify impact and solutions if data pruning is not happeningA Snowflake administrator is investigating a fact table that has grown to 8 TB. A dashboard query that filters on ORDER_DATE and REGION has become much slower over the last month, even though the warehouse size has not changed. Query Profile shows a very high percentage of micro-partitions scanned compared to micro-partitions total, and the table has frequent daily MERGE operations from multiple source systems. Which action is the BEST way to improve performance if data pruning is not occurring effectively?
- A
Define a clustering key on the columns commonly used in selective filters, such as ORDER_DATE and REGION, and monitor clustering depth to improve micro-partition pruning
- B
Increase the virtual warehouse size permanently so the query can scan all micro-partitions faster, since pruning behavior cannot be improved after data is loaded
- C
Create a search optimization service on every column in the table because it replaces the need for micro-partition pruning for large scan queries
- D
Convert the table to a temporary table and reload it weekly so Snowflake can rebuild all micro-partitions automatically for better pruning
Show answer and explanation
Correct answer: A
Explanation
Snowflake performance often depends on effective micro-partition pruning. If Query Profile shows that a query scans a large percentage of micro-partitions despite selective predicates, the engine is not able to eliminate enough data at scan time. This frequently happens when the physical organization of data no longer aligns with common filter columns, especially after heavy INSERT, UPDATE, or MERGE activity. For large fact tables filtered by date and dimensions, a clustering key on those columns can improve locality and pruning. Administrators should validate this using Query Profile and table clustering information rather than masking the issue by simply adding more compute. Snowflake documentation and best practices emphasize using clustering keys selectively for very large tables with common selective filters, while Search Optimization Service is intended for specific access patterns rather than broad replacement of partition pruning.
- A. Correct.
Correct. When Query Profile shows that most micro-partitions are being scanned, Snowflake is not effectively pruning data. A common cause is poor clustering caused by ongoing MERGE activity and data arriving in patterns that do not align with filter predicates. Defining an appropriate clustering key on frequently filtered columns such as ORDER_DATE and REGION can improve how related rows are co-located in micro-partitions, which improves pruning. Monitoring clustering information, such as clustering depth, helps validate whether reclustering is needed and whether the key is effective.
- B. Incorrect.
Incorrect. Increasing warehouse size may reduce elapsed time by adding compute, but it does not fix the root cause of poor pruning. The query would still scan far more micro-partitions than necessary, which is inefficient and can increase cost. Snowflake pruning behavior absolutely can be improved after load through better clustering strategy or by changing data organization patterns.
- C. Incorrect.
Incorrect. Search Optimization Service can help with highly selective point-lookups and some predicate patterns, but it is not a universal replacement for micro-partition pruning. Applying it to every column would usually be unnecessarily expensive and is not the best solution for a large fact table where common range and dimensional filters like ORDER_DATE and REGION should benefit from better clustering and pruning first.
- D. Incorrect.
Incorrect. Changing the table to temporary does not address the underlying organization of data for pruning and would introduce major data management and durability issues. Reloading weekly is not a standard or recommended solution for pruning problems. Snowflake automatically manages micro-partitions, but administrators improve pruning through schema/query design and clustering strategy, not by converting permanent fact tables to temporary tables.