Micro-Partitions, Clustering, and Performance

Pruning, clustering keys, search optimization, query profile, explain plans, spilling, and Snowflake cache layers.

You’ve written queries against a million-order dataset in earlier posts without once thinking about how the bytes are laid out on disk — and that’s the whole pitch. Snowflake hides the physical layer so completely that you can be productive for months and never learn how it works. But eventually a query gets slow, and “throw a bigger warehouse at it” stops being a satisfying answer. When that day comes, the thing you need to understand is the one thing Snowflake worked hardest to hide: how it stores data, and how it decides which of that data to not read. Almost all query performance in Snowflake is the story of data skipped, not data crunched.

The unit of storage: micro-partitions

When Snowflake loads a table, it doesn’t dump the rows into one big file. It slices them into micro-partitions — contiguous chunks of roughly 50 to 500 MB of uncompressed data each (smaller on disk, because Snowflake always compresses). A table isn’t one object; it’s thousands or millions of these little immutable files. Three properties matter, and each one earns its place:

Columnar. Within a micro-partition, data is stored column by column, not row by row. A query that selects three columns out of sixteen reads only those three columns’ worth of bytes and never touches the rest. This is why SELECT c_name, c_acctbal is dramatically cheaper than SELECT * on a wide table — it’s not a style preference, it’s less physical I/O.

Immutable. A micro-partition is never edited in place. An UPDATE or DELETE doesn’t rewrite the file — it writes new micro-partitions and marks the old ones as no longer part of the current table. This is exactly the mechanism that makes Time Travel and zero-copy cloning work (the old partitions are still there, just not referenced), and it’s why high-churn tables accumulate partitions that need cleaning up.

Self-describing. This is the one that makes performance happen. For every micro-partition, Snowflake records metadata: the minimum and maximum value of each column in that partition, the number of distinct values, the row count, and a few other properties. That metadata lives in the cloud-services layer, separate from the data itself, and it’s tiny relative to the data it describes.

Here’s why the min/max metadata is the whole ballgame. Suppose you filter WHERE o_orderdate = '1996-07-15'. Snowflake looks at the metadata for each micro-partition and asks a single cheap question: could this partition possibly contain that date? If a partition’s o_orderdate ranges from 1993-01-01 to 1993-03-31, the answer is no — and Snowflake skips the entire file without reading a single byte of it. That’s partition pruning, and it’s the single most important performance concept in the platform. A well-pruned query on a billion-row table might physically read a few dozen partitions. A badly-pruned one reads all of them.

Natural clustering, for free

Pruning only helps if the values you filter on are concentrated in a few partitions rather than smeared across all of them. And here’s the happy accident: data is usually loaded in some natural order — most often by time. Orders arrive and get loaded chronologically, so all of January’s orders land in the same handful of micro-partitions, February’s in the next handful, and so on. Snowflake calls this natural clustering — you didn’t ask for it, it fell out of the load order.

The consequence is that filters on the load-order column prune beautifully with zero effort on your part. If ORDERS was loaded by date, WHERE o_orderdate BETWEEN '1996-01-01' AND '1996-03-31' touches only the partitions covering that quarter. The trouble starts when your important filter is on a column that has nothing to do with load order — a c_custkey, a status flag, a region. Those values are scattered across every partition, min/max ranges overlap everywhere, and pruning can’t help. That’s the exact situation clustering keys exist to fix — but before we spend credits reorganizing anything, we need to see the problem.

Reading the Query Profile

Snowsight’s Query Profile is the first tool to reach for, and it’s free — every query you run already has one. Run a query, open its detail from the Query History page, and click the Query Profile tab. You get a diagram of operators, and two numbers that tell you almost everything.

Set your context and run something that prunes well:

USE DATABASE SNOWFLAKE_SAMPLE_DATA;
USE SCHEMA TPCH_SF1;

SELECT SUM(o_totalprice)
FROM orders
WHERE o_orderdate BETWEEN '1996-01-01' AND '1996-03-31';

Open the profile and find the TableScan operator. Under its statistics you’ll see Partitions scanned and Partitions total — say, 26 / 3242. That ratio is the pruning ratio, and it’s the number to internalize. Reading 26 partitions out of 3,242 means Snowflake skipped 99% of the table using metadata alone. That’s a healthy query.

Now break pruning on purpose with a filter on a scattered column:

SELECT SUM(o_totalprice)
FROM orders
WHERE o_clerk = 'Clerk#000000951';

Open this profile and the same TableScan will read something close to all the partitions — 3242 / 3242 — because clerk IDs are spread across every micro-partition, so no partition can be ruled out by its min/max. Same table, same warehouse, wildly different amount of physical work. The Query Profile just showed you, in two numbers, why the second query is slower. This is the diagnosis before any fix.

While you’re in the profile, learn to spot the other tells:

  • The most expensive operator. The profile shows each operator’s percentage of execution time. If TableScan dominates, you have a scan/pruning problem. If a Join or Aggregate dominates, the shape of your query — join order, cardinality — is the cost, not the read.
  • Join order and exploding joins. A join that emits far more rows than either input feeds it (a fan-out, or an accidental near-cross-join) shows up as a Join operator with a huge output row count. That’s a query-logic problem no warehouse size will rescue.
  • Spilling. This is the big one, covered next.

Spilling is the warehouse crying for help

When an operation — a large sort, a big join, a wide aggregation — needs more memory than the warehouse has, Snowflake doesn’t fail. It spills the overflow to disk. First to the warehouse’s local SSD (Bytes spilled to local storage), and if that fills up, to remote cloud storage (Bytes spilled to remote storage). Both appear in the Query Profile, and both are slow — remote spilling is catastrophically slow, often the difference between a query that takes two minutes and one that takes forty.

Spilling is the single clearest signal that a warehouse is too small for the work. Not too small in general — too small for this query’s memory footprint. The fix is usually to size up (a larger warehouse has proportionally more memory and local SSD), or to reduce the data reaching the memory-hungry operator by pruning and filtering earlier. If you see remote spill, don’t tune the SQL first — size up and confirm the spill disappears, then decide whether the bigger warehouse is worth keeping.

The same numbers, after the fact

The Query Profile is per-query and interactive. To find slow queries you didn’t watch run, query the history views. The account-level view lives in SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY (a role with the right grants, and up to ~45 minutes of latency); for the current session and recent history there’s the INFORMATION_SCHEMA.QUERY_HISTORY table function. The columns worth knowing by name:

SELECT
  query_id,
  LEFT(query_text, 60)                    AS sql_preview,
  total_elapsed_time / 1000               AS seconds,
  partitions_scanned,
  partitions_total,
  bytes_scanned,
  bytes_spilled_to_local_storage          AS spill_local,
  bytes_spilled_to_remote_storage         AS spill_remote,
  percentage_scanned_from_cache
FROM SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY
WHERE start_time > DATEADD('hour', -24, CURRENT_TIMESTAMP())
  AND total_elapsed_time > 10000          -- slower than 10s
ORDER BY total_elapsed_time DESC
LIMIT 20;

partitions_scanned versus partitions_total is your pruning ratio without opening a profile. A spill_remote above zero flags a memory-starved warehouse. percentage_scanned_from_cache tells you how much came from the warehouse’s local cache versus fresh reads. This one query is a standing performance dashboard — the queries at the top with a bad pruning ratio are your clustering candidates.

EXPLAIN: the estimate before you pay

EXPLAIN gives you the plan Snowflake would use, without running the query or spinning a warehouse — it reads only metadata, so it’s free and instant. It’s how you sanity-check an expensive query before committing to it:

EXPLAIN
SELECT SUM(o_totalprice)
FROM orders
WHERE o_orderdate BETWEEN '1996-01-01' AND '1996-03-31';

The output includes partitionsAssigned and partitionsTotal — an estimate of how many partitions the query will scan. If EXPLAIN says a filter you expected to be selective still assigns nearly every partition, you’ve learned before spending a credit that pruning won’t save you. Treat EXPLAIN as the cheap preview and the Query Profile as the ground truth after the fact.

Clustering keys: reorganizing for pruning

Back to the scattered-filter problem. If a large table is constantly queried with a selective filter on a column that isn’t its load order, you can tell Snowflake to physically reorganize the data so that column’s values are co-located in partitions — restoring pruning. That’s a clustering key.

You can’t cluster the read-only sample data, so make your own copy first (this also mirrors what real ELT produces):

CREATE DATABASE IF NOT EXISTS play;
USE DATABASE play;
USE SCHEMA public;

CREATE TABLE orders_local AS
SELECT * FROM SNOWFLAKE_SAMPLE_DATA.TPCH_SF1.orders;

Suppose your dashboards constantly filter by clerk. Define a clustering key:

ALTER TABLE orders_local CLUSTER BY (o_clerk);

Before and after, inspect the clustering with the system function. It reports how well-organized the table is with respect to a given key:

SELECT SYSTEM$CLUSTERING_INFORMATION('orders_local', '(o_clerk)');

You get back JSON. The number that matters is average_depth — the clustering depth, the average number of micro-partitions that overlap in value range at any given point. Depth of 1 is perfect: no two partitions share a value range for that key, so a point filter reads exactly one partition. A large depth means heavy overlap — a filter can’t rule partitions out, and pruning fails. The partition_depth_histogram in the same output shows the distribution. Watch average_depth for (o_clerk) fall after clustering; that falling number is pruning being restored, and it’s what you’ll see reflected as a better partitions-scanned ratio on your clerk queries.

A clustering key is not an index. It doesn’t build a separate lookup structure — it changes the order in which rows are stored so that min/max metadata becomes selective again. You get pruning for range and equality filters on the key, and you get it transparently, but you don’t get anything O(1) or point-lookup-shaped from it. And you cluster on one key expression (which may combine columns), not many — reorganizing physical storage can only satisfy one ordering at a time.

Automatic Clustering, and its meter

Defining CLUSTER BY doesn’t reorganize the table once and stop. As new rows load and existing ones change, the table drifts out of order. Snowflake keeps it clustered with Automatic Clustering — a serverless background process that reclusters partitions as needed. There’s no warehouse to size and no job to schedule; you turn it on by defining the key. Available across editions, including Standard.

The catch is the bill. Serverless reclustering consumes credits — billed separately from your warehouses, on Snowflake-managed compute — and every reclustering rewrites micro-partitions, which means new storage (and the old partitions linger for Time Travel and Fail-safe, so storage temporarily grows). On a high-churn table, Snowflake may be reclustering constantly, and the cost can quietly exceed the query time it saves. Watch the spend in the AUTOMATIC_CLUSTERING_HISTORY view, and if you need to pause it:

ALTER TABLE orders_local SUSPEND RECLUSTER;
-- ... later ...
ALTER TABLE orders_local RESUME RECLUSTER;

When a clustering key pays off: a large table (Snowflake’s own guidance points at the multi-hundred-GB-to-TB range — small tables prune fine as-is and don’t justify the reclustering cost); queried frequently with a selective filter or join on a non-load-order column; and not so write-heavy that reclustering never catches up. When it doesn’t: small tables; tables already well-ordered by their natural load column (you already have the pruning for free — don’t pay to re-derive it); high-churn tables where reclustering cost outruns query savings; and low-selectivity filters where even perfect clustering still reads most of the table. Cluster in response to a measured bad pruning ratio on a big, important table — never preemptively.

Search Optimization: the other tool, for the other shape

Clustering is built for range and scan queries — “give me this quarter,” “this customer’s orders.” It’s the wrong tool for a needle-in-a-haystack point lookup — “find the one row with this exact order key” — across a huge table, especially when you need selective lookups on several different columns (clustering can only order by one).

That’s the Search Optimization Service, and it’s an Enterprise edition feature (Enterprise, Business Critical, VPS — not Standard). Instead of reorganizing data like clustering, it builds and maintains a separate persistent search-access structure alongside the table — closer in spirit to an index. You add it per table (optionally scoped to specific columns or expressions):

-- requires Enterprise edition or higher
ALTER TABLE orders_local ADD SEARCH OPTIMIZATION;

-- or scope it to specific columns
ALTER TABLE orders_local ADD SEARCH OPTIMIZATION ON EQUALITY(o_orderkey, o_custkey);

It accelerates highly selective queries — equality and IN predicates, substring and wildcard searches, and lookups into VARIANT and geospatial data — the point-lookup patterns clustering can’t help. Like automatic clustering it’s serverless, so it costs credits to build and maintain, plus storage for the structure it keeps. Check whether a query benefits with SYSTEM$ESTIMATE_SEARCH_OPTIMIZATION_SAVINGS before committing.

The clean mental split: clustering co-locates data for range/scan queries and works on one key; search optimization builds an auxiliary structure for point lookups and can cover many columns. Reach for clustering when you slice by ranges; reach for search optimization when you fish for exact rows. They’re not competitors — a big table can have both.

Query Acceleration: renting scan muscle by the second

One more paid lever, for a specific pain. Sometimes a query isn’t slow because of bad pruning or a bad plan — it’s a big, scan-heavy query that occasionally shows up on a warehouse sized for everyday work, and buying a permanently larger warehouse for those rare spikes is wasteful. The Query Acceleration Service (QAS, Enterprise edition and up) handles that. When enabled on a warehouse, Snowflake can offload the parallelizable scan-and-filter portion of an eligible query onto serverless compute borrowed for the duration, then hand the results back — you get burst horsepower without permanently sizing up.

-- requires Enterprise edition or higher
ALTER WAREHOUSE analyst_wh SET
  ENABLE_QUERY_ACCELERATION = TRUE
  QUERY_ACCELERATION_MAX_SCALE_FACTOR = 8;

It bills as serverless credits for the borrowed compute, and only helps queries with lots of offloadable scan work (skewed, large-scan queries — not small ones or ones dominated by joins). SYSTEM$ESTIMATE_QUERY_ACCELERATION tells you whether a given query would benefit before you switch it on. Like search optimization, it’s a measured, targeted tool — not a default.

The three caches (and why your timing lies)

Some “fast” queries aren’t fast at all — they just didn’t do any work. Snowflake has three distinct caches, and knowing which one you hit is the difference between a real measurement and a fooled one.

1. Result cache — the whole answer, for 24 hours. When a query completes, Snowflake stores its result set for 24 hours. Run a byte-for-byte identical query text again — and if the underlying data hasn’t changed and your role has the privileges — Snowflake returns the stored result without a warehouse and without a single credit. The Query Profile for such a run shows a single QUERY RESULT REUSE node. This is why your third demo of the same query is suspiciously instant. When you actually want to time something, disable it for the session so every run does real work:

ALTER SESSION SET USE_CACHED_RESULT = FALSE;

Turn it back on (or reconnect) when you’re done — it saves real money in production, where identical dashboard queries repeat all day.

2. Metadata cache — answers with no data read at all. Remember the per-partition min, max, and count metadata? For some queries, that metadata is the answer. SELECT COUNT(*) FROM orders_local, or SELECT MIN(o_orderdate), MAX(o_orderdate) FROM orders_local, can often be satisfied entirely from metadata in the cloud-services layer — no warehouse compute, no partition scan. Try COUNT(*) on a huge table and watch it return instantly even on a cold XS warehouse; that’s the metadata cache, not speed. It’s also why these particular aggregates are nearly free and safe to run casually.

3. Warehouse-local data cache — the SSD that forgets. As a warehouse scans micro-partitions, it keeps the raw data on its local SSD. A follow-up query that needs the same partitions reads them from local SSD instead of remote storage — much faster, and it shows up as percentage_scanned_from_cache in the history view. The catch is in the word local: this cache belongs to the running warehouse and is wiped when the warehouse suspends or is resized. Suspend to save money (as the cost post urged) and the next query starts cold. Resize to go faster and you also throw away the warm cache. That’s the quiet tension between the aggressive AUTO_SUSPEND that saves credits and the warm cache that saves seconds — there’s no free answer, only a workload-dependent trade.

Diagnosing a slow query, start to finish

Tie it together into a workflow you can run every time something drags:

  1. Look at the Query Profile first. Which operator eats the time? That single answer routes everything below.
  2. If TableScan dominates, read the pruning ratio. Partitions scanned versus total. Good ratio (a small fraction) means pruning already works — the problem is elsewhere. Bad ratio (most or all partitions) means your filter isn’t pruning.
  3. A bad ratio on a big table, filtered on a non-load-order column, is a clustering (or search-optimization) candidate. Range/scan filter → clustering key. Point lookup → search optimization (Enterprise). Check SYSTEM$CLUSTERING_INFORMATION / the savings estimator before committing serverless spend.
  4. If you see spilling — especially remote — the warehouse is too small for this query’s memory. Size up, confirm the spill vanishes, decide if it’s worth keeping.
  5. If a Join or Aggregate dominates, it’s the SQL, not the storage. Look for fan-out joins, missing filters, needless SELECT *. No warehouse size fixes a bad plan.
  6. Before you trust any timing, remember the caches. A too-good number is probably the result cache — set USE_CACHED_RESULT = FALSE and rerun cold. A blazing COUNT(*)/MIN/MAX is the metadata cache, not a fast warehouse.
  7. Only then reach for the paid accelerators. Search Optimization and the Query Acceleration Service (both Enterprise) are worth real money for the right pattern — after you’ve measured, never as a first-line charm against slow SQL.

Final thoughts

Every knob in this post is a variation on one idea: the fastest data to process is data you never read. Micro-partition metadata lets Snowflake skip files; clustering keeps the right files skippable when load order won’t; search optimization builds a shortcut to the exact rows; the caches skip the work entirely. Performance tuning here isn’t about making the engine grind harder — it’s about arranging things so it grinds less, and then reading the Query Profile to confirm it actually did.

The trap the platform sets is that it’s fast enough by default that you can tune by superstition for a long time and appear to succeed — resize randomly, sprinkle a clustering key, feel clever when the query speeds up (it was the result cache). Resist that. The pruning ratio and the spill bytes are real numbers sitting in every query’s profile, and they’ll tell you exactly which lever to pull. Measure first, pull one lever, measure again. That loop, not a bigger warehouse, is what actually makes Snowflake fast.

Next: Cost, Credits, and Resource Monitors

Comments