Partitioning and File Layout

Suppose ten years of trade data for thousands of symbols sits in one enormous file. A query for "just AAPL, just March 2023" still has to open that one giant file and scan through it, because nothing on disk tells the query engine where AAPL's March 2023 rows are without reading everything else first. Partitioning fixes this by physically splitting the dataset into many smaller files organized by a key — commonly date, sometimes symbol, sometimes both — so a query that filters on that key can skip entire files it doesn't need, never reading their bytes at all. This is often called "partition pruning," and it's frequently the single biggest lever on query speed for a large dataset, well before any change to the query itself.

The layout choice — which key to partition by, and in what order — has to match how the data is actually queried, because a partition scheme only helps the queries that filter on the same key it's organized around. A dataset partitioned by date-then-symbol (folders like 2023-03-01/AAPL.parquet) makes "give me every symbol on this one date" cheap, since it's one folder, but makes "give me AAPL's full history" expensive, since it has to touch one file inside every date folder across ten years. Flipping the layout to symbol-then-date reverses which query is cheap and which is expensive. There is rarely a layout that's good for every query pattern; the choice is a bet on which access pattern matters most.

Partition granularity is a separate trade-off from partition key. Too coarse (one file per year) and a query for one day still reads a huge file. Too fine (one file per minute) and the filesystem or object store ends up with an unwieldy number of tiny files, each carrying fixed overhead — opening a file, reading its metadata — that adds up and can make small-file overhead dominate actual data-reading time. Daily partitioning is a common middle ground for tick-level data because it roughly matches how backtests are usually run: day by day, symbol by symbol.

Worked example: pruning in action

A dataset is partitioned by date, one file per trading day, ten years of history — about 2,500 files. A query asks for one week of AAPL data. With date partitioning, the query engine reads the partition metadata, identifies the five files that match "date within this week," and opens only those five, ignoring the other roughly 2,495 files entirely. Without partitioning — one giant file for the whole ten years — the same query would have to scan the entire file, even though it only needs a tiny slice of it, because there's no structural signal on disk about where that one week's data lives.

What this means in practice

Choose a partition key that matches the dominant query pattern of the pipeline — usually date for backtests that iterate chronologically, sometimes symbol for research that studies one instrument's full history. Getting this wrong doesn't cause incorrect results, only slow ones, which makes it an easy mistake to leave unfixed for a long time: the pipeline works, it's just needlessly scanning far more data than it has to on every run.