Partitioning
Putting a column's value into the directory path so a reader can exclude data without opening it. The cheapest skip available, and the only one that costs nothing to evaluate.
Who needs this, what one row is, and why the obvious build breaks
Every lesson starts from the consumer, because designing from the source outward is this domain's characteristic mistake.
Why does writing date=2026-08-24 into the directory name make a query cheaper than storing exactly the same date as a column inside the files?
Anyone whose query carries a time bound, which is very nearly every analytical query ever written. They express it as WHERE order_date = ... and expect the platform to do something intelligent with it; partitioning is the only mechanism that lets the platform act on that predicate before touching a single object.
One partition — the set of all rows sharing one value (or one tuple of values) of the partition column, materialised as a directory and the files under it. The partition, not the row, is the unit of pruning, of retention and of deletion.
Write every file for a table into one directory and let the query filter. The date is in the data, the engine can read it, and adding directories feels like reintroducing folders to a system that was supposed to be a table.
A query for one day must open every file in the table to discover which of them contain that day, because the only place the date is recorded is inside the files. The predicate is applied after reading, which is not a saving (Sequential Scan, Page by Page).
- A query for one day must open every file in the table to discover which of them contain that day, because the only place the date is recorded is inside the files. The predicate is applied after reading, which is not a saving (Sequential Scan, Page by Page).
- File-level statistics could help, but only if the data happens to be arranged by date — and if the writer appends in arrival order with any lateness, every file spans a wide date range and no file can be excluded (Clustering and Sort Order).
- Retention becomes a full table rewrite. Deleting everything older than a stated period means reading every file, filtering, and writing the survivors back, rather than removing directories (Data Retention).
- A backfill of one day cannot be isolated. Reprocessing that day means rewriting files that also contain other days, so the blast radius of a correction is the whole table (What Backfills Break).
- Incremental processing has nothing to grip. "Process what is new" degenerates into "process everything and compare", because there is no physical boundary corresponding to a time range (Incremental Processing).
What is actually happening
- Hive-style partitioning encodes the column name and value into the path:
events/date=2026-08-24/part-0000.parquet. The value is therefore known from the key alone, before any object is opened, and usually before any object is even listed if a catalog or manifest holds the partition list (Files, Paths and Names). - The planner rewrites a predicate on the partition column into a filter over that list of paths.
WHERE date = '2026-08-25'becomes "consider only the directory whose encoded value equals2026-08-25", and every other directory is eliminated with a string comparison rather than a read (Partition Pruning). - The partition column is usually not stored inside the files at all — it is redundant, since the path determines it, and formats that hoist it out save the bytes. This is why the column is sometimes called virtual and why some engines expose it with slightly different typing than a real column (Partition Pruning).
- Because the value is a path segment, it is a string at the storage layer. Everything about type coercion, zero-padding, time zones and null encoding follows from that, and so does most of the surprise in the next lesson.
- Partitioning is also the unit of atomic-ish operations. Publishing a day, deleting a day, and reprocessing a day are all directory-level operations, which is what makes partitioned tables operationally tractable in a way unpartitioned ones are not (Atomic Publish).
- The same primitive exists inside relational databases as declarative table partitioning, with the same benefit and the same cardinality trap. The difference is that a database's partitions are catalog entries and a lake's are directories, so the lake pays listing cost and the database pays planning cost (Partitioning and Sharding, Partitioning Internals: Key → Partition Function → Node).
The value goes in the path
The whole idea fits in one directory listing. The partition column's value is written into the key as column=value, so the value is knowable from the name of the object, and a reader can decide whether a directory can possibly contain matching rows without issuing a single read against it.
That is the difference between a column and a partition, and it is entirely physical. In both cases the date is recorded. In one case it is recorded somewhere you must open the file to find; in the other it is recorded somewhere the storage layer already tells you for free.
The layout below shows what a query for a single day actually touches. Four directories exist, one is read, three are eliminated by string comparison against their names. Nothing else about the table changed — same rows, same schema, same format, same total size.
The saving is not proportional to how much data a partition contains. It is proportional to how many partitions were excluded, which is why this scales so well along a time axis: after four years, a query for one day excludes well over a thousand directories at the cost of a thousand comparisons.
- events/date=2026-08-22/one day of events · 3 files · skipped
- events/date=2026-08-23/one day of events · 3 files · skipped
- events/date=2026-08-24/one day of events · 4 files · skipped
- events/date=2026-08-25/one day of events · 3 files · read
Compare with an unpartitioned table holding the same rows: all thirteen files are opened, their footers read, and the date predicate applied to data that has already been fetched. Same answer, and the work is the whole table instead of one directory.
events/
date=2026-08-22/
part-0000.parquet
date=2026-08-23/
part-0000.parquet
date=2026-08-24/
part-0000.parquet
date=2026-08-25/
part-0000.parquet
the value 2026-08-25 is knowable from the key.
no object needs to be opened to rule the others out.Declaring it, and what the engine stores
The declaration is unremarkable and the consequence is not. Naming a column as the partition key tells the writer to route each row to a directory by its value, and tells the reader that the value is derivable from the path. In many engines it also means the column is not written into the files, because storing it would be storing the same value once per row when one path segment already says it.
That omission is the source of a family of small surprises: the partition column may come back typed as a string, may not accept the same functions as a real column, and may behave differently in a SELECT * than the schema suggests. None of it is a bug; all of it follows from the value living in a path.
The second statement below is the one worth internalising. Both queries return the same answer. One of them can be answered by considering a single directory; the other cannot be answered without opening every file in the table, because event_ts is a real column and its value is only knowable from the data. The predicate looks equally reasonable in both cases, and it is not.
1-- The partition column is written into the path, not into the files.2CREATE TABLE events (3 event_id STRING,4 customer_id STRING,5 event_ts TIMESTAMP, -- a real column, stored in every file6 amount DECIMAL(12,2),7 event_date DATE -- the partition column, stored in the path8)9PARTITIONED BY (event_date);10 11-- Written as: events/event_date=2026-08-25/part-0000.parquet12 13-- (1) Prunes. The planner compares the literal against the partition list14-- and considers exactly one directory.15SELECT sum(amount)16FROM events17WHERE event_date = DATE '2026-08-25';18 19-- (2) Does not prune. event_ts is inside the files, so every file must be20-- opened to find out whether it holds rows in this range. Identical21-- result, entirely different amount of work.22SELECT sum(amount)23FROM events24WHERE event_ts >= TIMESTAMP '2026-08-25 00:00:00'25 AND event_ts < TIMESTAMP '2026-08-26 00:00:00';26 27-- (3) Prunes, and is what the second query should have been: bound the28-- partition first, then refine inside it.29SELECT sum(amount)30FROM events31WHERE event_date = DATE '2026-08-25'32 AND event_ts < TIMESTAMP '2026-08-25 12:00:00';Queries (1) and (2) are semantically equivalent on well-formed data and are not equivalent physically. The habit worth building is to always bound the partition column explicitly, even when another column already implies the same range — the planner does not know that event_ts and event_date are related unless you say so.
Partitioning is not indexing
The most common conceptual error in this area is treating a partition as an index. They are both "things that make queries read less", which is where the similarity ends, and the difference decides which problems each can solve.
An index is a separate structure that maps values to row locations. It supports selective lookups on high-cardinality columns — find this one customer among millions — and it costs storage and write-time maintenance. A partition is not a structure at all; it is a physical grouping, and the only thing it can do is exclude entire groups.
That difference is why the cardinality advice is the exact opposite for each. An index is most valuable on a high-cardinality column, because selectivity is what makes a lookup cheap. A partition is most valuable on a low-cardinality column, because every distinct value costs a directory. Applying index intuition to partitioning produces exactly the partition by user_id disaster, and it is a reasonable-sounding mistake rather than a careless one (Partition Cardinality).
The practical consequence: if the problem is "find one row by id", partitioning is not the tool and the workload probably belongs in a system with indexes (OLTP vs OLAP). If the problem is "read one day out of four years", partitioning is exactly the tool and no index would help, because the predicate is not selective in the way an index needs — it selects a contiguous quarter of a percent of the table, and reading that range sequentially is what you want (An Index Scan Is Not Automatically Faster).
Partition `events` by `customer_id` so that a query for one customer reads only that customer's directory. Each customer gets a directory; a query for one customer touches one path.
Partition `events` by `event_date` and sort each partition by `customer_id`. A query for one customer within a time range prunes to the relevant days, then skips files and row groups whose recorded `customer_id` range excludes the value.
A partition costs a directory and at least one file per distinct value, so partitioning on a column with millions of values produces millions of objects holding a handful of rows each — the metadata to find them exceeds the data inside them, and every query, including the customer lookup, becomes slower. Sorting achieves the same exclusion through file statistics at a cost of one write-time sort and zero additional objects. The general rule: partition on the low-cardinality axis everyone filters on, and express high-cardinality selectivity through sort order (Clustering and Sort Order).
| Property | Index | Partition |
|---|---|---|
| What it is | A separate structure mapping values to locations | A physical grouping of rows; no separate structure |
| Best cardinality | High — selectivity is the point | Low — every distinct value costs a directory |
| Access it enables | Find a small number of specific rows | Exclude entire groups of rows |
| Cost of maintaining it | Storage plus work on every write | No extra storage; a routing decision at write time |
| What it does for a scan | Little — a scan of most of the table ignores it | Everything — it decides how much of the table the scan covers |
| Cost of getting it wrong | A structure nobody uses, and slower writes | A metadata explosion that slows every query on the table |
How to build it
Most important first.
- Partition on time, in almost every case, at the coarsest granularity that still prunes usefully. Date is the predicate nearly every analytical query carries and the axis along which data is retained, backfilled and reprocessed (The Partitioning Decision).
- Choose the granularity from the volume per partition, not from the query. Hourly partitions on a table producing a small amount of data per hour create the small-files problem faster than they create pruning (Partition Cardinality).
- Prefer one partition column. Each additional dimension multiplies partition count, and the second dimension is almost always better served by sort order inside the partition (Clustering and Sort Order).
- Partition on event time, not ingestion time, if consumers reason about when things happened — otherwise a query for yesterday reads the partition where yesterday's late records did not land (Event Time, Late-Arriving Data).
- Register partitions with whatever metadata layer the engine uses, and keep that registration current. A partition that exists on storage and not in the catalog is invisible; one that exists in the catalog and not on storage is an error waiting for a query (Metadata: Technical, Operational and Business).
- Decide explicitly what happens to rows whose partition value is null or unparseable — the default in most engines is a single catch-all partition that grows forever without anyone choosing it — and consider writing the partition column into the files as well, since some engines type the path-derived value as a string and having the real typed column removes a class of surprise.
What this actually promises
Naming the guarantee you do not have is worth more than naming the one you do — everything downstream inherits the weakest promise in the chain.
- Partitioning guarantees physical separation: rows with different partition values are in different directories, always, with no exception for late or corrected data. That is the property retention, deletion and per-day reprocessing all rest on.
- It does not guarantee that a query filtering on the partition column will prune. Pruning is a planner behaviour that depends on the predicate's form, and the next lesson is entirely about the cases where it silently does not happen (Partition Pruning).
- It does not guarantee that a partition is complete. A directory for a date exists as soon as one row for that date is written, and nothing distinguishes "this day is finished" from "this day has three rows so far" (The High-Water Mark).
- It guarantees nothing about ordering within or across partitions. Files inside a partition are in whatever order the writer produced them.
- The partition value is derived from the path, so it is exactly as trustworthy as the writer that chose the path. A row written into the wrong directory is, from the reader's point of view, simply a row with a different date (Data Tests).
Can I trust it?
A green pipeline is evidence that code ran. These four fields are the evidence that the data is right.
- The check that matters: row count per partition against the same partition's history and against the source. A partition that exists but is far smaller than its neighbours is a partial load; one that is missing entirely is a failed run that some other monitoring called green (Volume Anomalies).
- A second check for event-time partitioning: count rows whose event date does not match the partition they are in. This should be zero by construction, and when it is not, the writer is mis-routing data and every per-day metric is quietly wrong.
- What both miss is a partition that is complete and wrong — every row present, every value bad. They also miss the catch-all null partition unless you look for it by name, and that is where mis-parsed timestamps accumulate silently.
- Partitioning changes what "fresh" can mean by making it addressable: a consumer can ask whether today's partition exists and how much is in it, which is a far more useful question than a table-level timestamp (Freshness Monitoring).
- A time-partitioned table has an inherently ragged edge. The most recent partition is open and incomplete by definition, and any consumer aggregating it is aggregating a partial day. Publishing a completeness marker per partition is what turns that from a trap into a contract (Atomic Publish).
- Partitioning by event time trades freshness for correctness: late records land in old partitions, so a partition that looked complete yesterday can gain rows today, and any downstream aggregate of it must be recomputable (Late-Arriving Data).
- The partition column is part of the table's public shape. Consumers write predicates against it, tools display it, and changing it changes how every existing query performs — this is the one part of physical layout that is not purely physical (Data Contracts).
- Changing the partition key requires rewriting the data. There is no in-place migration: until history is rewritten, the table is physically two tables and queries behave differently depending on which period they touch.
- Changing granularity — daily to hourly, or the reverse — has the same cost and the additional hazard that both layouts can coexist under one prefix, producing paths that some engines interpret and others reject.
- Some table formats can evolve the partition specification without rewriting history, applying the new spec to new data only. That removes the rewrite and does not remove the fact that old and new data prune differently (Open Table Formats).
- Partitioning is what makes recovery bounded. A backfill of one day rewrites one directory; without partitions, the same correction rewrites everything, which is the difference between a routine operation and one that requires a maintenance window (Planning a Backfill).
- Rewrite a partition by writing to a new location and swapping, never by deleting first. A partition that is briefly empty is a partition that some scheduled report will read as a very quiet day (Atomic Publish).
- When a writer has been routing rows into the wrong partitions, the repair is a rewrite of every affected partition, not just the one that looks wrong — rows went somewhere, and that somewhere is also incorrect.
What can go wrong
- A catch-all partition for null or unparseable values that nobody knows exists, silently accumulating every record whose timestamp failed to parse.
- Partitions present on storage but absent from the catalog, so queries return an answer that is confidently missing days (Metadata: Technical, Operational and Business).
- Ingestion-time partitioning presented to consumers as if it were event time, so every per-day metric is shifted by the ingestion delay and reconciles perfectly against itself.
- A partition granularity chosen for pruning that produces partitions too small to fill a file, converting a scan problem into a metadata problem (File Size and the Small-Files Problem).
- A time-zone mismatch between the writer's partition value and the consumer's expectation, which moves a boundary's worth of rows into the neighbouring day and is invisible in every aggregate except a daily one.
- "Partition by every commonly-queried column." This is the single most damaging piece of folklore in this module. Each additional partition column multiplies the partition count and divides the data per file, so a table partitioned on four dimensions has a directory tree larger than its data and prunes worse than one partitioned on date alone (Partition Cardinality).
- "Partitioning is indexing." An index is a separate structure that maps values to locations and supports selective lookups on high-cardinality columns. A partition is a physical grouping with no separate structure and no ability to find a single row. Reaching for partitioning to solve a point-lookup problem produces the worst of both (Why Is This Query Slow? Indexes).
- "The column is in the data, so the engine can use it." It can — after reading it. Pruning requires the value to be knowable *without* reading, which is exactly what putting it in the path achieves.
- "More partitions means more pruning." Only until planning cost overtakes scan saving. Past that point additional partitions strictly increase the work done before any data is read.
- "We partitioned by date, so date queries are fast." Partitioning makes pruning *possible*. Whether it happens depends on the predicate's form, and it silently does not in several common cases (Partition Pruning).
- Partition values are in paths, and paths appear in listings, access logs and error messages. Partitioning by a customer identifier or any personal attribute publishes that attribute to everyone who can list the prefix, independently of object-level permissions (PII in Pipelines).
- Time partitioning is what makes retention enforceable at reasonable cost — expiring a period becomes a directory removal with an obvious, auditable boundary, rather than a rewrite whose completeness nobody can prove (Data Retention).
Operating it
- Partition count per table, and the rate at which it grows. A table gaining partitions faster than it gains time periods is partitioned on something other than time (Partition Cardinality).
- Rows and bytes per partition as a distribution, not an average. The average hides both the empty partitions and the one holding a disproportionate share (Data Skew).
- Existence and size of the catch-all null partition, checked explicitly by name, because it will not appear in any dashboard that groups by a valid value.
- The fraction of partitions a typical query prunes, sampled from query history. This is the read-side proof that the partitioning is doing its job (Partition Pruning).
- At 10x volume with the same partition key, everything works and partitions get bigger — which is the behaviour you want, and is why time is such a good key: its cardinality grows with the calendar rather than with the business.
- At 100x, partition granularity may need to become finer, and the constraint becomes the partition count the metadata layer can plan over rather than the data volume itself.
- Cardinality is the axis that breaks first. A key whose distinct values grow with users, products or sessions produces a partition count that scales with the business, and that is the failure the next-but-one lesson is about (Partition Cardinality).
- Partitioning's entire value is bytes not scanned, and it is the largest single lever available on that driver because it eliminates data without opening it (Scan Cost).
- It costs metadata: one directory and at least one file per partition, listed and tracked forever. That cost scales with partition count and is completely independent of data volume.
- It costs write-time redistribution. Writing a partitioned table means routing rows by partition value, which for a distributed writer is a shuffle it would not otherwise perform (The Shuffle).
- It reduces retention cost dramatically, because expiring old data becomes a directory drop rather than a rewrite (Storage Lifecycle).
- Partitioning helps exactly the predicates it was chosen for and is neutral to every other query, which are then paying the metadata cost with none of the benefit. There is no partition key that is good for all access patterns (Every Optimization Buys Something and Sells Something).
- It commits you to a physical arrangement that is expensive to change, at the moment when you know least about how the table will be queried.
- Finer granularity buys better pruning and costs more partitions and smaller files. That trade has a floor set by file size, and crossing it converts an improvement into a regression (File Size and the Small-Files Problem).
Where this applies
Almost nothing here is universal. These labels say what each claim is specific to, and where a different engine, format, warehouse or scale would differ.
- GENERALEncoding a value in the physical location so it can be excluded without reading is universal, and appears as directory partitioning in lakes, declarative partitioning in relational databases, and internal micro-partition metadata in warehouses. What differs is who chooses the key and whether you can see the result.
- ENGINE-SPECIFICEngines differ in whether they discover partitions by listing storage or by reading a catalog or manifest, and in how they type a path-derived value. The same table can prune from a manifest in one engine and require a recursive listing in another, which changes planning cost by more than the scan saving in extreme cases.
- WAREHOUSE-SPECIFICSome warehouses partition automatically on ingestion and expose clustering rather than partitions, so the explicit key choice here does not exist — the equivalent decision is made through table options and the physical result is not directly observable, which removes both the mistake and the control.
- SIMPLIFIEDThe layout examples show one file per partition for legibility. A real partition holds several files, and the interaction between partition granularity and file size is the constraint that actually decides the granularity.
Where the depth lives
This domain teaches how data moves and how you know it arrived intact. It hands the rest off by name.
- — DevOps / Production Engineering owns the delivery side of a partition-key change: it is a data migration with a rollback plan, not a configuration edit, and it deserves the same treatment as a schema migration in a service.