Performance Optimization · Topic 3 of 7
Query Complexity, Micro-partitions and Clustering
How query shape affects Snowflake performance, how micro-partition pruning works, and when a clustering key and Automatic Clustering are worth the cost.
- 5 min
- Micro-partitions are 50 to 500 MB uncompressed, immutable, columnar, with per column min and max metadata.
- Pruning skips micro-partitions using metadata; the profile shows partitions scanned vs total.
- Cluster keys suit large, frequently queried, rarely changed tables; use 3 or 4 columns at most, low cardinality first.
What it is
Two things decide most query speed that is not about warehouse size: the shape of the SQL and the layout of the data. Complex queries can overwhelm memory or produce huge intermediate results. A good layout lets Snowflake skip most of the table.
Snowflake stores table data in micro-partitions, and keeps metadata about each one so a query can read only the partitions it needs. This skipping is called pruning. A clustering key tells Snowflake to keep related rows together so pruning works better on large tables.
How it works
Query complexity
Snowflake plans and runs complex SQL well, but some patterns cost far more than they appear.
| Pattern | Why it hurts | Better approach |
|---|---|---|
| Join without a proper key, or on non unique keys | Rows multiply (exploding join), memory fills, spilling starts | Check join keys, deduplicate, filter before joining |
| Many nested views and CTEs | Long compilation time, plan too large | Materialize intermediate results into a temporary or transient table |
UNION where UNION ALL is enough | Extra distinct step | Use UNION ALL |
SELECT DISTINCT or ORDER BY on huge results | Global sort or hash of everything | Reduce rows first, sort only the final result |
SELECT * on wide tables | Reads every column | Name the columns |
| Window functions over very large partitions | Large partitions are processed together and can spill | Narrow the partition key or pre filter |
Functions on a filter column, such as WHERE TO_CHAR(order_date, ...) = ... | Can defeat pruning on the stored value | Filter on the raw column with a range |
A heavy query can be broken into steps, each writing to a temporary table, which cuts compile time.
The Query Profile shows the symptom of complexity: a join node whose output rows greatly exceed its input rows, remote spilling, or a long compilation time in Query History.
Micro-partitions
- Table data is split automatically into micro-partitions holding 50 MB to 500 MB of uncompressed data, stored compressed and in columnar form.
- They are immutable. An update or delete writes new micro-partitions and retires the old ones (which Time Travel keeps for the retention period).
- Snowflake records metadata for each one: the value range for every column, the number of distinct values, and other properties.
- Pruning uses that metadata first to drop whole micro-partitions, then reads only the needed columns within the rest.
- Some answers come from metadata alone, such as
COUNT(*)on a whole table orMINandMAXon a column, without scanning data.
Data is stored in the order it was loaded, so it is often naturally clustered by load time. A query filtering on a date column then prunes well. A filter on a column unrelated to load order may touch nearly every partition.
Clustering and its impact
Clustering means rows with similar values in the key columns sit in the same micro-partitions, so their value ranges overlap little. Clustering depth is the average number of overlapping micro-partitions for the key columns. A lower depth means better pruning. A table where no ranges overlap is in a constant state and cannot improve.
ALTER TABLE sales CLUSTER BY (sale_date, region);
SELECT SYSTEM$CLUSTERING_INFORMATION('sales', '(sale_date, region)');
SELECT SYSTEM$CLUSTERING_DEPTH('sales', '(sale_date)');
SYSTEM$CLUSTERING_INFORMATION returns JSON with the total number of micro-partitions, how many overlap and the depth. SYSTEM$CLUSTERING_DEPTH returns the depth alone. Treat the depth as a hint; real query performance is the best test.
Choosing a clustering key
| Guideline | Detail |
|---|---|
| Table size | Large tables, typically multiple terabytes |
| Query pattern | Many queries filter or sort on the same few columns |
| Number of columns | At most 3 or 4 columns or expressions |
| Column order | Lowest cardinality first, then higher |
| Cardinality | Avoid very low (a boolean) and very high (a nanosecond timestamp) used directly |
| Expressions | Reduce a high cardinality column, for example by clustering on the date part of a timestamp |
| Preferred columns | Filter and join columns over columns used only in GROUP BY or ORDER BY |
Automatic Clustering
Once a key is defined, Snowflake reclusters the table in the background using serverless compute. It reclusters only when the table will benefit, so rows are not reordered the moment you set the key.
- It costs serverless credits and extra storage, because reclustering creates new micro-partitions while the old ones stay for Time Travel and Fail-safe.
- Frequent DML on a clustered table increases the cost. Clustering pays off most when a table is queried often and changed rarely.
ALTER TABLE ... SUSPEND RECLUSTERandRESUME RECLUSTERpause and restart it.AUTOMATIC_CLUSTERING_HISTORYshows credits used.- A clone keeps the key, but Automatic Clustering is suspended on the clone until you resume it.
CREATE TABLE AS SELECTdoes not carry the key over.
Clustering is not free and not always useful. On small tables, or on columns queries never filter on, it adds credits and storage with no pruning benefit.
Clustering helps range filters on big tables. Point lookups need the search optimization service instead (next page).
Common mistakes
Clustering a small table. Pruning only matters when there are many micro-partitions to skip.
Choosing a high cardinality key as is. A raw timestamp or an id leaves almost no overlap to exploit and makes reclustering costly. Use an expression such as the date.
Clustering a table that changes constantly. Reclustering keeps chasing new data and burns credits.
Judging success by depth alone. Compare query times and partitions scanned before and after.
What to remember for the exam
- Micro-partitions are 50 to 500 MB uncompressed, immutable, columnar, with per column min and max metadata.
- Pruning skips micro-partitions using metadata; the profile shows partitions scanned vs total.
- Cluster keys suit large, frequently queried, rarely changed tables; use 3 or 4 columns at most, low cardinality first.
SYSTEM$CLUSTERING_INFORMATIONandSYSTEM$CLUSTERING_DEPTHmeasure clustering; Automatic Clustering is serverless and adds credit and storage cost.- Query shape matters: avoid exploding joins, needless
UNION,SELECT *and heavy sorts.
Check yourself
What does Snowflake use to decide which micro-partitions a query can skip?
Per column metadata such as the minimum and maximum values stored for each micro-partition.
Which functions report how well a table is clustered?
SYSTEM$CLUSTERING_INFORMATION and SYSTEM$CLUSTERING_DEPTH.
Why can Automatic Clustering raise storage cost?
Reclustering creates new micro-partitions, and the replaced ones are kept for Time Travel and Fail-safe.
Topic 3 of 7 in Performance Optimization
Test yourself on Performance Optimization
The full Snowflake DEA-C02 question bank has 709 questions, with timed practice exams and an explanation for every answer.
This study guide is independent and is not affiliated with, endorsed by, or sponsored by Snowflake. Vendor names, certification names and exam codes are trademarks of their respective owners and are used for identification only.