From 12,400 Partitions to 3: How Snowflake’s Four Access Paths Actually Work

If you come from Teradata, Oracle, or SQL Server, the first thing you learn about Snowflake stops you cold: there are no indexes on standard tables. No B-trees. No secondary indexes. No hash indexes. Nothing.

For anyone who has spent years tuning queries by choosing the right index, this feels like showing up to a construction site and being told there are no power tools. Just your hands.

But Snowflake is not missing something. It replaced indexes with four different mechanisms, each designed for a specific query pattern. The problem is that most people coming from traditional databases never learn which one to use when. They either use none of them (and wonder why queries are slow) or enable the wrong one (and wonder why nothing costs explode).

Want more practical data engineering analysis like this?

Join DWHPro Letters and get field-tested notes on Teradata, Snowflake, AI, migrations, performance, and enterprise data work. DWHPro Letters is free. Subscribe to get new issues by email.

Get the next issue

This article explains each mechanism with real SQL examples, shows you how to read the Query Profile to know what is actually happening, and gives you a decision framework you can apply tomorrow.

A Quick Overview Before We Dive In

Micro-partition pruning is built in. Snowflake collects min/max metadata for every column in every micro-partition. When a query filters on a column, Snowflake skips partitions whose value range does not overlap with the filter. You do not configure it. It just works.

Clustering keys physically reorganize micro-partitions so that rows with similar values in the clustering columns are stored together. This makes pruning far more effective when natural data ordering does not align with your query patterns. Automatic Clustering maintains this in the background as data changes.

The Search Optimization Service (SOS) builds a persistent search access path based on Bloom filters. It tracks which column values might appear in each micro-partition, enabling aggressive pruning for highly selective point lookups. SOS requires Enterprise Edition or above.

Hybrid table indexes provide traditional B-tree style secondary indexes on hybrid tables, which use a row-based storage engine built for low-latency, high-concurrency OLTP workloads.

Now, let us look at each one in detail.

Micro-Partition Pruning: The Foundation You Get Without Lifting a Finger

Every micro-partition in Snowflake (50 to 500 MB of compressed columnar data) carries metadata that includes the min and max values for each column. When a query includes a WHERE clause, Snowflake checks this metadata at planning time and excludes every partition that cannot contain matching rows. Fewer partitions scanned means less data read, which means faster queries.

The effectiveness depends entirely on how well the data is physically ordered for the filtered column. Date columns on chronologically loaded tables prune beautifully, because each partition covers a narrow date range. Randomly distributed columns, prune terribly because their values are spread across nearly every partition.

Two Queries That Show the Difference

A date range filter on chronologically loaded data:

SELECT order_id, customer_id, order_total 
FROM orders 
WHERE order_date BETWEEN '2025-01-01' AND '2025-01-31';

-- Query Profile: Partitions scanned: 31 out of 3,650 total
-- Pruning ratio: 99.2%

The orders table is loaded daily, so rows for each day sit in adjacent micro-partitions. Snowflake’s min/max metadata on order_date skips everything outside January. No clustering key needed. Natural ordering does the work.

A filter on a poorly distributed column:

SELECT order_id, customer_id, order_total 
FROM orders 
WHERE region = 'EMEA';

-- Query Profile: Partitions scanned: 3,412 out of 3,650 total
-- Pruning ratio: 6.5%

The region column has only a few distinct values, and orders from all regions arrive in every daily batch. The values are spread across nearly every partition, so min/max metadata provides almost no pruning. This is your signal that something else is needed.

How to Read the Query Profile

Open the Query Profile for any executed query and click the TableScan node. Two metrics tell you everything:

Partitions scanned vs. Partitions total. A ratio close to 1:1 means pruning is failing. A low ratio (31 out of 3,650) means pruning is working, and you can stop here.

Bytes scanned. Even with good partition pruning, Snowflake reads all requested columns from the surviving partitions. A tighter SELECT list reduces the amount of data scanned within each partition.

When Pruning Alone Is Enough

If the Query Profile already shows a low scan-to-total ratio for your important queries, do not add clustering keys or SOS. If natural ordering delivers good pruning, the best action is no action.

Typical scenarios: time-series data filtered by date, event logs filtered by event_time, transactional tables loaded chronologically and queried by date range, and append-only tables where recent data is queried most often.

Clustering Keys: Fixing What Natural Ordering Cannot

When queries consistently filter on columns that are not naturally well-ordered in the micro-partitions, a clustering key tells Snowflake to reorganize the data so that similar values are stored together.

How It Works

A clustering key does not create a separate index structure. It instructs Snowflake’s Automatic Clustering service to continuously re-sort micro-partitions in the background, minimizing value overlap across partitions for the clustering columns. The result is tighter min/max ranges per partition, which makes pruning far more effective.

Automatic Clustering is a serverless feature. It runs in the background and does not require a virtual warehouse.

Before and After: The Same Query, Dramatically Different Results

Before clustering:

SELECT o.order_id, o.order_date, o.order_total 
FROM orders o 
WHERE o.region = 'EMEA' AND o.order_date BETWEEN '2025-01-01' AND '2025-03-31';

-- Query Profile (no clustering key): Partitions scanned: 892 out of 3,650
-- Pruning from order_date: good. Pruning from region: poor.

The date filter prunes effectively (natural ordering), but the region filter adds nothing because region values are spread across all surviving partitions.

After adding a clustering key:

ALTER TABLE orders CLUSTER BY (region, order_date);

-- After Automatic Clustering completes:
SELECT o.order_id, o.order_date, o.order_total
FROM orders o
WHERE o.region = 'EMEA' AND o.order_date BETWEEN '2025-01-01' AND '2025-03-31';-- Query Profile (with clustering key): Partitions scanned: 78 out of 3,650
-- Pruning ratio: 97.9%

From 892 partitions to 78. Same query, same data, same warehouse. The only difference: Snowflake reorganized the micro-partitions so rows from the same region and date range sit together. Both filter conditions now contribute to pruning.

Clustering helps analytical queries too, not just point lookups:

SELECT region, DATE_TRUNC('month', order_date) AS month, SUM(order_total) AS revenue 
FROM orders 
WHERE order_date BETWEEN '2024-01-01' AND '2024-12-31' 
GROUP BY region, DATE_TRUNC('month', order_date) 
ORDER BY region, month;

-- Query Profile: Partitions scanned: 365 out of 3,650
-- Aggregation processes ~100M rows from pruned partitions

The clustering key allows Snowflake to read only the partitions for the requested year. Within those partitions, region values are grouped, which can improve aggregation performance. Clustering is about improving the physical layout for your dominant query patterns, whether they are selective or broad.

How to Monitor Clustering Effectiveness

SELECT table_name, num_bytes_reclustered, num_rows_reclustered 
FROM SNOWFLAKE.ACCOUNT_USAGE.AUTOMATIC_CLUSTERING_HISTORY 
WHERE start_time >= DATEADD('day', -30, CURRENT_TIMESTAMP()) 
ORDER BY num_bytes_reclustered DESC;

Review this view regularly. If a table’s reclustering activity is high but its queries show minimal pruning improvement, drop the clustering key.

When to Choose Clustering (and When to Skip It)

Use clustering keys when the Query Profile shows a high partition scan ratio for queries that filter on specific columns, the table is large enough that poor pruning causes noticeable performance degradation, and the filtered columns have moderate cardinality (a column with only 2 distinct values may not benefit much; a column with millions of unique values may not cluster well either).

Avoid clustering keys on small tables (under a few hundred micro-partitions), on tables with very high data churn where continuous reclustering provides diminishing returns, or when the dominant query patterns already prune well through natural data ordering.

Search Optimization Service: Where Min/Max Pruning Fails, Bloom Filters Succeed

This is where it gets interesting. SOS solves a problem that neither micro-partition pruning nor clustering can solve well: highly selective point lookups on columns that are not the clustering key.

To understand why SOS exists, you need to understand exactly where standard pruning fails.

The Fundamental Difference Between Pruning and SOS

What micro-partition pruning knows: the range. For each column in each partition, Snowflake stores the minimum and maximum values. When you query WHERE customer_id = ‘C-00482917’, Snowflake checks each partition’s metadata and asks: could this value fall between the min and max? If a partition’s min is ‘C-00000012’ and its max is ‘C-00999841’, the answer is yes, and Snowflake must scan that partition.

When data is sorted on the filtered column, each partition covers a narrow range, and pruning works beautifully. But when the filtered column is randomly distributed (customer_id values arriving in no particular order across daily loads), nearly every partition spans a wide range. Almost all of them pass the min/max check. The Query Profile shows partitions scanned equal to the total number of partitions.

What SOS knows: which values are actually present. When you enable SOS, Snowflake builds a search access path using Bloom filters. A Bloom filter is a compact data structure that records (approximately) which specific column values appear in each partition. When the same query asks for customer_id = ‘C-00482917’, SOS does not check a range. It checks the Bloom filter and definitively answers: that value is NOT in this partition. The partition is skipped with certainty.

The trade-off: Bloom filters are probabilistic in one direction. They guarantee a value is absent, but can only say a value might be present. Occasionally, SOS flags a partition as a possible match when it is not (a false positive). In practice, false positive rates are very low, and the net effect is dramatic.

Think of It Like a Library

Standard pruning is like a librarian who checks a shelf label: “This shelf has books from A through M.” If your book starts with C, you cannot skip that shelf. You have to walk along it and check every spine.

SOS is like a librarian with a detailed catalog who tells you: “That book is definitely not on shelves 1 through 97. Check shelves 98, 99, and 100.” You still walk along those three shelves, but you skipped 97% of the library.

A hybrid table index (which we will cover next) is like a catalog card that says: “Shelf 99, position 14.” You walk straight to it.

A Concrete Example: 12,400 Partitions Down to 3

Consider a customer_events table with 12,400 micro-partitions, clustered by event_date. You query WHERE customer_id = ‘C-00482917’.

With min/max pruning alone, every partition’s customer_id range spans from near ‘C-00000001’ to near ‘C-99999999’, because customers are spread across all dates. Pruning cannot eliminate a single partition: 12,400 out of 12,400 scanned.

With SOS, the Bloom filter checks each partition and rules out 12,397 of them: the value is definitely NOT in those partitions. Snowflake scans the remaining 3. One or two might even be false positives. But the point is clear: 3 partitions instead of 12,400.

SOS is a serverless feature. The maintenance runs in the background without a virtual warehouse. SOS requires Enterprise Edition or above.

If you work with enterprise data platforms, migrations, performance tuning, or AI-driven delivery teams, DWHPro Letters is written for you. Get the next issue by email.

More SOS Query Patterns

Point lookup by a unique identifier:

-- Table: customer_events (2 billion rows, clustered by event_date)

SELECT event_id, event_type, event_date, payload
FROM customer_events
WHERE customer_id = 'C-00482917';-- Without SOS: Partitions scanned: 12,400 out of 12,400 (no pruning)
-- With SOS:    Partitions scanned: 3 out of 12,400ALTER TABLE customer_events ADD SEARCH OPTIMIZATION ON EQUALITY(customer_id);

This is the sweet spot: a high-cardinality column (millions of distinct customer IDs), used in equality predicates, on a large table already clustered by a different column.

IN list lookup:

SELECT product_id, product_name, category, price 
FROM products 
WHERE product_id IN ('SKU-10482', 'SKU-29571', 'SKU-88310');

-- With SOS on EQUALITY(product_id): Partitions scanned: 3 out of 850

Each value in the IN list is checked against the Bloom filter independently. Only partitions that might contain at least one value are scanned.

Substring search on text data:

SELECT log_id, timestamp, message 
FROM application_logs 
WHERE message LIKE '%ConnectionTimeout%';

-- With SOS on SUBSTRING(message): Partitions scanned: 45 out of 25,000ALTER TABLE application_logs ADD SEARCH OPTIMIZATION ON SUBSTRING(message);

SOS supports substring and regular expression predicates, not just equality. For text-heavy columns such as log messages, this can dramatically reduce the scan volume.

When SOS Does Not Help

Range filters (SOS provides no benefit):

SELECT order_id, customer_id, order_total 
FROM orders 
WHERE order_total BETWEEN 500 AND 1000;

-- SOS enabled on order_total: No improvement.
-- Partitions scanned: same as without SOS.

SOS is designed for equality predicates and point lookups. Range filters, BETWEEN clauses, and inequality comparisons do not benefit from SOS. For those patterns, use a clustering key.

Low-selectivity filters (SOS adds overhead for nothing):

SELECT * 
FROM orders 
WHERE status = 'COMPLETED';

-- If 80% of rows have status = 'COMPLETED', SOS cannot prune effectively.
-- The Bloom filter will match almost every partition.

SOS works best when the filter selects a tiny fraction of the table. If a filter matches most rows, most partitions contain at least one match, and SOS cannot skip them.

SOS vs. Hybrid Table Indexes: Same Problem, Completely Different Mechanics

At first glance, SOS and hybrid table indexes appear to solve the same problem. Both accelerate queries like WHERE customer_id = ‘C-00482917’. But the way they find matching rows is fundamentally different, and understanding this is essential for choosing the right one.

SOS: Narrow the Search, Then Scan

SOS reduces 12,400 partitions down to 3, but Snowflake still has to scan those 3 partitions. Each micro-partition is 50 to 500 MB of compressed columnar data. Snowflake must open those partitions, decompress the relevant columns, and scan through them row by row. The Bloom filter tells Snowflake where to look. It does not tell Snowflake where inside the partition the rows are.

This is a two-step process: eliminate partitions (Bloom filter step), then scan survivors (standard columnar read). The second step still requires a running virtual warehouse and still takes measurable time.

Hybrid Table Index: Go Directly to the Row

A hybrid table index does not scan anything. The B-tree index contains direct pointers to the exact physical location of each row in the row store. Snowflake traverses the B-tree, follows the pointer, and reads that specific row. No partition scanning, no decompression, no candidate filtering. The index knows the exact address.

What This Means in Practice

Latency. SOS delivers seconds-to-subsecond response times, depending on how many partitions survive. Hybrid table indexes deliver single-digit to low double-digit milliseconds. For an application backend serving a customer profile page, the difference between 800 milliseconds and 20 milliseconds matters.

Concurrency. SOS queries require a running virtual warehouse for the scanning step. Each query occupies warehouse resources. Hybrid tables are designed for thousands of concurrent point lookups per second, with row-level locking that prevents operations from blocking each other.

Analytical performance. This is the trade-off. SOS sits on top of the standard columnar storage engine. The underlying table remains a standard table, and analytical queries (aggregations, joins, broad scans) run at full columnar speed. Hybrid tables store data in a row-based format, which yields lower compression but is significantly slower for analytical queries. If the same table needs to serve both point lookups and analytical reports, SOS on a standard table is the better choice. If the table serves only transactional point operations, hybrid tables win.

Table size. SOS works on tables of any size, including multi-terabyte fact tables with billions of rows. Snowflake limits hybrid tables to 2 TB per database and recommends keeping individual hybrid tables small for optimal transactional performance. For a 500 GB customer_events table that occasionally needs fast lookups, SOS is the only realistic option. For a 10 GB user_sessions table serving an application backend, a hybrid table with an index is the natural choice.

Hybrid Table Indexes: When You Actually Need a Traditional Index

Hybrid tables are not an optimization layer on top of standard tables. They are a fundamentally different table type with a different storage engine and a different purpose.

How They Work

A hybrid table stores data in both a row-based format (for fast transactional access) and a columnar format (for analytical queries). It requires a primary key, supports enforced foreign key and unique constraints, and provides row-level locking. You can create secondary indexes with CREATE INDEX, which produces traditional B-tree style indexes with direct row pointers.

Hybrid tables are part of Snowflake’s Unistore architecture, designed to integrate OLTP and OLAP on a single platform.

Query Examples

Single-row lookup by primary key:

CREATE HYBRID TABLE user_sessions ( 
  session_id VARCHAR(64) PRIMARY KEY, 
  user_id VARCHAR(32), 
  created_at TIMESTAMP, 
  last_active TIMESTAMP, 
  session_data VARIANT 
);

SELECT session_data
FROM user_sessions
WHERE session_id = 'sess-a1b2c3d4';-- Latency: ~20ms (single-row index-based read)

A pure point lookup by primary key. The hybrid table’s row store serves this with sub-second latency using direct index access. A standard Snowflake table cannot match this because it must spin up a warehouse, read columnar micro-partitions, and deserialize them.

Lookup by secondary index:

sql

CREATE INDEX idx_user_sessions_user ON user_sessions(user_id);

SELECT session_id, last_active
FROM user_sessions
WHERE user_id = 'usr-78245';-- Latency: ~25ms (secondary index lookup returning a handful of rows)

The secondary index on user_id provides a B-tree access path. Snowflake traverses the index, finds matching ROW_IDs, and retrieves the rows directly from the row store. This is a deterministic lookup, not a probabilistic Bloom filter skip.

High-concurrency single-row updates:

UPDATE user_sessions 
SET last_active = CURRENT_TIMESTAMP(), session_data = :new_data 
WHERE session_id = 'sess-a1b2c3d4';

-- Thousands of these per second from an application backend.
-- Row-level locking prevents contention between concurrent updates.

This is the defining use case for hybrid tables: high-concurrency, low-latency reads and writes driven by application backends. Standard Snowflake tables use table-level locking for DML operations, making concurrent single-row updates impractical.

When Not to Use Hybrid Tables

Analytical aggregation (hybrid tables are slower):

-- DO NOT run this on a hybrid table: 
SELECT DATE_TRUNC('hour', created_at) AS hour, COUNT(*) AS sessions 
FROM user_sessions 
WHERE created_at >= DATEADD('day', -7, CURRENT_TIMESTAMP()) 
GROUP BY hour 
ORDER BY hour;

-- On a standard table: 2 seconds
-- On a hybrid table: significantly slower (row-based storage is not optimized for scans)

Analytical queries that scan and aggregate large volumes run significantly slower on hybrid tables. The row-based format achieves lower compression than columnar storage, resulting in more data to read. Hybrid tables are built for point operations, not analytical scanning.

Snowflake limits hybrid tables to 2 TB per database. For optimal transactional performance, Snowflake recommends keeping individual hybrid tables small and throughput within approximately 1,000 operations per second for a balanced workload (80% reads, 20% writes). If your table serves analytical workloads, use a standard table. If it serves transactional workloads, use a hybrid table. If it serves both, consider maintaining both, connected through streams and tasks.

The Decision Framework: A Five-Step Checklist

When a query is slow, work through these steps in order:

Step 1: Check natural pruning. Run the query, open the Query Profile, and look at partitions scanned vs. total on the TableScan node. If pruning is already effective (fewer than 10-20% of partitions scanned), stop. No further optimization needed.

Step 2: Analytical or transactional? If the query is part of an application backend performing high-concurrency, low-latency single-row operations, evaluate hybrid tables. If the query is analytical (reports, dashboards, aggregations), stay on standard tables and go to Step 3.

Step 3: Range filter or equality filter? If the poorly-pruned filter is a range (BETWEEN, >, <), a clustering key is the right choice. SOS does not help with range predicates. If the filter is an equality predicate or IN list on a high-cardinality column, go to Step 4.

Step 4: Clustering key or SOS? Consider clustering first. It benefits all queries filtering on the clustering columns, not just point lookups. Choose SOS only when the table is already clustered by a different column that serves other important queries, and you need fast point lookups on an additional column. SOS and clustering can complement each other on different columns of the same table.

Step 5: Verify the improvement. After enabling clustering or SOS, rerun the query and compare the Query Profile before and after. If the partition scan ratio has not improved meaningfully, disable the feature. For clustering, check SYSTEM$CLUSTERING_INFORMATION. For SOS, confirm the Query Profile shows SOS is being used by the optimizer.

How the Four Mechanisms Compare

Micro-partition pruning is the starting point for every query. It works on all editions, requires no setup, and is always active. It handles range queries well when data is naturally ordered, but provides limited benefit for equality lookups on randomly distributed columns. There is no DML impact because the metadata is maintained during normal data loading.

Clustering keys are the right choice when natural ordering does not align with your dominant query filters. Available on all editions. They excel at range queries and broad analytical scans, and provide moderate benefit for equality lookups. The trade-off is ongoing reclustering overhead whenever data changes. Setup: ALTER TABLE … CLUSTER BY.

The Search Optimization Service targets highly selective point lookups on high-cardinality columns that are not the clustering key. It requires Enterprise Edition or above. SOS adds a Bloom filter overlay that enables aggressive partition skipping for equality predicates and IN lists. It does not help with range queries. Like clustering, SOS requires ongoing maintenance as data changes. Setup: ALTER TABLE … ADD SEARCH OPTIMIZATION ON.

Hybrid table indexes serve a fundamentally different purpose. They provide B-tree secondary indexes on a row-based storage engine built for low-latency, high-concurrency OLTP workloads. Available in AWS and Azure commercial regions. They support both range queries and sub-millisecond equality lookups through deterministic index traversal. Index maintenance is synchronous, adding overhead to every write. Setup: CREATE HYBRID TABLE with a mandatory primary key, then CREATE INDEX.

The Bottom Line

Snowflake’s lack of traditional indexes is not a limitation. It is a design choice rooted in the separation of compute and storage, as well as in the columnar micro-partition architecture.

Start with what Snowflake gives you out of the box. Micro-partition pruning is always on and often sufficient. Add clustering or SOS only when the Query Profile proves that pruning is failing for a query that matters. And if you need sub-millisecond point lookups at high concurrency, hybrid tables exist for exactly that reason.

The goal is not to enable every feature. It is to enable only what your queries actually need.

Planning or surviving an enterprise data platform migration?

I write regularly about the performance, cost, architecture, and project mistakes that show up in real Teradata, Snowflake, Databricks, and enterprise data work.

Subscribe for free and keep launch access.

Written by Roland Wenzlofsky, founder of DWHPro and author of Teradata Query Performance Tuning. DWHPro has helped data warehouse practitioners for 15+ years.