In a columnar engine, what is a zone map and how does it let a query skip data?

## The problem pruning solves An analytical query over billions of rows is normally limited by how many bytes it has to pull off storage and decompress, not by CPU spent on the rows that match. The cheapest read is the one that never happens. So every columnar engine invests in metadata whose only job is to *prove that a chunk of data cannot contain any row this query wants* — and then not read it. This is why an OLAP engine can answer a filtered query over a 50 TB table in seconds: it touched 50 GB. ## What a zone map is A columnar engine does not store a column as one continuous stream. It cuts the table into **blocks** — anywhere from tens of thousands to a few million rows, called row groups, stripes, micro-partitions, parts or granules depending on the engine — and stores each column of each block as its own contiguous, compressed run of bytes. Next to those bytes it records a compact summary *per column per block*: - minimum value - maximum value - row count - null count - sometimes a distinct-value estimate or a truncated prefix for strings That summary is the **zone map** (also called min/max statistics, block statistics, or a block-range index). It is kilobytes of metadata describing hundreds of megabytes of data, it is written automatically when the block is written, and there is nothing for a user to create, tune or rebuild. It lives in the file footer, in a manifest, or in a metadata service the engine consults before opening any data file. ## How the engine uses it When a query carries a filter on a column, the planner or the scan operator evaluates the predicate against the interval `[min, max]` of each block: ```sql WHERE event_time >= DATE '2026-08-01' ``` A block whose `event_time` max is `2026-07-14` cannot hold a matching row, so the engine skips it — and skips it for *every* column, not just the filtered one. A block whose range straddles the boundary must be read and the rows checked individually. The test is deliberately conservative. It has **no false negatives** (a block that could match is never skipped, so pruning can never change the answer) but plenty of **false positives**: a block whose range covers the value may still contain zero matching rows, and reading it is wasted I/O. Min and max describe the *envelope* of the block, never which values are actually inside it. Null counts are useful in their own right: a block with `null_count = row_count` can be skipped for any `IS NOT NULL` predicate, and a block with `null_count = 0` for any `IS NULL` predicate. ## The same idea at three granularities Pruning happens at several scales and interview answers should separate them: 1. **Partition elimination** — whole directories or key ranges dropped from the plan because the partition value cannot match. Coarsest and cheapest. 2. **File elimination** — files dropped using file-level min/max in the table metadata, before any file is opened. 3. **Block skipping** — blocks inside a surviving file dropped using their zone maps, before their column chunks are fetched. All three are the same mechanism — compare a predicate to a bound, skip what cannot match — applied at different sizes. ## What decides how much you actually skip Physical ordering, and nothing else. If rows are stored sorted, or nearly sorted, by the filtered column, block ranges are narrow and barely overlap, and a selective filter touches a handful of blocks. If the column's values are scattered randomly across the table, every block's `[min, max]` spans nearly the whole domain, every block survives the test, and the zone map buys you nothing at all. Sort keys, cluster keys and ingest ordering exist precisely to shape this metadata. ## Where pruning quietly fails - **The column is wrapped in a function.** Some engines can invert simple monotonic expressions, many cannot; do not rely on it. Compare the raw column to a constant when you can. - **Infix pattern matching** such as a `LIKE` with a leading wildcard: no interval test applies. - **A single outlier per block.** One sentinel value like `9999-12-31` or a placeholder id widens that block's range to the whole domain and the block always survives. - **Predicates on a joined dimension** rather than the fact table: the fact scan has no constant to test until the engine builds a runtime filter from the dimension. - **`OR` across uncorrelated columns**, which forces the union of two weak prunes. - **Long strings**, where formats may truncate the stored bounds and lose precision. ## How you verify it Every serious engine reports, in its query profile or job statistics, how many partitions/files/blocks were scanned out of the total, and how many bytes were read. That ratio is the number to look at — not the wall-clock time — because in a warehouse that bills per byte scanned, the ratio *is* the invoice.