Snowflake's pruning behavior with subqueries: Which of the following is true?

• A. Snowflake prunes micro-partitions based on a predicate with a subquery.
• B. Snowflake does not prune micro-partitions based on a predicate with a subquery.
• C. Snowflake prunes micro-partitions only when a join with a subquery exists.
• D. Snowflake never prunes micro-partitions.

Answer: (B)

Micro-partition pruning relies on metadata like min/max values for each column to skip partitions without reading the data. This works best with simple, directly evaluable predicates on a column. When a predicate includes a subquery, Snowflake can’t determine the subquery’s result from partition metadata alone, especially if the subquery is correlated or depends on data outside the partition being considered. Because the pruning decision can’t be made upfront, Snowflake ends up scanning more micro-partitions rather than pruning them based on that predicate. That’s why the statement that Snowflake does not prune micro-partitions based on a predicate with a subquery is the correct description.