A data lake that works well in the first month often becomes painful by the sixth, and the cause is rarely the query engine. It is the layout of the files. Query engines on object storage mostly work by deciding which files to open and which parts of those files to read. If your layout lets them skip most of the data, queries are quick and cheap. If it does not, every query scans everything.

Two decisions dominate: how you partition, and which file format and file sizes you write.

Why Columnar Formats Are the Default

Analytical queries typically touch a few columns across many rows. Row-oriented formats like CSV and JSON force the engine to read and parse every column of every row. Columnar formats such as Parquet and ORC store each column together, so the engine reads only the columns the query names. They also compress well because similar values sit next to each other, and they carry statistics (minimum and maximum values per chunk) that let engines skip chunks that cannot match a filter.

That does not mean CSV and JSON have no place. Keep raw data in its original form in a landing zone so you can always reprocess it. Then convert to a columnar format for the curated layer that people query.

What Partitioning Does

Partitioning organizes files into directories by the value of one or more columns, such as event_date=2026-11-04/. When a query filters on that column, the engine reads only matching directories. This is called partition pruning, and it is the cheapest speedup available.

Good partition keys share three traits:

• They appear in the filters of most queries.
• They have moderate cardinality, meaning a manageable number of distinct values.
• They produce partitions of reasonable size.

Date is the classic choice because most analytical questions are bounded by time, and new data naturally arrives by date.

The Over-Partitioning Trap

A common mistake is partitioning by something with too many values, like user ID, or by several columns at once. The result is thousands or millions of tiny directories, each holding a tiny file. Listing those directories becomes slow, metadata overhead balloons, and each file read has fixed cost that dwarfs the useful work.

The opposite mistake exists too: a single huge partition means pruning never helps. If your queries filter by region and date but you partitioned only by region, a query for yesterday still scans years of history in that region.

The Small Files Problem

Streaming and frequent micro-batch ingestion tends to write many small files. Every file adds an open and a read request, and engines spend more time on overhead than on data. As a rule of thumb, aim for files in the range of hundreds of megabytes rather than kilobytes, but the ideal size depends on your engine, so check its current documentation.

The usual fix is compaction: a scheduled job that rewrites many small files in a partition into fewer larger ones. Table formats built for lakes, such as Apache Iceberg and Delta Lake, provide compaction and also handle atomic commits so readers never see half-written data. If you are managing raw Parquet directories by hand, plan for compaction from the start.

Layout Decisions at a Glance

Decision	Good default	What goes wrong
Curated file format	Columnar, such as Parquet	Querying raw CSV or JSON at scale
Partition key	Date or another commonly filtered, moderate-cardinality column	High-cardinality keys, many tiny partitions
Number of partition columns	One or two	Deep nested partitions that nobody filters on
File size	Large enough to amortize overhead	Streams writing thousands of tiny files
Compression	Engine-supported codec, applied consistently	Mixed or unsupported codecs
Schema	Consistent types across files	Same column stored as string in some files, integer in others

Sorting Within Files

Partitioning is coarse. Within a partition, sorting or clustering by a commonly filtered column makes the min/max statistics inside files far more selective, so engines skip more. Many table formats and warehouses offer clustering or sort-order features for exactly this. Use them for columns that appear in filters but would make poor partition keys, such as customer or product identifiers.

A Realistic Example

Imagine a team landing clickstream events as hourly JSON files, partitioned by user_id because someone thought per-user queries would be common. After a few months there are huge numbers of directories, and listing alone takes noticeable time. Most real queries filter on date and event type. The team rewrites the curated layer as Parquet partitioned by date only, sorted by event type within each partition, and runs a nightly compaction job. Queries that previously scanned the whole lake now read one or two days of data and only the requested columns. Nothing changed in the query engine.

Changing a Layout Later

Layout changes mean rewriting data, so test on a sample first. Write the new layout alongside the old one, point a few real queries at both, compare scanned data and runtime, then migrate consumers. Keep the old copy until the new one has passed a validation check on row counts and key aggregates.

Frequently Asked Questions

Should every column I filter on be a partition key? No. Partition on one or two columns that prune a large share of data. Use sorting or clustering for the rest.

Is Parquet always the best choice? For analytical reads, columnar formats are usually the right default. For raw archives, interoperability with outside systems or tiny datasets, simpler formats can be fine.

How do I know if I have a small files problem? Look at file counts and average file size per partition, and at query planning time. Thousands of files per partition and many-second planning delays are the telltale signs.

Can I partition by a timestamp directly? Partitioning by full timestamp creates enormous numbers of partitions. Derive a coarser column, such as date or hour, and partition on that.

Does a table format like Iceberg or Delta replace all of this thinking? It helps with compaction, atomic writes and schema evolution, but you still choose partitioning and sort order. Good layout principles still apply.

Next Step

Take your five most-run queries and list the columns each filters on. If a date or similar column appears in most of them and your largest table is not partitioned on it, start with a small-scale test of that change.