Data Layout

An analytical query's cost is dominated by how much data it has to read, and how much it has to read is determined by how much the engine can skip. Skipping requires that the data be organised so that entire partitions and files can be excluded from the scan by examining metadata alone.

Data layout is that organisation: the partitioning columns, the clustering or sort order within partitions, and the file sizes that determine the granularity of the per-file statistics used for pruning.

Why it matters

Separating storage from compute makes compute elastic and does nothing about scanning too much data. A platform with poor layout responds to slow queries by adding compute — which works, costs enormously, and never addresses the cause. The bill grows with usage and the performance never improves.

Correcting the layout is unusual among optimisations in that it reduces cost and improves performance simultaneously, frequently by an order of magnitude, with no trade-off to negotiate. It is also the intervention addressed last, because adding compute is faster and requires no analysis.

Implementation patterns

• Partition on the column queries actually filter by, derived from query logs rather than from assumptions. The assumed filter column and the real one differ surprisingly often.
• Time is usually the right partition key for event and fact data, since almost every analytical query is time-bounded — but at a granularity that produces reasonably-sized partitions, since daily partitions on a small table produce thousands of tiny files.
• Cluster or sort within partitions on the secondary filter columns, so per-file statistics are narrow and pruning is effective.
• Avoid high-cardinality partition keys, which produce a partition per value and an unmanageable file count.
• Target file sizes in the hundreds of megabytes, since per-file overhead dominates for small files and pruning granularity suffers for very large ones.
• Compact continuously, because streaming ingestion produces small files and an uncompacted table degrades gradually and silently.
• Sort during compaction, so the maintenance improves pruning rather than merely reducing file count.
• Measure bytes scanned per query, not just duration — this is the metric that reveals a layout problem, and duration hides it when compute is elastic.

Industry example

The design of separated-storage analytical platforms makes this the central performance lever. Snowflake's micro-partition metadata and Databricks' file statistics both exist to enable pruning, and the published guidance for both converges on the same advice: cluster on the filter columns, keep files appropriately sized, and compact.

The recurring organisational pattern is equally consistent: teams scale up warehouses to fix query latency, costs rise proportionally, and the underlying full scans continue — until someone examines bytes scanned and finds that a partitioning change reduces it by 95%.

Failure scenarios

• Partitioning on a column nothing filters by, so pruning never engages.
• High-cardinality partition keys, producing an unmanageable number of tiny partitions.
• No clustering within partitions, so per-file statistics are too wide to exclude anything.
• Small files from uncompacted streaming ingestion, defeating pruning and multiplying per-request cost.
• SELECT * on wide columnar tables, reading every column when four are needed — which no layout fixes.
• Layout chosen at table creation and never revisited as query patterns change.
• Adding compute as the response to slow queries, which is effective, expensive and permanent.
• Bytes scanned never measured, so the problem is invisible.

Trade-offs

Good layout for one access pattern is frequently poor layout for another. A table partitioned by date serves time-range queries excellently and customer-lookup queries badly, and a table cannot be optimally laid out for every consumer.

Maintaining layout costs compute: compaction and re-clustering are background jobs that consume resources continuously, and their cost must be weighed against the query savings — which it almost always wins, and should be measured rather than assumed.

There is also a real coupling between the physical layout and the query patterns, which means the layout must be revisited as consumption changes — an ongoing responsibility rather than a one-time design.

The trade is maintenance cost and access-pattern specialisation in exchange for the dominant factor in analytical cost. Where consumers genuinely need incompatible layouts, the answer is a second physical representation — a derived table laid out differently — which is cheaper than either compromising or scanning everything.

Interview question

"Our warehouse bill doubled this year and query times are the same. Tell me what you would look at first, what metric would confirm your hypothesis, and why buying a bigger warehouse would have made the numbers look better while making the situation worse."