What a Partition Is, and Why Pruning Makes a Big Table Cheap

What a partition actually is

Partitioning splits one logical table into many folders, one per value of a partition key, and puts each folder's rows in its own files. The classic key is a date. On a lake the layout is literally directories named key=value:

s3://lake/events/
  dt=2026-01-01/   part-0.parquet  part-1.parquet
  dt=2026-01-02/   part-0.parquet  part-1.parquet
  dt=2026-01-03/   part-0.parquet  part-1.parquet  part-2.parquet

The partition value lives in the path, not inside every row. A query engine reads the folder names from the catalog, so it knows which folders hold which dates before it opens a single file.

Partition pruning: the whole point

When a query filters on the partition key, the engine reads only the matching folders and skips the rest. This is partition pruning. A WHERE dt = '2026-01-03' over a year of daily partitions opens 1 folder out of 365 and ignores the other 364. Because serverless engines charge by bytes scanned (Amazon Athena bills about 5 dollars per terabyte scanned), reading one partition instead of the whole table is not just faster, it is directly cheaper.

Table

A filter on the partition key reads only the matching folder. The engine skips the rest without opening them.

partition	size	WHERE dt = '2026-01-03'
dt=2026-01-01	520 MB	skip
dt=2026-01-02	540 MB	skip
dt=2026-01-03	610 MB	read
dt=2026-01-04	500 MB	skip

Why you partition a large table

Four reasons, in order of how often they come up:

1. Scan less data. Prune to the partitions a query needs and read a fraction of the table. This is the big one.
2. Match the common filter. Almost every query on an events table filters by date, so partitioning by date makes almost every query cheap.
3. Manage the lifecycle. Dropping or expiring old data is a whole-folder operation: delete the dt=2025-01-01/ partition, with no row-by-row delete.
4. Parallelism. Independent partitions are natural units of parallel work.

Measuring pruning with SQL

A partition catalog records, per partition, its size and row count, so you can compute exactly how many bytes a pruning query scans versus a full scan. The exercises query a partition_catalog and compute the savings from filtering to one day and to a date range.

Common mistake: partitioning a table but never filtering on the partition key. Pruning only happens when a query filters on the key, so a partition scheme your queries ignore adds file overhead with no payoff.

Interview nuance: asked "why would you partition this table," lead with pruning and cost: partitioning by the column you filter on most (usually date) lets the engine skip the partitions a query does not need, so it scans fewer bytes, runs faster, and on a pay-per-scan engine costs less.

On a real platform this differs. A real engine prunes by reading partition metadata from the catalog (Glue) and never lists the skipped folders. The partition_catalog here exposes one row per partition so you can compute the pruned scan yourself; the arithmetic (sum the matching partitions, compare to the total) is what the engine's cost estimate does.