skip to content

Why can a Snowflake query filter a huge table fast when Snowflake has no indexes?

level: juniorimportance: must knowfreq 72%

answer

  1. no CREATE INDEX in Snowflake
  2. tables are split up automatically
  3. each chunk carries per-column metadata
  4. min/max ranges decide what is read
  5. Query Profile: partitions scanned vs total

basics

~20 s

Snowflake splits every table into immutable micro-partitions and keeps per-column min/max metadata for each one. A filter is compared against that metadata, so only micro-partitions whose value ranges could match are read. Skipping the rest replaces indexes.

solid answer

~50 s

Snowflake has no `CREATE INDEX`. Instead, every table is automatically stored as many **micro-partitions** — immutable, compressed, columnar files, each documented as holding roughly 50–500 MB of uncompressed data. As each one is written, Snowflake records per-column metadata about it: minimum and maximum values, distinct-value counts, null counts. That metadata lives in the cloud services layer, so when a query says `WHERE order_date = '2026-08-01'`, the optimizer compares the constant against every micro-partition's min/max for `order_date` and skips any file whose range cannot contain it. Nothing is fetched or decompressed for skipped files. That is **partition pruning**, and it is Snowflake's main lever for reading less. Columnar layout adds a second axis: selecting three columns reads only those columns' blocks. You can see the result in Query Profile, where the TableScan operator reports partitions scanned versus partitions total.

code

sql · 9 lines
sql
-- prunes: predicate compares directly against stored column values
select sum(amount)
from orders
where order_date >= '2026-08-01' and order_date < '2026-08-02';

-- prunes poorly: the filtered column is scattered across every micro-partition
select sum(amount)
from orders
where customer_id = 918273;

go deeper

for a junior

Be ready to say that Snowflake has no user-created indexes, that tables are automatically split into micro-partitions, and that per-column min/max metadata lets the engine skip files that cannot match your filter.

for a middle

Explain where the metadata lives, why it is consulted before any storage I/O, and why a filter on a randomly scattered column prunes nothing even though the metadata exists. Know that column projection is a separate saving.

for a senior

Show that you verify rather than assume: read partitions scanned versus partitions total in Query Profile, and mine QUERY_HISTORY for the queries whose ratio is worst before proposing any tuning.

for a principal

Frame pruning as the main cost lever on a consumption-priced platform: bytes scanned drives warehouse time drives credits, so layout decisions and query hygiene are budget decisions, not micro-optimizations.

## The mechanism Snowflake uses instead of indexes In a classic relational database you speed up a selective filter by building a secondary index: a separate structure that maps values to row locations. Snowflake exposes no such thing — there is no `CREATE INDEX` statement for a standard table. Its only way to make a query cheaper is to **read less of the table**, and that ability is built into the storage format itself rather than bolted on afterwards. ## Micro-partitions Every Snowflake table is physically stored as a large number of **micro-partitions**. A micro-partition is a contiguous group of rows written as a single immutable file, laid out in columnar fashion and compressed per column. Snowflake creates them automatically as data arrives — you never declare them, there is no partitioning clause in `CREATE TABLE`, and you cannot set their size. Snowflake documents each as containing roughly 50 MB to 500 MB of *uncompressed* data; the stored footprint is smaller because everything is compressed. A multi-terabyte table therefore consists of many thousands of micro-partitions. Because they are immutable, they are also written in the order the data arrived. A table loaded once a day from an event stream ends up with micro-partitions that are naturally close to sorted on event time, without anyone asking for it. This "natural clustering" is why a date filter on an append-only table often prunes well with no tuning at all. ## The metadata that makes skipping possible When Snowflake writes a micro-partition, it records metadata about it in the cloud services layer: for each column, the range of values (minimum and maximum), the number of distinct values, the number of NULLs, and other properties. This metadata is small, lives outside the data files, and is consulted by the optimizer before any storage I/O happens. ## How a filter turns into skipped files Given `SELECT sum(amount) FROM orders WHERE order_date = '2026-08-01'`, the optimizer walks the micro-partition metadata for `orders` and tests each one: could a row with `order_date = '2026-08-01'` live in a file whose `order_date` ranges from `2025-02-11` to `2025-02-12`? No — skip it. The file is never downloaded from cloud storage, never decompressed, never scanned. Only micro-partitions whose recorded range overlaps the predicate are read, and within those, only the columns the query mentions. This is the whole reason a well-shaped Snowflake query on a 5 TB table can finish on an X-Small warehouse: it may only have touched 20 GB. ## What pruning cannot do Pruning is a property of **physical data layout**, not of the predicate alone. If the filtered column's values are scattered randomly across the table — for example a `customer_id` in a table loaded by date — then almost every micro-partition's min/max range spans nearly the whole domain, every range overlaps the predicate, and nothing is pruned. The metadata is still there; it simply cannot exclude anything. Two other everyday cases defeat it. Wrapping the column in a function or cast (`WHERE to_date(event_ts) = ...`) means the predicate no longer compares directly against the values whose min/max were recorded, so the comparison usually cannot be made. And a small table made of a handful of micro-partitions has nothing worth skipping — pruning matters at scale. ## Measuring it Don't guess. In the Snowflake UI, open **Query Profile** and look at the TableScan operator: it reports *Partitions scanned* and *Partitions total*. A query that scanned 5,400 of 5,400 pruned nothing; one that scanned 12 of 5,400 pruned almost everything. The same figures are available historically as the `partitions_scanned` and `partitions_total` columns of `SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY`, which is how you find the expensive dashboards without watching them run. ## Practical consequences Three habits follow. Filter on the columns your data is physically organised by — usually the load-time or event-time column. Write predicates against the bare column so the metadata comparison is possible. And select only the columns you need, since column projection compounds with pruning: half the files and a tenth of the columns is a twentyfold reduction in bytes scanned, which on Snowflake is directly a reduction in warehouse time and therefore credits. When the column you filter on is *not* the one the data is organised by, and the query matters enough, that is when you reach for a clustering key — a declared ordering Snowflake maintains for you so pruning works on a column that load order would not have given you.

  • Where do you actually see how many micro-partitions a Snowflake query read?
    In Query Profile, the TableScan operator shows *Partitions scanned* against *Partitions total* — the ratio is your pruning score. For historical or bulk analysis, `SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY` carries `partitions_scanned` and `partitions_total` per query, so you can rank the worst-pruning statements of the last month without re-running them.
  • Does wrapping the filtered column in a function stop Snowflake from pruning?
    Usually yes. The metadata holds min/max of the stored column values, so a predicate like `WHERE to_date(event_ts) = '2026-08-01'` compares a computed result rather than those values and the optimizer generally cannot use the ranges. Write `event_ts >= '2026-08-01' AND event_ts < '2026-08-02'` instead — same rows, and the comparison is directly against recorded ranges.
  • If a table's rows are loaded in random order, does the metadata still help?
    Barely. The metadata is recorded regardless, but if values are scattered every micro-partition's min/max spans nearly the whole domain, so every range overlaps the predicate and nothing can be excluded. Pruning is a property of physical layout, not of the predicate — which is exactly the gap a clustering key or a sorted load is meant to close.

It is a warehouse of sealed crates where each crate's label lists the range of dates inside. To find August 1st orders you read labels, not contents, and open only the crates whose range covers that day.

saying these in an interview costs you the question

  • Says you should create an index on the filtered column in Snowflake
  • Thinks micro-partitions are declared in DDL like range partitions
  • Claims pruning works on any predicate regardless of data layout
  • Assumes SELECT * costs the same as selecting three columns
  • Confuses result caching with reading fewer micro-partitions

context

resolving links…