DWHPro Insights

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

By Roland Wenzlofsky ·

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).

This article illustrates four access mechanisms. SQL requires a separate Snowflake test environment, representative data and appropriate permissions; it cannot be validated on Teradata. Partition counts, latencies and diagrams below are examples, not recorded query-profile evidence.

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.

Search Optimization Service maintains a persistent search access path for supported selective predicates. Public access-path behavior does not establish Bloom filters as its universal internal implementation; this article does not claim that algorithm.

Concurrency: row-level locks can reduce conflicts between different rows, but operations on the same rows can still block. Standard and hybrid table locking must be evaluated for the actual transaction pattern.

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 uncompressed 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';
-- Illustrative 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';
-- Illustrative 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;
-- Illustrative 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, Search Access Paths Help

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.

SOS can identify relevant micro-partitions for supported selective predicates. Treat the search access path as a documented feature, not a guaranteed Bloom-filter implementation. Measure actual pruning and service use in Query Profile.

The benefit depends on selectivity, supported predicates, data changes and maintenance cost. No internal false-positive rate is established by this article.

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.

The reduction from 12,400 candidate partitions to three is an illustration, not a reproduced result. Actual partitions scanned must be taken from a real Snowflake query profile with its data and query ID.

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

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,400
ALTER 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

For supported selective equality and IN predicates, the search access path can reduce candidate partitions; verify the actual plan and scan counts.

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,000
ALTER 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.
-- A low-selectivity predicate may leave most partitions relevant.

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

After pruning, the warehouse reads the required columns from remaining micro-partitions. The documented 50–500 MB range is uncompressed data, and exact I/O depends on selected columns, compression and cache state.

This is a two-step process: eliminate partitions (search access-path 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 index can locate qualifying rows directly, but range scans and further predicate evaluation may still occur. It is not a guarantee that every hybrid query avoids scanning.

What This Means in Practice

SOS and hybrid indexes have different access paths, but neither guarantees a fixed end-to-end latency. The 800 ms/20 ms comparison is illustrative, not a benchmark.

Hybrid tables have both row and columnar representations. Analytical performance depends on the chosen access path and workload; it is not necessarily a full row-store scan or universally slower than a standard table.

A 500 GB table is not excluded by a 2 TB database quota alone. Choose standard-table SOS or hybrid storage from current quotas, supported features, concurrency, write rate and measured costs.

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';
-- Illustrative latency, not measured: ~20ms (single-row index-based read)

A primary-key lookup can use the hybrid row store. The shown millisecond figures are illustrative and were not measured in this audit; warehouse and client/network state affect end-to-end latency.

Lookup by secondary index:

CREATE INDEX idx_user_sessions_user ON user_sessions(user_id);
SELECT session_id, last_active
FROM user_sessions
WHERE user_id = 'usr-78245';
-- Illustrative latency, not measured: ~25ms (secondary index lookup returning a handful of rows)

A secondary index offers a direct access path for qualifying predicates; its actual performance depends on selectivity, locks, compute and the workload.

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 can reduce conflicts on different rows; same-row contention remains between concurrent updates.

When Not to Use Hybrid Tables

Analytical aggregation (compare the selected access path):

-- Compare the actual plan and runtime on each supported table type: 
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;
-- Illustrative standard-table latency, not measured: 2 seconds
-- Hybrid tables also have a columnar representation; measure the selected access path.

Measure this analytical query on the target table and compute configuration. Hybrid columnar access can change the plan; no categorical latency comparison is established here.

Hybrid tables also maintain a columnar representation for analytical access. Streams on hybrid tables are not supported in the cited restrictions, so do not prescribe hybrid-to-standard synchronization through a hybrid-table stream. If a separate copy is needed, design and validate a supported synchronization method.

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 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 search access path 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.

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 measured low-latency 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.


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

Deciding what to change in your data platform?

Compare optimisation, modernisation and migration against your workload and business requirements.

Explore independent platform assessments

Practical data engineering insights, regularly

Join more than 3,500 data professionals: a regular newsletter with practical insights on data engineering, new data platforms (Snowflake, Databricks, Teradata) and AI.

Follow DWHPro in Google to see our articles more often in Search.

Get more practical Snowflake advice

Join the DWHPro newsletter for Snowflake performance tips, cost optimization ideas and data warehousing insights.

Protected by Security by CleanTalk and CleanTalk Anti-Spam