Skip to content
Tutorials Center
My learning

Topics

Lesson 6 of 38 ยท Architecture

Micro-partitions and Columnar Storage

Learn how Snowflake stores tables in micro-partitions, how metadata enables partition pruning, why columnar format is fast, and when clustering keys help.

When people ask how Snowflake can scan a huge table so quickly without indexes, the answer is micro-partitions and pruning. This lesson explains both, shows how data layout affects speed, and introduces clustering.

What a micro-partition is

Snowflake automatically divides every table into contiguous units called micro-partitions. Each holds about 50 to 500 MB of uncompressed data (so typically 16 MB or so compressed). You do not define them; they are created as data is loaded, in the order the rows arrive.

Inside a micro-partition the data is columnar: each column is stored and compressed separately. If your query reads 3 columns of a 100-column table, Snowflake reads only those 3 columns' data. For analytics, that is a massive saving compared with reading whole rows.

Micro-partitions are immutable. An UPDATE or DELETE does not edit a file. It writes new micro-partitions containing the changed data and marks the old ones as no longer current (kept for Time Travel until retention ends).

A micro-partition

50-500 MB Uncompressed size
Columnar Each column separate
Immutable Never edited in place
Metadata Min, max, counts

Created automatically as rows are loaded.

1 / 2

Metadata and pruning

For each micro-partition and each column, Snowflake records the minimum and maximum value, the number of distinct values and the null count. These small statistics live in the cloud services layer.

When you run WHERE order_date = '2025-03-14', the optimizer compares that value with each partition's min and max for order_date. A partition whose range is 2024-01-01 to 2024-06-30 cannot contain the date, so it is pruned: never read. If the data was loaded roughly in date order, most partitions are pruned and the query is fast.

flowchart LR
  Q["WHERE date = 2025-03-14"] --> M["Check min/max<br/>per partition"]
  M --> P1["P1: Jan 1 - Feb 10<br/>skip"]
  M --> P2["P2: Feb 11 - Mar 25<br/>read"]
  M --> P3["P3: Mar 26 - May 05<br/>skip"]

Metadata lets the optimizer skip partitions without opening them.

You can see pruning in the Query Profile in Snowsight: it lists "Partitions scanned" versus "Partitions total". A selective query on well-ordered data scans a tiny fraction.

Simulating pruning

SQLite does not have micro-partitions, but you can model them. The stand-in below slices orders into fixed "partitions" of 4 rows each, computes min and max dates per partition, then shows which partitions a date filter would need to read:

SQL Stand-in: partitions with min and max ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

SQL Stand-in: which partitions can match a filter? ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

In a real table with millions of rows the same logic skips thousands of partitions.

Natural order and clustering

Pruning only helps if similar values sit together. Data loaded in date order is naturally clustered by date, so date filters prune well. A filter on customer_id might find that every partition contains some of every customer, so nothing can be skipped.

For very large tables (multiple terabytes) where queries filter on a column that does not match load order, you can define a clustering key. Snowflake then reorganizes data in the background so values of that column are grouped together:

SQL
-- Snowflake SQL (read-only)
ALTER TABLE shop.raw.orders CLUSTER BY (customer_id);

-- How well is the table clustered on that column?
SELECT SYSTEM$CLUSTERING_INFORMATION('shop.raw.orders', '(customer_id)');

The output is JSON with metrics such as average_depth (how many partitions overlap for a value; lower is better) and a histogram of overlaps.

Clustering has a price: automatic clustering runs in the background and consumes credits, and re-clustering a table rewrites partitions (more storage during Time Travel). Use it when:

  • the table is very large (often hundreds of GB or more),
  • queries repeatedly filter or join on the same columns,
  • the data is not already loaded in that order,
  • the table does not change too frequently.

Choose low-to-medium cardinality columns (dates, region, status). A key that has a unique value per row is expensive and pointless, though truncating it, for example CLUSTER BY (TO_DATE(ts)), helps.

Loading order matters

A cheap way to get good clustering is to control load order. Sort data by your most-filtered column on the way in:

SQL
-- Snowflake SQL (read-only)
INSERT INTO shop.raw.orders_sorted
SELECT * FROM shop.raw.orders_staging
ORDER BY order_date;

Sorting costs some time up front but can reduce every later query's scan. For smaller tables it is not worth it.

Storage costs and compression

Because storage is columnar and compressed, tables are often 3x to 10x smaller than the raw CSV. You can see storage per table:

SQL
-- Snowflake SQL (read-only)
SELECT table_name, active_bytes, time_travel_bytes, failsafe_bytes
FROM snowflake.account_usage.table_storage_metrics
WHERE table_schema = 'RAW'
ORDER BY active_bytes DESC;

time_travel_bytes shows how much old data is kept because of retention. Frequently updated tables can carry a lot of it.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. What is stored in the metadata of each micro-partition?

    This small summary lets the optimizer skip partitions that cannot satisfy a filter.
  2. 2. You UPDATE one row in a huge table. What happens to the micro-partition holding it?

    Micro-partitions are immutable. Changes create new ones.
  3. 3. When does a clustering key most likely help?

    Clustering improves pruning but costs background compute, so it suits big, frequently filtered tables.
  4. 4. Why does a columnar format speed up analytics?

    With columns stored separately, reading 3 of 100 columns touches about 3 percent of the data.

Summary

  • Tables are split automatically into immutable, columnar, compressed micro-partitions.
  • Metadata (min, max, counts) enables pruning, so queries skip partitions that cannot match.
  • Good pruning depends on how well data is clustered on the filtered column.
  • Clustering keys and ordered loads improve pruning on large tables, at a cost.
  • Query Profile shows partitions scanned versus total.

Next: Virtual Warehouses