Partitioning Parquet files without regret

What partitioning buys you

A single Parquet file is fast to scan when you read only the columns you need. A directory of thousands of them is a different story. Before the engine reads any data, it has to list the files, open their footers, and decide which ones could contain matching rows. Partitioning moves much of that decision into the directory layout. If the path contains event_date=2026-10-11, a reader can skip every other folder without opening anything inside it.

Partitions are a physical choice, though, and you live with it. Every distinct value becomes a directory, and every directory holds files. Too many directories or too few rows per file costs more than having no partitions at all.

Partition by what your filters use

Choose columns that appear in WHERE clauses in most of your queries. Event date is the usual answer because analytical questions tend to ask about a time window. A country or region column makes sense when most consumers filter on it. A column that shows up in filters only now and then is a poor choice, since every query that skips the filter reads every directory.

Keep the number of partition values low. A few hundred to a few thousand is comfortable for most object stores and query engines. A column with millions of distinct values produces millions of directories, each holding a tiny file, and that leads directly to the next problem.

The small-files problem

Each file costs a request to open, a footer to parse, and some metadata held in memory. Fifty thousand files of 2 MB each will query more slowly than 200 files of 250 MB each, even though the byte count is similar. A useful target is somewhere between 128 MB and 512 MB per file. The lower end suits wide tables and memory-constrained readers. The upper end suits scan-heavy workloads, but larger files make retries and rewrites more expensive.

Streaming jobs and small batch runs produce small files by default, so plan for a compaction step. Compaction rewrites a closed partition into fewer, larger files. Run it once the partition stops receiving data, make it idempotent, and swap the new files in so readers never see a half-written partition.

Hive-style directories and pruning

Hive-style layout names directories as key=value. Most engines understand this convention and can recover the partition columns from the path. When a query filters on event_date, the engine compares the predicate to the directory names and drops the folders that cannot match. That is partition pruning, and it happens before any Parquet footer is read. Directory names become part of your contract, too. Renaming a partition column breaks every query that depends on it, so pick the names once.

Writing this layout with pandas takes one extra argument:

import pandas as pd

events = pd.read_parquet("staging/events_2026_10_11.parquet")
events.to_parquet(
    "warehouse/events",
    engine="pyarrow",
    partition_cols=["event_date", "country"],
)

Reading it back with DuckDB shows the effect. The filter below only needs the event_date=2026-10-11 directories:

SELECT country, sum(amount) AS total
FROM read_parquet('warehouse/events/**/*.parquet', hive_partitioning = true)
WHERE event_date = DATE '2026-10-11'
GROUP BY country;

Run the query under EXPLAIN ANALYZE. The scan node should report only files under the matching date. If it reports the whole dataset, check that the filter compares the partition column directly and does not wrap it in a function, because a wrapped column gives the engine nothing to match against the directory names.

Why partitioning by user_id is a mistake

It is tempting, because user-level questions are common. But a platform with ten million users creates ten million directories. Most of those directories hold a few kilobytes, so the file count explodes and every query pays for opening them. A query for one user still has to list the whole tree, and a query across all users reads everything anyway, now with far more overhead.

Use sort order within files instead. If you sort rows by user_id before writing, each Parquet row group carries min and max statistics for that column, and the reader can skip row groups that cannot contain the user you want. Partition by time, sort by the high-cardinality key, and let the statistics do the rest.

Late data reopens partitions

Partitioning by date means late-arriving data rewrites a partition you thought was closed. Decide how long a partition stays open, and make the rewrite path the same as the normal write path. Keeping that window explicit is cheaper than discovering a month later that three partitions were quietly missing rows.