Predicate/Projection Pushdown, Zone Maps/Min-Max Statistics, Partition Pruning, and Data Skipping

Learning outcomes

1. Four ways to avoid unnecessary scan work

For AtlasMart’s “P100 GMV on 2027-01-15” benchmark query, four related mechanisms matter. Projection pushdown means reading only columns required by filters/output/aggregates. Predicate pushdown means applying filters as close to storage as the engine/format permits. Partition pruning excludes entire independently partitioned objects/partitions from a predicate such as date. Data skipping uses metadata such as per-row-group min/max statistics (often called zone-map-like statistics) to avoid chunks whose value range cannot satisfy a predicate.

These are physical optimizations; none changes the fact grain or the meaning of GMV.

2. Row group statistics are evidence, not magic

The mandatory column harness creates 18 row groups of 12,000 rows and stores min_order_date/max_order_date per group. For the date-clustered store, only one group’s range contains 2027-01-15, so 17 groups can be rejected without opening their date/product/amount chunks. The query reads 425 compressed bytes from three column chunks and examines 12,000 rows inside the surviving group.

The SQLite row-store comparison reports SCAN fact_sales for the same logical filter because the lab deliberately creates no date/product index. That plan proves the local access path only; it is not a statement that row stores cannot index selectively.

3. Controlled failure: metadata exists, therefore pruning will happen

The shuffled column store has the same 216,000 rows, the same 18 row groups, the same min/max metadata fields, and the same query result. But every shuffled group contains a broad mixture of dates, so each group’s min/max range spans the target. The harness therefore reads all 18 groups, skips zero, examines 216,000 rows in those groups, and opens 484,495 compressed bytes of the three projected columns.

4. Partition pruning and row-group skipping are not synonyms

5. Hands-on: prove layout changes skipping

import randomfrom datetime import date, timedeltaROWGROUP = 12_000# 216,000 rows across 180 days, sorted by date.days = []start = date(2026, 9, 18)for rep in range(24_000):    shift = rep % 180    for offset in (0, 0, 0, 1, 2, 2, 3, 3, 4):        days.append((start + timedelta(days=shift + offset)).toordinal())days.sort()target = date(2027, 1, 15).toordinal()def groups_read(values):    read = 0    for i in range(0, len(values), ROWGROUP):        g = values[i:i+ROWGROUP]        if min(g) <= target <= max(g):            read += 1    return readsorted_read = groups_read(days)shuffled = list(days); random.Random(18018).shuffle(shuffled)shuffled_read = groups_read(shuffled)print("sorted groups read/skipped:", sorted_read, 18-sorted_read)print("shuffled groups read/skipped:", shuffled_read, 18-shuffled_read)assert (sorted_read, shuffled_read) == (1, 18)

Expected result: clustered data reads 1 of 18 groups; shuffled data reads all 18. The values and query are unchanged. This is the controlled counterexample to “file-format metadata guarantees pruning.”

6. Boundary cases that defeat pushdown/skipping

• A predicate wraps a column in an expression the reader cannot reason about.
• Statistics are missing, stale, redacted, or too coarse.
• Every row group spans nearly the whole value domain.
• A query requests most columns, so projection savings are small.
• Encrypted data/metadata policies can limit what statistics are exposed or usable.
• A high-selectivity filter still must scan all chunks if no useful metadata/index exists.

7. Observability and reconciliation

Capture query text/hash, projected columns, partitions/groups considered/read, bytes opened, rows surviving each filter, result checksum, engine/version, cache policy, and layout version. Alerting on “bytes read” without a correctness checksum can reward an accidentally over-pruned query. Backfills must regenerate metadata along with data; stale metadata is a correctness risk in systems that trust it for elimination.

8. Production judgment and bridge

Use partitioning/clustering from Chapter 17 when it aligns with repeated filter patterns and maintenance boundaries, then verify actual pruning rather than assuming it. Keep a rollback path because layout that helps one workload may hurt another. Lesson 4 shifts from storage avoidance to CPU execution: once values are read, why do analytical engines commonly process them in typed batches?

Knowledge check

Authoritative references

• Apache Parquet — ConceptsOfficial terminology for row groups, column chunks, pages, and the units at which I/O and encoding occur.
• Apache Parquet — File FormatOfficial layout showing column chunks organized inside row groups and metadata used to locate relevant chunks.
• Apache Parquet — EncodingsOfficial definitions of plain, dictionary, run-length/bit-packed, and delta encodings. Parquet is a reference, not a prerequisite for the mandatory lab.
• DuckDB — Execution FormatCurrent official example of a vectorized analytical engine. This course uses the page only as a non-prerequisite reference for the execution concept.
• SQLite — EXPLAIN QUERY PLANOfficial documentation for the local row-store plan evidence. The plan text is diagnostic output, not a stable application API.
• Python — gzipStandard-library compression used by the dependency-free local storage harness.
• Python — structStandard-library binary packing used for fixed-width didactic column chunks.
• Python — sqlite3Standard-library SQLite interface used for the local row-oriented table and query-plan evidence.
• Kimball Group — Dimensional Modeling TechniquesBackground for keeping dimensional grain and metric semantics stable while changing physical storage and access paths.