Snowflake interview questionsQuestion 2 of 2
Snowflake interview question · Question 2 of 2
How do micro-partitions affect Snowflake query performance?
Short answer
Snowflake stores each table as many small immutable micro-partitions and keeps metadata such as the minimum and maximum value of every column in each one. When a query filters on a column, Snowflake skips micro-partitions whose ranges cannot match, which is called pruning. So performance depends on data layout: if values of the filtered column are well clustered, most partitions are skipped; if they are scattered, the query scans almost everything, and a clustering key may help on very large tables.
Detailed explanation
- Each micro-partition has per-column min/max metadata.
- Filters are compared against that metadata before any data is read.
- Well-clustered data means narrow, non-overlapping ranges, so more is skipped.
Check partitions scanned versus total in the query profile. Poor pruning on a large table with a frequent filter is the signal to consider CLUSTER BY.
Why pruning might fail
- Filter wraps the column in a function or casts it.
- Values are scattered (for example, loads not ordered by the filtered column).
- The filter is on a column joined from another table rather than the scanned table.
Common mistakes
- Clustering small or rarely queried tables.
- Choosing a unique-id clustering key.
Progress is saved in this browser only. No account needed.