Which design yields better partition pruning when you have a CITY attribute used for filtering in JSON data?

• A. Use a single VARIANT column.
• B. Create a separate CITY column and load CITY.
• C. Both yield equal pruning.
• D. Neither yields pruning.

Answer: (B)

Partition pruning relies on using accurate statistics of the columns involved in a filter. When city information is stored inside a JSON structure in a single VARIANT column, Snowflake would need to extract that nested value to evaluate the predicate, which makes it difficult for the engine to prune micro-partitions efficiently. In contrast, a separate CITY column becomes a real scalar column with its own statistics (min/max, distinct values, etc.). With this, Snowflake can quickly skip partitions that don’t match the city filter, leading to much better partition pruning and query performance.

So, creating a dedicated CITY column and loading CITY provides the prune-friendly structure needed for efficient partition pruning. A design using only the VARIANT column tends to limit pruning effectiveness, and designs that don’t leverage a dedicated city column don’t improve pruning.