COF-C03 Question 291
Single answerMetadata cacheA data engineering team runs the same filter-heavy query every 10 minutes against a large partitioned table in Snowflake. The query uses a selective predicate on a date column, and the team notices that Snowflake consistently scans only a small subset of the table's micro-partitions. They want to understand which Snowflake capability is primarily enabling this reduction in scanned data and what action would best preserve this benefit as the table grows. Which option is the BEST answer?
- A
The warehouse data cache is preventing remote storage reads, and increasing warehouse size is the best way to preserve the reduction in scanned data.
- B
The result cache is reusing the full prior query output, and rewriting the query text periodically is the best way to preserve the reduction in scanned data.
- C
The metadata cache is allowing Snowflake to use micro-partition metadata for pruning, and maintaining good clustering on commonly filtered columns helps preserve this benefit.
- D
The search optimization service is automatically enabled for large tables, and no table design action is needed to preserve the reduction in scanned data.
Show answer and explanation
Correct answer: C
Explanation
The best answer is Option 3. In Snowflake, micro-partitions contain rich metadata maintained by the cloud services layer, including column-level statistics such as minimum and maximum values. This metadata enables partition pruning, sometimes discussed in exam contexts as use of metadata cache/metadata to avoid scanning unnecessary micro-partitions. When queries use selective predicates, Snowflake can eliminate irrelevant micro-partitions before scanning, which is why only a small subset of the table is read.
To preserve this advantage as the table grows, it is important to maintain data organization that supports pruning. In practice, this means considering clustering keys or otherwise ensuring that commonly filtered columns remain well-clustered, especially for very large tables with frequent selective filters. This aligns with Snowflake best practices around micro-partitions, clustering, and pruning. By contrast, result caching avoids re-execution entirely for identical eligible queries, and warehouse cache reduces repeated I/O, but neither is the primary reason Snowflake can skip irrelevant micro-partitions.
- A. Incorrect.
Incorrect. The warehouse cache, often called the local disk cache, can reduce latency by reusing previously read data from SSD cache on the virtual warehouse. However, it does not explain why Snowflake scans only a small subset of micro-partitions in the first place. That behavior is primarily due to partition pruning based on metadata. Increasing warehouse size may improve performance in some cases, but it does not preserve pruning efficiency as data distribution worsens over time.
- B. Incorrect.
Incorrect. The result cache returns a previously computed result set when the underlying data and query conditions allow reuse. In the scenario, the key observation is that Snowflake is scanning only a subset of micro-partitions, which indicates pruning during query execution rather than full result reuse. Rewriting query text periodically would actually reduce result cache reuse rather than preserve pruning behavior.
- C. Correct.
Correct. Snowflake stores metadata about each micro-partition, such as min/max values and other statistics for columns. The optimizer uses this metadata to prune micro-partitions that cannot satisfy a selective predicate, reducing scanned data. As the table grows, maintaining good clustering on columns frequently used in filters, such as the date column in this scenario, helps keep relevant values grouped so pruning remains effective.
- D. Incorrect.
Incorrect. Search Optimization Service is a distinct optional feature that can improve performance for certain highly selective point-lookups and search predicates, but it is not automatically enabled for large tables. The scenario specifically describes behavior explained by micro-partition pruning using metadata. Table design and clustering can matter significantly in preserving that benefit.