Physical Data Layout
Two datasets with identical rows and identical schemas can differ by an order of magnitude in what a query must read. The difference is which rows share a file and which files share a directory.
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.
The rows are the same, the schema is the same and the query is the same — so why does one copy of this dataset cost far more to query than the other?
Every analytical consumer, none of whom can see any of this. An analyst writing WHERE order_date = CURRENT_DATE - 1, a dashboard refreshing on a schedule, a training job reading a year of history, an ad-hoc query someone runs during an incident. They all express *what* they want; layout decides *how much has to be opened* to give it to them.
The unit of this module is not the row. It is the file — and, one level down, the row group inside it, and one level up, the partition directory that contains it. Layout is the discipline of deciding which rows share a file and which files share a directory, because those two decisions are what a reader can use to skip.
Write the data out however the job happens to produce it. One file per task, one directory per table, no sort order, whatever compression the writer defaults to. The engine is described as fast and the data is described as small, so layout looks like premature optimisation — and for the first few months, on a dataset that fits in one scan, it genuinely is.
The dataset crosses the point where a full scan no longer finishes inside the schedule. Nothing changed except accumulated history, and now the nightly model that reads "the last day" is reading four years to find it (Incremental Processing).
- The dataset crosses the point where a full scan no longer finishes inside the schedule. Nothing changed except accumulated history, and now the nightly model that reads "the last day" is reading four years to find it (Incremental Processing).
- A streaming writer commits every few minutes, so the table gains files faster than it gains meaningful data. Query planning starts taking longer than query execution, and no chart of bytes explains why (File Size and the Small-Files Problem).
- Someone adds
WHERE date = ...expecting it to help. It does not, becausedateis a column inside the files rather than a directory in the path, so every file must still be opened to find out whether it contains that date (Partitioning). - The table is partitioned, the filter is on the partition column, and the query still reads everything — because the predicate is wrapped in a function the planner cannot see through (Partition Pruning).
- A well-meaning engineer partitions by
user_idto "make user queries fast". The write job now produces a directory per user, the catalog holds millions of partition entries, and every operation that lists the table becomes the slowest thing in the platform (Partition Cardinality). - A join that should have been local becomes a full shuffle, because the two sides of it were written with no relationship to each other and the engine has no choice but to redistribute both (The Shuffle).
What is actually happening
- An analytical query is dominated by what it did not have to read. Columnar formats already let a reader skip columns it did not select; layout is the set of decisions that let it skip *rows* — not by filtering them after reading, but by never opening the bytes at all (Row vs Column Storage).
- Skipping happens at four nested levels, each decided by a different piece of metadata. Directory pruning skips whole partitions using the path. File skipping uses per-file statistics — typically column minimum and maximum — recorded when the file was written. Row-group skipping uses the same statistics at a finer granularity inside a file. Column projection skips column chunks entirely (The Parquet Read Path).
- Every one of those skips is a proof, not a guess: the reader skips a file only when the recorded statistics make it impossible for a matching row to be inside. That is why layout never changes an answer, and why inaccurate statistics are a correctness bug rather than a performance one.
- The statistics are only as useful as the data's arrangement. A minimum and maximum on a column whose values are scattered randomly across every file covers nearly the whole domain in every file, so no file can be excluded. Sorting concentrates the range each file covers, which is the entire reason clustering exists (Clustering and Sort Order).
- On object storage there is a fifth cost that has nothing to do with bytes: per-request overhead. Listing a prefix, opening a footer and issuing a ranged read are all requests, and their cost is driven by how many objects exist rather than how large they are (Object Storage as Data Infrastructure).
- Layout also decides *parallelism*. A file is usually the smallest unit of work an engine hands to a task, so file boundaries decide how work is divided — and a partition that holds most of the data becomes the task that decides the job's runtime (Data Skew, Straggler Tasks).
What a reader is allowed to skip, and where each skip is decided
The mental model that makes the whole module coherent is that a query engine is not trying to read your data. It is trying to prove, as cheaply as possible, that it does not have to. Each level of the hierarchy below is a separate proof, using a separate piece of metadata, and each one can be defeated independently.
The proofs are strictly ordered, and the earlier ones are worth far more. Pruning a directory costs one comparison against a path and eliminates everything under it. Skipping a row group costs a footer read and eliminates a slice of one file. If the first proof fails, all the later ones still run — over everything.
This ordering is why a table with no partitions but excellent sorting still opens every file. The reader must read a footer to learn it can skip a file, and reading footers of ten thousand files is itself the expense. Directory pruning is the only level that avoids touching the object at all.
The physical units, and what confusing them costs
People discuss layout in units — "the partition", "the file", "the row group" — and mean different things by them. Being precise here is not pedantry: each unit is the granularity of a different operation, and choosing the wrong unit for an operation is the mechanism behind most layout mistakes.
Read the table below as a set of granularities. A partition is the unit of pruning and of deletion. A file is the unit of parallelism and of listing. A row group is the unit of statistics and of decoding. A column chunk is the unit of projection. Nothing else in this module makes sense until those four are separate in your head.
The breaksIf column is where the real teaching is. Almost every layout pathology in the rest of the module is one of these four confusions carried out at scale.
| Stage | One row is | Breaks if |
|---|---|---|
| Partition directory | Every row sharing one value of the partition column — in the usual case, one day of data. | It is treated as a filter rather than a physical grouping. A partition is not free: it is at minimum one directory and one file, so a partition holding a handful of rows costs more to find than to read. |
| File (object) | A self-describing chunk of rows: schema, data, and a footer of statistics, readable independently of every other file. | Files are made small enough that per-file overhead — a list entry, a footer read, a scheduled task — dominates the work of reading the rows inside (File Size and the Small-Files Problem). |
| Row group | A horizontal slice of one file, holding all columns for a contiguous run of rows, with its own per-column statistics. | The rows inside it are unsorted, so the statistics for every row group span the full value range and no row group can ever be excluded (Parquet Internals). |
| Column chunk | One column's values for one row group, encoded and compressed as a unit. | The query selects columns it does not use — SELECT * in an analytical query defeats projection entirely, which is the cheapest skip available (Projection Pushdown). |
| Page | The smallest independently decodable unit inside a column chunk. | You reason about it at all in most platform work. It matters for encoding and for the format lessons, and almost never for a partitioning decision (Dictionary, Run-Length, Delta and Bit Packing). |
Four granularities, four different operations. Deletion works on partitions, parallelism on files, statistics on row groups, projection on column chunks — and a technique aimed at the wrong granularity does nothing.
Four dials, and what actually moves the number
Layout has four dials — file size, partitioning, sort order, and encoding/compression — and they are not equally powerful. Ranking them honestly is more useful than describing them all, because engineers reliably reach for the last one first: codecs are easy to change and require no rewrite of anyone's assumptions.
The relative weights below are a teaching ordering rather than a measurement. What transfers is the ordering and the reason for it: bytes you never read cost nothing to decode, so any lever that eliminates reads outranks every lever that makes reads cheaper. Compression is a multiplier on the bytes that survive pruning; pruning decides how many bytes there are to multiply.
The ordering inverts in one specific case, which is worth holding onto: on a badly fragmented table, request and metadata overhead can dominate everything else, and no amount of partitioning or sorting helps until file size is fixed. That is why file size is the first thing to correct and partitioning is the second (File Compaction).
- File size — decides parallelism and per-request overhead. Helps every query regardless of predicate, which makes it the only universal lever here.
- Partitioning — decides what can be skipped without opening anything. Helps exactly the predicates it was chosen for and nothing else (Partitioning).
- Sort order / clustering — decides how selective file and row-group statistics are. A second axis of skipping that costs write-time sorting rather than metadata explosion (Clustering and Sort Order).
- Encoding and compression — decides the cost of the bytes that survive. Composes with everything above and substitutes for none of it (Why Analytical Data Compresses).
The dominant term in a healthy table. Moved by partitioning and by sort order, because both decide what the reader can prove it does not need.
Invisible in any byte-based view and driven by file count, not data volume. This is the term that dominates a fragmented table and the reason small files are a pathology rather than an inefficiency.
Decided by whether the layout co-locates the join key. Bucketing exists to move this one and moves nothing else (Bucketing).
Compaction and re-sorting read and write the same rows again. It scales with how often you maintain and how much you rewrite each time, and an over-eager schedule can exceed the scan cost it removes.
Where the compression codec and the encoding scheme act. Real, and last on the list, because it applies only to data that pruning already failed to eliminate.
Relative weights, not measurements. The ordering is what transfers between platforms; the magnitudes do not, and this domain never publishes a price.
Relative weights for a typical partitioned columnar table, shown to establish an ordering — not measurements and not transferable to any specific workload. Fix them in order: file size, then partitioning, then sort order, then encoding.
How to build it
Most important first.
- Start from the predicates. Write down the three or four
WHEREclauses that most queries actually carry — almost always a time bound plus one or two dimensions — because those are the only predicates layout can help with (The Partitioning Decision). - Get file size into a sane range before anything else. It is the cheapest fix, it helps every query rather than one predicate, and it is the failure that most reliably makes a platform feel broken (File Compaction).
- Partition on a low-cardinality column that appears in nearly every filter, and normally only one — usually a date. Partitioning is a coarse instrument and adding a second dimension multiplies the partition count (Partition Cardinality).
- Sort within the partition on the next most common filter column. Sorting is what makes file-level statistics selective, and it costs a write-time sort rather than a directory explosion (Clustering and Sort Order).
- Reach for bucketing only when a specific recurring join is the problem and the shuffle it causes is the dominant cost. It is the least flexible technique here and it constrains both sides of the join (Bucketing).
- Make layout maintenance a scheduled, owned job rather than a heroic intervention, and measure the layout rather than the query. Layout decays — streaming appends fragment it, updates de-sort it — and the four numbers that tell you whether any of this is working are files per partition, bytes per file, partitions per table, and the fraction of partitions a typical query prunes (Data Observability).
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.
- Layout guarantees nothing about correctness in either direction: a query returns the same rows whatever the layout, provided the recorded statistics are honest. This is the property that makes layout safe to change and dangerous to trust blindly — if statistics are stale or wrong, a reader will skip data it needed and return a confidently incomplete answer.
- It guarantees nothing about freshness. A perfectly laid-out table can be a week stale, and a well-pruned query over stale data is a fast wrong answer (Freshness Monitoring).
- Partition pruning is a best-effort planner behaviour, not a contract. Nothing in SQL promises that a filter on a partition column will prune; it depends on the planner seeing the predicate in a form it can evaluate against partition metadata (Partition Pruning).
- File-level and row-group statistics are written by the writer. A format guarantees where they live, not that a given writer populated them or that they survived a subsequent rewrite (Parquet Internals).
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 catches layout regressions is structural rather than value-based: for each partition written in the last period, record the file count and the total size, and alert when the number of files per partition or the median file size crosses a stated band.
- A second check on the read side: sample real queries and record the ratio of partitions pruned to partitions available. A pruning ratio that collapses after a release is the signal that a predicate shape changed, not that the data did (Partition Pruning).
- What both miss is everything about correctness. A table can be laid out beautifully and contain duplicated rows, missing days or a metric that changed meaning — layout checks are blind to all of it, and passing them proves only that the bytes are arranged well (Data Tests).
- They also miss the case where statistics are present but useless — a min/max spanning the whole domain in every file records perfectly valid statistics and permits zero skipping.
- Layout and freshness pull against each other directly. Producing large, well-sorted files requires accumulating enough data to be worth writing, and accumulating is waiting. The freshest possible write is a small unsorted file, which is exactly the layout that queries hate (Cost vs Freshness).
- The usual resolution is two layouts for the same data: a small-file, append-only recent region for freshness, and a compacted, sorted historical region for query cost, with a scheduled job promoting one into the other (File Compaction).
- A consumer should be told which region their query touches, because the recent region behaves differently — more files, weaker statistics, more variable planning time. Presenting one freshness number for a table that is physically two things misleads everybody.
- Layout is mostly a physical concern, and a change to it should be invisible to consumers. Mostly — but not entirely, because the partition column is part of the table's public shape: consumers write predicates against it, tools display it, and some engines expose it as a pseudo-column rather than a real one.
- Changing a partition key is a full rewrite, not an
ALTER. Existing data stays in the old arrangement unless it is rewritten, so a table can be half-partitioned by one key and half by another, and queries behave differently depending on which period they touch (What Backfills Break). - A schema change can silently degrade layout: adding a high-cardinality column to a sort order, or changing a column's type so previously-recorded statistics no longer apply, both remove skipping without removing any data (Schema Evolution).
- Layout mistakes are among the most recoverable failures in this domain, because no information is lost — a badly laid-out table contains exactly the same rows as a well laid-out one. The fix is always a rewrite, and the cost is compute and time rather than data.
- Rewrite into a new location and swap, rather than rewriting in place. A layout change that overwrites a table consumers are reading turns a performance improvement into an availability incident (Atomic Publish).
- The one genuinely unrecoverable case is a rewrite that also transformed. If a compaction job cleans, casts or deduplicates while it rewrites, the original bytes are gone and the transformation cannot be re-examined (Keeping Raw History: The Recovery Position and the Liability).
What can go wrong
- Statistics that are present but non-selective, so every skip check passes and nothing is skipped. The query is slow for a reason that appears nowhere in the plan.
- A layout maintenance job that falls behind its ingest rate, so file count grows monotonically and the fix looks like it is running when it is losing.
- A partition key with a value that is null or empty, which most engines route into a single catch-all partition that grows without bound (Partition Cardinality).
- A rewrite that changes file boundaries while a long-running query holds a stale file list, producing missing-file errors in readers that were succeeding a minute earlier (File Compaction).
- Optimising layout for the query someone described rather than the queries the platform actually runs — the most common way a well-executed layout project produces no improvement (Measure Before You Optimize).
- "Layout is a performance detail, so it can wait." Layout is the cheapest thing to decide before the data exists and one of the most expensive to change after four years of it exist. It is a design decision wearing a performance costume.
- "The engine will figure it out." A planner can only exploit structure that is physically present. No optimiser can prune a partition that does not exist, or skip a file whose statistics do not exclude the predicate (Query Optimizers).
- "More partitions means more pruning." Past a point, more partitions means more metadata to read before any pruning can happen, and the planning cost overtakes the scan saving (Partition Cardinality).
- "We fixed layout, so queries are fast now." Layout fixes the queries whose predicates match the layout. A query filtering on a column the layout ignores is exactly as expensive as before, and the average across a dashboard can move very little.
- "Compression and layout are the same lever." Compression reduces the bytes of what you do read; layout reduces what you read at all. They compose, and confusing them leads to tuning codecs on a table whose real problem is that it has no partitions (Why Analytical Data Compresses).
- Partition values appear in paths, which appear in listings, logs, error messages and access-audit records. Partitioning by an identifier or any personal attribute leaks that value to anyone who can list the prefix, even without read access to the objects (PII in Pipelines).
- Layout also decides how cheaply an obligation can be met. Deleting a retention-expired period is a directory drop when the table is partitioned by time and a full table rewrite when it is not (Data Retention, Deletion Requests).
Operating it
- Files per partition and median file size, per table, tracked over time. Two numbers that predict most layout complaints before anybody makes one.
- Partition count per table, with an alert on growth rate rather than on an absolute threshold — a table gaining partitions faster than it gains days is partitioned on the wrong thing.
- Bytes scanned per query, grouped by the model or dashboard that issued it. This is the number that connects layout to cost and the only one a non-engineer finds persuasive (Scan Cost).
- Per-task input sizes for the jobs that read the table. A distribution with a long right tail is a skew report, and skew is a layout property before it is a compute one (Data Skew).
- At 10x volume, layout stops being an optimisation and becomes the thing that decides whether the pipeline fits in its window. The decisions do not change; the consequence of getting them wrong does.
- At 100x, the metadata itself becomes a scaling problem. Listing partitions, reading footers and planning a query are work proportional to file count, and platforms at this size adopt table formats specifically because a manifest is cheaper to read than a directory tree is to list (Open Table Formats).
- Cardinality scales worse than volume. Doubling rows doubles bytes; doubling the distinct values of a partition key can multiply the number of objects, and objects are what the metadata layer counts.
- Bytes scanned is the dominant driver and the one layout controls most directly — every partition pruned and every row group skipped is a byte nobody paid to read (What Actually Drives Data Platform Cost).
- Request count is the driver that surprises people, because it is invisible in any byte-based chart. On object storage the cost of finding files is paid per object, so a fragmented table is expensive at rest and expensive to plan (Object Storage).
- Bytes shuffled is driven by whether the layout happens to co-locate the join or grouping key. A layout that ignores joins does not make them wrong, it makes them redistribute (The Shuffle).
- Rewrite cost is the price of maintaining layout: compaction and re-sorting read and write the same data again, so an over-aggressive maintenance schedule can cost more than the scanning it saves (Compute Waste).
- Every layout choice optimises one access pattern and is neutral or harmful to the others. A table sorted for time-range queries is not sorted for customer lookups, and there is no arrangement that is simultaneously best for both (Every Optimization Buys Something and Sells Something).
- Good layout costs write-time work — sorting, buffering, compacting — which is latency the producer pays so that many readers do not. That is usually the right trade and it is not free, and for a table read twice a month it is not worth making.
- Maintaining layout means running jobs that rewrite data nobody asked to change, which consumes compute, creates new file versions and adds an operational surface with its own failure modes.
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.
- GENERALThe skip hierarchy — prune directories, skip files by statistics, skip row groups, project columns — holds across analytical engines and file formats. What differs is which levels a given engine implements and how much metadata it caches between queries.
- FORMAT-SPECIFICFile-level and row-group statistics exist in Parquet and ORC and not in CSV or JSON, so file skipping is impossible for text formats regardless of how the data is arranged. Layout advice for a text-format lake reduces to partitioning and file size alone.
- ENGINE-SPECIFICSpark, Trino, DuckDB and the warehouses differ in how they discover files (directory listing versus a manifest), how aggressively they cache metadata, and whether they apply statistics at row-group granularity. The same table can prune well on one engine and poorly on another.
- SIMPLIFIEDTreating layout as four independent dials is a teaching model. In practice they interact: partitioning changes what sorting can achieve, file size changes how selective row-group statistics are, and bucketing constrains both.
Where the depth lives
This domain teaches how data moves and how you know it arrived intact. It hands the rest off by name.
- — Distributed Systems owns why a file is the natural unit of work distribution and what happens to a job when one unit is far larger than the rest. This module treats skew as a layout property; the scheduling side of it belongs there.