A Practical Guide to Partitioning Strategies for Data Lake Tables

Partitioning is one of those things that sounds simple when you read the docs but gets tricky fast when you actually need to make it work on real data. In this article let us walk through how to choose a partitioning strategy for data lake tables, look at what works in practice, and point out the things that nobody tells you until it is too late.

If you have worked with data lakes on S3, GCS, or ADLS, you already know that query performance depends heavily on how your data is laid out. Without partitioning, even a simple SELECT on a terabyte-scale table means scanning everything. With the right partition strategy, you can skip 95% of the files and get results in seconds instead of minutes.

Why Partitioning Actually Matters

When you query a table stored as Parquet files in a data lake, the query engine needs to open and read every file in the table directory to find the rows you want. If you have a table with five years of daily transaction data, that is around 1,800 date folders. Without partitioning, every query scans all of them.

With partitioning, the engine checks the directory structure first and only reads the folders that match your filter. If you filter for WHERE transaction_date = '2026-07-01', it reads one folder instead of 1,800. The difference is not subtle.

But choosing the wrong column to partition on can make things worse, not better. Let us go through the options.

The Main Partitioning Strategies

1. Date-Based Partitioning

This is the most common pattern and usually the first one you reach for. You partition by a date column, typically year/month/day or yyyy-mm-dd.

s3://my-bucket/sales/
  transaction_date=2026-07-01/
  transaction_date=2026-07-02/
  transaction_date=2026-07-03/

Most query engines understand this Hive-style layout natively. In Spark, you register the partition and the filter becomes a directory listing rather than a full scan.

-- Spark SQL with a partitioned Delta table
CREATE TABLE sales (
  transaction_id STRING,
  customer_id STRING,
  amount DECIMAL(10,2)
)
USING delta
PARTITIONED BY (transaction_date DATE)
LOCATION 's3://my-bucket/sales/';

Date partitioning works well when your queries almost always filter by date. If your dashboard has a date range picker, this is the right call.

The downside is partition granularity. If you partition by day and run a query for an entire year, you are still hitting 365 partitions. That is much better than scanning everything, but partition listing overhead starts to show up. For these cases, you might want monthly or even quarterly partitions instead. The file sizes within each partition also matter, which we will get to later.

2. Categorical and High-Cardinality Partitioning

Sometimes your most common filter is not a date. Think of a table partitioned by region, tenant_id, or event_type.

CREATE TABLE events (
  event_id STRING,
  timestamp TIMESTAMP,
  payload STRING
)
USING iceberg
PARTITIONED BY (event_type)
LOCATION 's3://my-bucket/events/';

This works well when the partition column has a manageable number of distinct values, somewhere under a few hundred. But here is where people get burned: if event_type has 50,000 distinct values, you end up with 50,000 tiny folders, each with one or two Parquet files. That is called the small files problem, and it kills performance because the query engine spends more time listing directories than reading data.

A good rule of thumb: each partition should have at least a few hundred megabytes of data. If a partition is smaller than that, consider bucketing or a different column.

3. Multi-Level Partitioning

You can combine date and categorical partitions for a two-level or three-level structure.

s3://my-bucket/sales/
  region=apac/
    transaction_date=2026-07-01/
    transaction_date=2026-07-02/
  region=emea/
    transaction_date=2026-07-01/
    transaction_date=2026-07-02/

CREATE TABLE sales (
  transaction_id STRING,
  amount DECIMAL(10,2)
)
USING delta
PARTITIONED BY (region STRING, transaction_date DATE);

This is powerful but you have to get the order right. Put the lower-cardinality column first. If region has 5 values and transaction_date has 1,000, putting region first gives you 5 top-level folders, each with 1,000 sub-folders. The other way around gives you 1,000 top-level folders with 5 sub-folders each, which is harder for the listing engine to handle efficiently.

4. Hidden Partitioning with Apache Iceberg

Apache Iceberg introduced the concept of hidden partitioning, which is worth understanding because it solves a specific problem. With hidden partitioning, the physical directory layout does not need to match the partition column. Iceberg can transform a timestamp column into hourly partitions, or a string column into bucketed partitions, without you needing to create a separate column for it.

-- Iceberg partition by month from a timestamp column
CREATE TABLE events (
  event_id STRING,
  event_ts TIMESTAMP,
  payload STRING
)
USING iceberg
PARTITIONED BY (months(event_ts));

The physical layout uses a hash-based directory name, and Iceberg keeps track of the mapping in its metadata. Users query normally with WHERE event_ts >= '2026-07-01' and Iceberg figures out which partitions to read. You do not need to add a year or month column just for partitioning.

Comparison at a Glance

Strategy	Best For	Watch Out For
Date (daily)	Time-series queries, dashboards	Too many partitions for long-range scans
Date (monthly)	Longer retention, multi-year scans	Large partition sizes, skew around month boundaries
Categorical	Multi-tenant apps, low-cardinality filters	High cardinality creates small files
Multi-level	Combined region+date, tenant+date filters	Wrong order creates inefficient layout
Iceberg hidden	Avoiding derived columns, flexible transforms	Iceberg-specific, not portable to other formats

Partition Size and the Small Files Problem

This is the part that bites people in production. If you partition by day and your daily data volume is 5 MB, each partition has one tiny Parquet file. After a year, you have 365 tiny files. Query engines like Spark and Trino are optimized for reading larger files — 128 MB to 1 GB per file is the sweet spot. Thousands of small files mean thousands of S3 GET requests, and that adds up fast in both latency and cost.

What to do about it depends on your table format.

With Delta Lake, you can run OPTIMIZE to compact small files within a partition. But OPTIMIZE does not merge across partition boundaries, so if each partition has one 5 MB file, you are stuck with that layout unless you repartition.

-- Compacts small files within partitions
OPTIMIZE sales;

With Iceberg, you can use rewriteDataFiles to compact files and can also bin-pack across partitions in some configurations. You can also use the write.distribution-mode property to control file sizing at write time.

The real fix is choosing partition granularity that matches your data volume. If you have 100 MB of data per day, daily partitions make sense. If you have 5 MB per day, partition by month instead, or use a different approach entirely like z-ordering.

Z-Ordering vs Partitioning

I see people using partitioning when what they actually need is a sort order within the files. If you filter by customer_id a lot but customer_id has millions of distinct values, partitioning by it is a disaster. Instead, use z-ordering (Delta Lake) or sort order (Iceberg) to colocate related data within files without creating separate directories.

-- Delta Lake z-ordering
OPTIMIZE sales ZORDER BY (customer_id);

Z-ordering organizes data so that rows with similar customer_id values end up in the same file. Queries with WHERE customer_id = 'xyz' can skip files that do not contain that value using file-level statistics (min/max), without needing directory-level partitioning. This gives you pruning benefits without the small files problem.

Z-ordering is not a replacement for partitioning — it is a complement. Partition by low-cardinality columns that your queries filter on heavily, then z-order by high-cardinality columns within those partitions.

Things I Have Learned the Hard Way

Evolving partitions is painful. If you partition by year/month/day and later realize you need year/month, you have to rewrite the entire table. Some formats like Iceberg support partition evolution — changing the partition spec without rewriting data — but it is not free and adds complexity. Think about your retention and query patterns before you write the first row.

Partition pruning does not work with function calls. If you have WHERE YEAR(transaction_date) = 2026 but your table is partitioned by transaction_date, the engine cannot prune because the predicate does not directly match the partition column. You need WHERE transaction_date >= '2026-01-01' AND transaction_date < '2027-01-01'. Write your queries to match the partition column directly.

Cloud storage LIST operations cost money. S3 charges per 1,000 LIST requests. When you partition too granularly and query wide date ranges, the listing cost alone can be noticeable. This is another reason to keep partition counts reasonable.

Skewed partitions happen. If one region has 80% of your data and the others have 5% each, your partitioning looks balanced on paper but your queries hit a giant partition most of the time. In that case, consider salting or sub-partitioning the large region differently.

What Changes in Production

In a demo, you create a table with a PARTITIONED BY clause and it works. In production, you need to think about a few more things.

First, file sizing at write time. Configure your writers to target a specific file size. For Spark, setting spark.sql.files.maxRecordsPerFile or using Delta’s delta.targetFileSize gives you control over output file sizes rather than relying on defaults that might create too many small files.

Second, compaction jobs. Set up a scheduled job — Step Functions, Airflow, whatever you use — that runs OPTIMIZE or rewriteDataFiles after ingestion. For daily data, run it once a day. This keeps file counts under control without you having to think about it every time you write data.

Third, monitoring. Track the number of files per partition and the average file size. If you see a partition crossing a few thousand files, it is time for compaction. If you see average file sizes dropping below 50 MB, adjust your write configuration.

Partitioning is one of those foundational decisions that is hard to change later, so spending time upfront thinking about your query patterns, data volume, and growth rate pays off. Start simple, monitor the file layout, and iterate. Most tables do fine with a single date-based partition at the right granularity. Add complexity only when you have a clear reason.