📦 Micro-Partitions, 🧠 Metadata Layer & ⚡ Multi-Level Caching in Snowflake
How Snowflake stores, locates, and retrieves data efficiently.
In this chapter, you'll discover why Snowflake can scan petabytes of data in seconds without using traditional indexes or manual partitions.
Every query executed in Snowflake follows the same architectural flow:
📦 Micro-Partitions → Physically store the table data in a compressed columnar format.
🧠 Metadata Layer → Maintains information about every micro-partition, enabling Snowflake to instantly locate the required data and skip unnecessary files.
⚡ Multi-Level Caching → Reduces latency by reusing metadata, query results, and previously accessed micro-partitions across three caching layers.
Together, these components form the core of Snowflake's Storage Engine and Query Processing Architecture, allowing it to execute analytical queries efficiently without relying on traditional indexes or manually managed partitions.
📦 1. What is a Micro-Partition?
A micro-partition is the physical storage unit of Snowflake tables. Unlike traditional databases that write data to large, continuous tablespaces or files, Snowflake automatically splits every table into thousands of small, contiguous, non-overlapping physical files.
⚙️ Core Technical Characteristics:
- Size: Each micro-partition contains between 50 MB and 500 MB of uncompressed data (typically compressed down to 16 MB in cloud storage).
- Immutability: Micro-partitions are 100% immutable (write-once). When data is modified (via
INSERT,UPDATE, orDELETE), Snowflake never updates the existing micro-partition. Instead, it writes brand new micro-partitions containing the changes and updates the metadata to point to the active set of files. This design is what powers Time Travel and Zero-Copy Cloning. - Cloud Storage Placement: They are stored as encrypted, compressed files in raw cloud object storage (AWS S3, Azure Blob, or Google Cloud Storage) under the covers.
📂 What is Row-Oriented vs. Columnar Storage (Inside a Micro-Partition)
Unlike traditional operational databases (which write data row-by-row), Snowflake organizes the data within each micro-partition in a columnar format.
Row-Oriented Layout (Traditional Database)
┌───────────────────────────────┐
│ Row 1: ID, Name, Date, Sales │
├───────────────────────────────┤
│ Row 2: ID, Name, Date, Sales │
├───────────────────────────────┤
│ Row 3: ID, Name, Date, Sales │
└───────────────────────────────┘
To calculate:
SELECT SUM(SALES)
the database reads every row, even though only the SALES value is required.
Snowflake Columnar Layout (Inside One Micro-Partition)
ID Column
---------
1
2
3
NAME Column
-----------
Alice
Bob
John
DATE Column
-----------
2026-06-01
2026-06-02
2026-06-03
SALES Column
------------
150
200
300
For:
SELECT SUM(SALES)
Snowflake only reads the SALES column.
ID, NAME and DATE columns are completely skipped.
💡🤔 Student Thinking Corner
"Wait... Oracle tables also have rows and columns. Isn't Oracle columnar too?"
This is probably the first question every student asks.
When we create a table in Oracle...
CREATE TABLE CUSTOMER
(
ID NUMBER,
NAME VARCHAR2(100),
SALES NUMBER,
SALES_DATE DATE
);
...it looks exactly the same as creating a table in Snowflake.
So naturally, you may think:
🤔 "Both databases have rows and columns. So why is Snowflake called a Columnar Database?"
The answer is:
Rows and Columns describe the logical design of a table.
Row Store and Column Store describe how data is physically stored on disk.
These are two completely different concepts.
Oracle (Row Store)
Oracle stores an entire row together.
Disk Block
-----------------------------------------
Row 1
ID = 1
NAME = Alice
DATE = 2026-06-01
SALES = 150
-----------------------------------------
-----------------------------------------
Row 2
ID = 2
NAME = Bob
DATE = 2026-06-02
SALES = 200
-----------------------------------------
When Oracle executes:
SELECT SUM(SALES)
FROM CUSTOMER;
it reads each stored row.
Although the query only needs SALES, Oracle reads the row because the row is the physical storage unit.
Snowflake (Column Store)
Inside every Micro-Partition, Snowflake stores each column separately.
Micro-Partition
ID
--
1
2
NAME
----
Alice
Bob
DATE
----
2026-06-01
2026-06-02
SALES
-----
150
200
Now the same query...
SELECT SUM(SALES)
FROM CUSTOMER;
reads only
SALES
150
200
The ID, NAME, and DATE columns are never read.
This significantly reduces disk I/O, improves compression, and speeds up analytical queries.
🎯 Think of it like a Filing Cabinet
Oracle
Each customer has one folder.
Folder 1
---------
ID
Name
Date
Sales
Folder 2
---------
ID
Name
Date
Sales
Need everyone's Sales?
👉 You must open every folder.
Snowflake
Each attribute has its own drawer.
Drawer 1 → All IDs
Drawer 2 → All Names
Drawer 3 → All Dates
Drawer 4 → All Sales
Need Sales?
👉 Open only the Sales drawer.
No need to touch the other drawers.
⭐ Golden Rule
The SQL syntax is almost identical in Oracle and Snowflake.
CREATE TABLE CUSTOMER(...)
looks the same in both systems.
However...
✅ Oracle stores rows physically together.
✅ Snowflake stores columns physically together inside each Micro-Partition.
The SQL is the same.
The storage engine is completely different.
🔍 What Exactly Does a Micro-Partition Store?
Internally, each micro-partition file is packed with structured data and indexing blocks:
-
Header Metadata
- Offsets and pointers identifying where each column begins inside the micro-partition.
-
Columnar Data Chunks
- Separate compressed arrays for each column (e.g., all
Customer_IDvalues together, allSales_Amountvalues together).
- Separate compressed arrays for each column (e.g., all
-
Compression Structures
- Column-specific compression techniques such as Dictionary Encoding, Run-Length Encoding (RLE), Delta Encoding, and others, chosen automatically based on the data characteristics.
-
Statistical Metadata
- Min/Max values
- NULL counts
- Distinct value estimates
- Additional metadata used for partition pruning.
🧠 2. The Metadata Layer – The Brain of Snowflake
The Metadata Layer (residing inside the Cloud Services layer) is the catalog that coordinates all query processing. Rather than scanning files to find out what is inside them, Snowflake queries the metadata layer first.
⚡ Partition Pruning: Horizontal & Vertical
Pruning is the mechanism by which Snowflake optimizes queries to read the absolute minimum amount of data possible.
- Horizontal Pruning (File Skipping): When you run a query with a filter (e.g.,
WHERE sales_date = '2026-06-25'), the optimizer checks the min/max statistics of thesales_datecolumn in the metadata catalog for all micro-partitions. It instantly ignores any files where'2026-06-25'falls outside their min/max range. - Vertical Pruning (Column Skipping): Because data is stored column-by-column, if your query only does
SELECT sales_amount FROM orders, Snowflake only reads the byte ranges containing thesales_amountcolumn data and ignores the rest of the file.
[!NOTE] Since pruning is completely metadata-driven, Snowflake can answer queries like
SELECT MIN(sales_amount), MAX(sales_amount) FROM ordersinstantly without starting up or consuming any Virtual Warehouse compute credits!
⚡ 3. How Caching Supports Micro-Partitions
To avoid repeatedly pulling micro-partitions over the network from cloud storage, Snowflake implements three distinct caching layers:
| Cache Layer | What It Stores | Location | Life Cycle / Invalidation |
|---|---|---|---|
| 1. Metadata Cache | Table structure, micro-partition file paths, column statistics (min/max values, null counts). | Cloud Services Layer | Always active; updated automatically whenever data changes. |
| 2. Result Cache | The exact final output of a completed query. | Cloud Services Layer | Persists for 24 hours. Any modification to the underlying table data invalidates this cache. |
| 3. Virtual Warehouse Cache (Local SSD Cache) | The raw, decrypted micro-partition files scanned by active queries. | Compute Nodes (RAM & Local SSD) | Persists as long as the Virtual Warehouse is running. It is wiped out when the warehouse suspends/stops. |
🔄 4. Step-by-Step Query Flow: Putting It Together
Imagine you execute:
SELECT customer_name, SUM(sales_amount)
FROM orders
WHERE sales_date = '2026-06-25';
Here is exactly how Snowflake processes it:
🚀 Path A: The Fast Path (Result Cache Hit)
If this exact query was executed in the last 24 hours and the underlying table data hasn't changed, Snowflake skips compute and storage entirely.
🐢 Path B: The Full Path (Result Cache Miss & Query Execution)
When a query is not cached, Snowflake performs the full query execution workflow across the Metadata, Compute, and Storage layers.
🏁 Summary: Why Micro-Partitions are the Core Engine
Micro-partitions are not just a way of organizing data on disk. They are the underlying technology that powers:
- No Partition Management: No need to define partition keys manually. Snowflake continuously clusters and partitions data dynamically.
- Zero-Copy Cloning: Creating a copy of a 10 TB table takes seconds because Snowflake only copies the metadata pointers to the original micro-partitions.
- Time Travel: Historical queries read metadata configurations from past states to scan the micro-partitions that were active at that specific timestamp.