TL;DR: Make the engine read less. Partition tables on the columns you filter by so the engine skips irrelevant partitions (partition pruning). Store data column-major (Parquet/ORC) so analytical queries read only the columns they need, with compression and per-block min/max stats enabling predicate pushdown. Avoid the small-files problem (compact into right-sized files) and the over-partitioning problem (too many tiny partitions). Layout often matters more than the query engine.
How to approach it. Frame the goal: query performance is mostly about reading less data, and layout (partitioning plus format plus file sizing) controls that. Walk partition pruning, columnar formats, and the file-count pitfalls with concrete cause and effect, then make the call that layout beats engine tuning.
A strong answer. Partitioning and pruning. Physically split the table by a column's values (commonly date, e.g. /date=2026-06-18/). A query filtering on that column then reads only matching partitions, partition pruning, skipping the rest entirely. Partition on columns you frequently filter by that have moderate cardinality. The trap is over-partitioning on a high-cardinality column (one tiny partition per user id), which creates millions of small files and metadata overhead that runs slower than no partitioning at all. Within partitions, modern engines also use data skipping, Z-ordering, or clustering (min-max stats per file, or sorting on a column) to prune at the file level for secondary filter columns.
Columnar formats (Parquet/ORC). Analytical queries touch a few columns over many rows, so storing data column-major lets the engine read only the needed columns instead of whole rows (large IO savings versus row-based CSV or JSON). Columnar formats compress far better (similar values sit together) and store per-row-group statistics (min/max), enabling predicate pushdown: skip row groups that cannot match the filter. Parquet is the default for this reason. Row formats like Avro suit write-heavy, row-at-a-time streaming; columnar suits analytical reads.
The small-files problem. Streaming or frequent small writes produce many tiny files, and query engines then pay per-file open and scheduling overhead that dominates runtime while metadata bloats. Fix by compacting into right-sized files (often ~128MB to 1GB each, e.g. Delta OPTIMIZE, or coalesce on write). The opposite extreme also hurts: a few enormous files limit parallelism. Target a sane file size and count.
| Lever | What it controls | Failure mode if wrong |
|---|---|---|
| Partition column | Coarse directory pruning | High-cardinality key explodes into tiny files |
| Parquet/columnar | Column pruning, compression, pushdown | Row formats force full-row scans, no stats |
| File sizing | Per-file overhead vs parallelism | Too many tiny files, or too few giant ones |
| Z-order/clustering | File-level pruning on secondary columns | Skipped, so secondary filters scan everything |
The defensible position: design the layout to read less. Partition on real filter columns (not high-cardinality), use Parquet for analytics, keep files right-sized, and lean on data-skipping stats. This usually beats tuning the query or scaling the cluster.
Key takeaways.
- Query speed is dominated by bytes read; partitioning, columnar format, and file sizing all exist to cut that.
- Partition on moderate-cardinality filter columns (date is the classic); never partition on a key that fans out into tiny files.
- Parquet wins on three axes at once: column pruning, compression, and row-group min/max stats for predicate pushdown.
- Compact small files (OPTIMIZE/coalesce) before you reach for a bigger cluster.
What interviewers probe next.
- "What column do you partition on?" A frequently-filtered, moderate-cardinality column (date is the classic); avoid high-cardinality keys that explode into tiny partitions.
- "Partitioning vs Z-order/clustering?" Partitioning is physical directory pruning (coarse); Z-order and clustering sort data so file-level min-max stats prune secondary columns without exploding partition count.
- "Why does Parquet beat CSV for analytics?" Column pruning, better compression, and row-group stats for predicate pushdown; CSV forces full-row scans with no stats.
- "How do you fix slow queries from small files?" Compact to right-sized files (OPTIMIZE/coalesce) and reduce write frequency or batch the writes.
Common mistakes.
- Over-partitioning on a high-cardinality column, creating millions of tiny files that slow everything.
- Storing analytics data as CSV/JSON (row-based) and scanning whole rows for a few columns.
- Ignoring the small-files problem from streaming writes.
- Tuning the engine or scaling the cluster before fixing the data layout that forces over-reading.