Question

A data engineer is investigating why a query with a WHERE clause filtering on an ORDER_DATE column scans far fewer micro-partitions on one large table than on another table of similar size. The well-performing table's data was loaded in ORDER_DATE order, while the other table's rows were loaded in random order. What Snowflake storage mechanism explains the difference in the number of micro-partitions scanned?

Accepted Answer

Snowflake stores min/max metadata per micro-partition and prunes partitions whose value ranges do not overlap the filter; naturally ordered data yields tighter, non-overlapping ranges

Answer

Snowflake automatically re-sorts every table on each query using the compute layer, so load order has no effect on scanning

Answer

The well-performing table must have a materialized view that the optimizer transparently substitutes for the base table

Answer

Micro-partitions are cached in the cloud services layer, and only tables loaded in sorted order are eligible for that cache