COF-C03 Question 260
Single answerInefficient pruningA data engineering team notices that a query against a 4 TB SALES_FACT table is scanning almost all micro-partitions even when users filter on ORDER_DATE for a single month. Query Profile shows very low pruning efficiency. The table is loaded continuously from multiple sources, and rows for many different dates are intermixed in each load batch. The most common query pattern filters on ORDER_DATE and sometimes on REGION. Which action would most directly improve pruning for these queries?
- A
Define a clustering key on (ORDER_DATE, REGION) and allow Snowflake to reorganize micro-partitions around these columns
- B
Increase the size of the virtual warehouse so that more micro-partitions can be scanned in parallel
- C
Create a materialized view that selects all columns from SALES_FACT without any filtering or aggregation
- D
Convert SALES_FACT from permanent to transient to reduce storage overhead and improve partition elimination
Show answer and explanation
Correct answer: A
Explanation
Snowflake stores table data in immutable micro-partitions and tracks metadata such as column min/max values. Pruning is efficient when a query predicate can use that metadata to skip micro-partitions that cannot contain matching rows. In this scenario, continuous mixed-date loading causes high overlap in ORDER_DATE ranges across micro-partitions, which reduces pruning efficiency. For frequently filtered large tables, Snowflake best practice is to consider clustering keys on selective, commonly filtered columns when natural clustering is poor and query performance suffers. Increasing warehouse size addresses compute, not pruning. Materialized views help only for targeted patterns, and transient tables do not change pruning behavior. Relevant Snowflake documentation includes guidance on micro-partitions, clustering keys, and query profile analysis for partition pruning.
- A. Correct.
Correct. Inefficient pruning happens when micro-partition metadata cannot effectively exclude irrelevant partitions. Because rows for many ORDER_DATE values are intermixed, the min/max metadata for ORDER_DATE across many micro-partitions overlaps heavily, so Snowflake must scan far more data. Defining a clustering key on commonly filtered columns such as ORDER_DATE and REGION can improve clustering depth over time and make micro-partition pruning more effective for those predicates.
- B. Incorrect.
Incorrect. A larger warehouse may reduce elapsed runtime by adding compute, but it does not improve pruning itself. Snowflake would still scan roughly the same set of micro-partitions; it would just do so faster. The problem described is poor partition elimination, not insufficient compute.
- C. Incorrect.
Incorrect. A materialized view that simply mirrors all columns without changing access patterns is not a direct fix for inefficient pruning on the base table. Materialized views are useful for specific precomputed query patterns, but they do not inherently solve broad pruning issues caused by poorly clustered micro-partitions in the source table.
- D. Incorrect.
Incorrect. Table type (permanent versus transient) affects data protection and storage-related features such as Fail-safe, not micro-partition pruning behavior. Changing to transient would not reorganize data or improve how ORDER_DATE filters eliminate micro-partitions.