What is partition pruning, concretely?

Skipping entire files (or file segments) that cannot possibly match a query's filter, based on how the data is physically laid out — one file per month, one per country, etc. — instead of reading everything and filtering afterward. The saving is proportional to how selective the filter is against the partition scheme: filtering to 1 of 12 months on data partitioned by month skips roughly 11/12 of the bytes.

Why is column pruning's speedup so much larger than partition pruning's in this lesson?

Parquet is a columnar format, so reading one column out of ten already skips most of the file's bytes on disk — but the bigger cost here is materializing 200,000 rows of a wide text column into Python objects, which the single-column aggregate never has to do at all. Both are real costs; wide, high-cardinality text columns you don't need are often the single most expensive thing to prune.

Does this mean every table should be partitioned as finely as possible?

No — over-partitioning creates many small files, and small files carry their own per-file overhead (opening, reading metadata, planning) that can outweigh the pruning benefit. A reasonable partition granularity matches the filters queries actually use most often; partitioning by month is useless if every query filters by customer_id instead.

How does this relate to the file-formats lesson's Parquet-vs-CSV comparison?

That lesson measures the cost of the file format itself — Parquet's columnar layout and compression versus CSV's row-oriented text. This lesson measures the cost of how files are *organized on disk* — partitioned vs. not, and how much of a file's columns a given query actually needs. Both matter, and both are usually combined: partitioned Parquet is the common real-world default.

Cost Optimization: Measuring What Partition and Column Pruning Actually Save

Write twelve months of order data as separate Parquet files, then measure — not assume — what partition pruning and column pruning actually save.

Twelve monthly files, ~200,000 rows each

import duckdb, os, time

con = duckdb.connect()
for month in range(1, 13):
    con.execute(f"""
        copy (
            select row_number() over () as order_id, {month} as month,
                   (random() * 1000)::int as customer_id, (random() * 500)::decimal(10,2) as amount,
                   repeat('x', 200) as notes, 'GB' as country, 'web' as channel,
                   'completed' as status, current_date as order_date, random() as misc
            from range(200000)
        ) to 'orders_by_month/month_{month:02d}.parquet' (format parquet)
    """)

sizes = {f: os.path.getsize(f"orders_by_month/{f}") for f in os.listdir("orders_by_month")}
total_bytes = sum(sizes.values())
print(f"12 monthly files, {total_bytes / 1_000_000:.1f} MB total, "
      f"{sizes['month_03.parquet'] / 1_000_000:.1f} MB for March alone")

$ python cost_optimization.py
12 monthly files, 42.2 MB total, 3.5 MB for March alone

Partition pruning: reading one file vs. filtering after reading twelve

t0 = time.perf_counter()
result = con.execute("""
    select count(*), sum(amount) from read_parquet('orders_by_month/*.parquet') where month = 3
""").fetchone()
t_no_pruning = time.perf_counter() - t0
print(f"  scanned all 12 files, filtered in-memory: {result[0]} rows, {t_no_pruning*1000:.1f}ms")

t0 = time.perf_counter()
result = con.execute("""
    select count(*), sum(amount) from read_parquet('orders_by_month/month_03.parquet')
""").fetchone()
t_pruned = time.perf_counter() - t0
print(f"  scanned only month_03.parquet directly: {result[0]} rows, {t_pruned*1000:.1f}ms")
print(f"  speedup: {t_no_pruning / t_pruned:.1f}x, bytes read: "
      f"{total_bytes/1_000_000:.1f}MB vs {sizes['month_03.parquet']/1_000_000:.1f}MB")

== partition pruning: scanning all 12 files for one month's data ==
  scanned all 12 files, filtered in-memory: 200000 rows, 8.0ms
  scanned only month_03.parquet directly: 200000 rows, 2.6ms
  speedup: 3.0x, bytes read: 42.2MB vs 3.5MB

Same 200,000 rows out, three times faster, because 3.5MB was read from disk instead of 42.2MB. The where month = 3 filter on read_parquet('*.parquet') still had to open and scan every one of the 12 files before discarding 11/12 of the rows — reading the one matching file directly skips that work entirely. This is exactly what naming a file (or a Hive-style partition directory) by the value a query filters on buys: the filter becomes “which files do I even open,” not “which rows do I discard after reading.”

Column pruning: one column vs. all ten

t0 = time.perf_counter()
con.execute("select sum(amount) from read_parquet('orders_by_month/month_03.parquet')").fetchone()
t_one_col = time.perf_counter() - t0

t0 = time.perf_counter()
con.execute("select * from read_parquet('orders_by_month/month_03.parquet')").fetchall()
t_all_cols = time.perf_counter() - t0
print(f"  select sum(amount) only: {t_one_col*1000:.1f}ms")
print(f"  select * (10 columns):   {t_all_cols*1000:.1f}ms")
print(f"  column pruning speedup: {t_all_cols / t_one_col:.1f}x")

== column pruning: one column vs all ten ==
  select sum(amount) only: 2.1ms
  select * (10 columns):   474.7ms
  column pruning speedup: 230.1x

The gap here is much larger than partition pruning’s — Parquet’s columnar layout means sum(amount) only ever touches the amount column’s bytes on disk, skipping the wide 200-character notes text column entirely, and it never has to materialize 200,000 Python row objects the way fetchall() on all ten columns does. Wide, rarely-queried text or JSON columns are frequently the single most expensive thing sitting in an otherwise-cheap table.

Practice

1. Measure the same partition-pruning comparison for a 6-month range (`where month between 1 and 6`) instead of one month.

  scanned all 12 files, filtered in-memory: 1200000 rows, 10.0ms
  scanned only 6 matching files: 1200000 rows, 7.8ms
  speedup: 1.3x

A wider filter range prunes proportionally less (skipping 6/12 files instead of 11/12), so the speedup shrinks — partition pruning’s payoff scales with how selective the filter actually is.

2. Select two columns instead of one (`sum(amount), count(distinct customer_id)`), and compare against the single-column time.

two columns (amount, customer_id): 5.1ms

Still close to the single-column time and nowhere near the 474.7ms of select * — both amount and customer_id are small, low-cardinality-relative columns; the expensive one was always the wide notes text column, not “more than one column” in general.

3. Why is `bytes read` a more reliable cost signal than query duration in a real cloud warehouse?

Many warehouses (BigQuery, Snowflake, Athena) bill primarily on bytes scanned, not query wall-clock time — a query can be fast because of caching or a warm warehouse and still scan (and cost) the same bytes as a slow first run. Bytes-scanned is the number that shows up on the invoice; duration is a proxy that caching and concurrency can decouple from actual cost.

4. What would over-partitioning look like for this dataset, and why would it hurt?

Partitioning by order_id (200,000+ tiny single-row files instead of 12) — every query would now pay the fixed per-file overhead (open, read metadata, plan) 200,000 times over, which for files this small would dwarf any pruning benefit. Partition granularity should match how selective real queries’ filters actually are, not be maximized for its own sake.

Next: CI/CD for data — testing and deploying pipeline code itself through environments, not just checking a schema contract.

Twelve monthly files, ~200,000 rows each

Partition pruning: reading one file vs. filtering after reading twelve

Column pruning: one column vs. all ten

Practice

Frequently Asked Questions