Skip to content
Tutorials Center
My learning

Topics

Lesson 21 of 36 ยท Storage Formats and Lakehouses

Partitioning, Bucketing and Compaction

Learn Hive-style partitioning, partition pruning, how to choose partition columns, the small-files problem and how compaction fixes it, with runnable examples.

A single Parquet file is easy to reason about. A table with ten years of events is not: it might be tens of thousands of files. Partitioning is how you organize those files so a query for last week reads last week's files and ignores the rest. Compaction is the maintenance job that stops the file count from exploding.

Both topics decide whether a lake query takes two seconds or twenty minutes, and whether your cloud bill is modest or painful.

Hive-style partitioning

The classic layout puts one directory per value of the partition column:

TEXT
events/
  event_date=2026-03-01/part-0.parquet
  event_date=2026-03-02/part-0.parquet
  event_date=2026-03-02/part-1.parquet
  event_date=2026-03-03/part-0.parquet

The partition value lives in the path, not inside the files. When a query says WHERE event_date = '2026-03-02', the engine lists only that directory. This is partition pruning, and it works before any file is opened.

Python Write and prune a partitioned dataset

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

Choosing partition columns

Good partition columns have these properties:

  1. Used in most filters. Time is by far the most common (event_date), followed by a tenant or region.
  2. Low to moderate cardinality. Hundreds to a few thousand distinct values, not millions.
  3. Evenly sized partitions. If one value holds 90% of rows, that partition is a hot spot.

Typical mistakes:

  • Partitioning by user_id. A million users means a million tiny directories.
  • Partitioning by a timestamp down to the second.
  • Partitioning by several high-cardinality columns at once.
  • Partitioning a small table (a few GB) at all; one file per partition is then a few KB.

A good target is partitions of at least a few hundred MB. If daily partitions are only 20 MB, partition by month instead.

Bucketing and clustering

When the filter or join column has very high cardinality (customer id), partitioning is the wrong tool. Two alternatives:

  • Bucketing (hash partitioning): rows go into a fixed number of buckets by hash(customer_id) % N. Joins between two tables bucketed the same way avoid a shuffle.
  • Sorting / clustering (z-order, Liquid Clustering, clustered tables): rows are ordered so that min/max statistics are tight, as in the previous lesson.
Python Hash bucketing in a few lines

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

The bucket function must be deterministic and shared by every writer, which is why real engines specify a hash such as murmur3.

The small-files problem

Streaming jobs and frequent micro-batches write many small files. Each file costs:

  • one object-store request to list and open it,
  • a footer read,
  • a task or scheduling slot in the engine,
  • metadata space in the table's catalog.

A query over 100,000 files of 100 KB runs far slower than over 100 files of 100 MB holding the same bytes.

flowchart LR
  S["Streaming writer<br/>every minute"] --> A["1,440 small files / day"]
  A --> C["Compaction job"]
  C --> B["A few 256 MB files"]

Compaction

Compaction reads many small files and rewrites them as fewer, larger ones, often sorting at the same time. Table formats provide it as a command (OPTIMIZE, rewriteDataFiles, compact). The idea in plain Python:

Python Compact small files into one

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

Notice the order: write the new file before deleting the old ones. If the job crashes in between, readers still see complete data (possibly duplicated, which a table format's transaction log prevents). Table formats make the swap atomic.

Operational guidance

  • Compact recently written partitions on a schedule (hourly or daily), not the whole table.
  • Aim for target file sizes of 128 to 512 MB.
  • Avoid over-partitioning first; it is cheaper to repartition by a coarser column than to compact millions of directories.
  • Remember partition evolution: Iceberg lets you change the partition scheme without rewriting old data (covered in the lakehouse lesson).
  • Delete files no longer referenced (vacuum/expire snapshots) after a retention period.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. What is partition pruning?

    Because the partition value is in the path, non-matching directories are never listed or opened.
  2. 2. Which is the worst partition column for a table with 5 million users?

    Millions of partitions create millions of tiny files and directories.
  3. 3. Why is a job that writes a file every minute a problem for readers?

    The small-files problem makes listing and opening files dominate query time.
  4. 4. In a safe compaction job, what is the correct order?

    Writing first means a crash never leaves the table without its data; table formats make the swap atomic.

Summary

  • Partition by columns used in filters, with moderate cardinality and large enough partitions.
  • Use bucketing or clustering for high-cardinality keys.
  • Many small files slow everything; compact to 128-512 MB files on a schedule.
  • Write new data before removing old, or use a table format's atomic commit.

Next: Lakehouse Table Formats