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
In a nutshell
  • 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.

Query with filterchecksMicro partition metadatamin and max per columnskipsIrrelevant partitionsreadsOnly matching partitions

How it works

Query complexity

Snowflake plans and runs complex SQL well, but some patterns cost far more than they appear.

PatternWhy it hurtsBetter approach
Join without a proper key, or on non unique keysRows multiply (exploding join), memory fills, spilling startsCheck join keys, deduplicate, filter before joining
Many nested views and CTEsLong compilation time, plan too largeMaterialize intermediate results into a temporary or transient table
UNION where UNION ALL is enoughExtra distinct stepUse UNION ALL
SELECT DISTINCT or ORDER BY on huge resultsGlobal sort or hash of everythingReduce rows first, sort only the final result
SELECT * on wide tablesReads every columnName the columns
Window functions over very large partitionsLarge partitions are processed together and can spillNarrow the partition key or pre filter
Functions on a filter column, such as WHERE TO_CHAR(order_date, ...) = ...Can defeat pruning on the stored valueFilter 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.

Exam tip

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 or MIN and MAX on 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

GuidelineDetail
Table sizeLarge tables, typically multiple terabytes
Query patternMany queries filter or sort on the same few columns
Number of columnsAt most 3 or 4 columns or expressions
Column orderLowest cardinality first, then higher
CardinalityAvoid very low (a boolean) and very high (a nanosecond timestamp) used directly
ExpressionsReduce a high cardinality column, for example by clustering on the date part of a timestamp
Preferred columnsFilter 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 RECLUSTER and RESUME RECLUSTER pause and restart it. AUTOMATIC_CLUSTERING_HISTORY shows credits used.
  • A clone keeps the key, but Automatic Clustering is suspended on the clone until you resume it. CREATE TABLE AS SELECT does not carry the key over.
Large tablefrequent filters on dateCLUSTER BYClustering keyserverlessAutomatic Clusteringbackground reclusteringresults inFewer partitions scanned
Watch out

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_INFORMATION and SYSTEM$CLUSTERING_DEPTH measure 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.