Micro-Partitions

Snowflake · Storage & Optimization

Theory

Snowflake stores every table as micro-partitions: contiguous, immutable units of storage, each holding roughly 50 to 500 MB of uncompressed data, stored in a compressed columnar format. Rows are grouped into partitions in the order they are loaded. For every partition, Snowflake records metadata such as the range (min and max) of values in each column and the number of distinct values.

The optimizer uses this metadata to prune: for WHERE order_date = '2024-03-15', any partition whose order_date range does not include that date is skipped without being read. Because partitions are immutable, an UPDATE writes new partitions and the old ones are kept for Time Travel and Fail-safe.

Why we use it

Pruning means a query on a 10 TB table may read only a small fraction of it. Snowflake does this automatically, so you do not have to design and maintain partitions by hand as in traditional databases.

When to use it

Think about micro-partitions when queries filter on large tables: filter on columns that correlate with load order (like dates), avoid wrapping filter columns in functions where possible, and check pruning in Query Profile.

How Data Gets Divided

Every table is automatically and invisibly divided into micro-partitions — contiguous, immutable, compressed chunks of roughly 50–500MB uncompressed — with no manual partitioning required.

The Metadata That Powers Pruning

For each micro-partition, Snowflake stores metadata like the min/max value per column, letting the optimizer skip (prune) partitions that can't match a query's filter without scanning them.

Why This Replaces Manual Partitioning

Traditional databases require you to design and maintain partitions by hand; Snowflake's automatic micro-partitioning removes that maintenance burden while still delivering comparable pruning performance.

How it works

  1. Query with WHERE order_date = '2024-03-15'
  2. Optimizer reads min/max metadata for every partition
  3. Does the partition's date range include the value? Yes: Keep it for scanning. No: Prune it, never read.
  4. Warehouse scans only the kept partitions and needed columns
  5. Return result; Query Profile shows partitions scanned vs total

Example

-- Managed automatically — no manual partitioning required

Key points

Snowflake topics

Snowflake Fundamentals

Warehouses & Performance

Staging & SnowSQL

Tables & Data Handling

Cloud Integrations

Storage & Optimization

Security & Governance

Views, Pipelines & Automation

Data Types & SQL Fundamentals

Semi-Structured Data Deep Dive

Advanced Features

Data Sharing & Collaboration

Cost Management & Monitoring

Databases, Schemas & Objects

Performance Deep Dive

Advanced Security & Governance

Stored Procedures & UDFs

Ecosystem & Integrations

SnowPro Certification Prep

T
Tech Town
Log InSign Up Free
Home SQL Python DSA Power BI Snowflake Databricks Spark Gen AI Practice Problems Contest Blog

Mastering Snowflake

The modern data cloud, from architecture to SnowPro certification. Click any topic in the sidebar to open it right here.

{{ sectionCount }}
Sections
{{ topicCount }}
Topics
Free
Forever
Hands-on practice Practice Snowflake SQL Sharpen the SQL you use in Snowflake: 150 query problems in a live editor with instant checking. Start practising →
Section {{ sec.num }}

{{ sec.label }}

Snowflake  /  {{ current.sectionLabel }}  /  {{ current.name }}

{{ current.name }}

Theory

{{ current.def }}

Why we use it

{{ rich.why }}

When to use it

{{ rich.when }}

{{ sec.h }}

{{ sec.body }}

{{ sec.body }}

Diagram

{{ rich.dg.caption }}

{{ tb.title }}
{{ c.v }}
{{ rich.dg.op }}
➜
{{ rich.dg.output.title }}
{{ c.v }}
{{ nd.label }}
{{ nd.sub }}
{{ nd.label }}
{{ nd.sub }}
{{ rich.dg.left.title }}
{{ it }}
{{ rich.dg.right.title }}
{{ it }}
On screen — where to click

{{ rich.ui.title }}

{{ u.text }}
  1. {{ st.n }}{{ st.text }}
{{ rich.ui.title }}
{{ u.text }}
Flowchart — how it works
{{ nd.text }}
{{ nd.text }}
◇ {{ nd.text }}
Yes → {{ nd.yes }}
No → {{ nd.no }}
Syntax
{{ current.syntax }}
Example
{{ current.example }}
Want to run this yourself? Unlike the SQL and Python courses, Snowflake needs a real account — there's no in-browser engine for it. Click Copy above, then paste it into a free Snowflake trial (30 days, no card required) and run it in Snowsight.
Key Points — click to check off as you learn
  • {{ pt.mark }} {{ pt.text }}

Share this topic