Improving Query Performance with Partitioning

In Part 3, we introduced a partition as a way of dividing a table into smaller logical pieces based on the values of one or more columns.

The purpose is simple: when a query needs only one part of a large table, the query engine can avoid reading unrelated data.

At the time, that definition was enough. We had not created our retail tables yet, and there was not much practical data to partition.

Now we have a small processing pipeline and several tables built from it. This is a good time to return to partitioning and look at it more closely.

In this part, we will add daily partitioning to the retail tables we have created so far. We will also see what happens to existing data when a partition specification changes and how to reorganize that older data when necessary.

Our Data Naturally Grows Over Time

The source of truth for our processing pipeline is retail_clickstream_events.

This table records actions that happened on the fictional Oleander retail website: page views, searches, product views, cart activity, checkout starts, purchases, and more.

Each event has an event_time column containing the exact timestamp when the event happened. New events continue to arrive as time passes, so this is naturally time-series data.

The derived tables follow the same daily pattern.

• retail_daily_traffic_summary has event_date
• retail_sessions has session_date
• retail_session_products has event_date
• the product-funnel and cart-abandonment tables also have event_date

We have repeatedly filtered and aggregated the data by day. That works well with seven days of sample data.

But let’s imagine that the pipeline continues running for three years.

The tables now contain years of history, while many jobs still ask a small question such as, “What happened yesterday?” Without a useful partition layout, Spark may need to consider far more data files than that one day requires.

The jobs can become slower as the tables grow, and reading unnecessary data also costs more.

This is where partitioning becomes useful.

A Daily Partitioning Mental Model

If a table is partitioned by day, rows from the same day are grouped into the same logical partition.

Suppose a table contains three years of events. A query requests only January 15, 2026:

WHERE event_time >= TIMESTAMP '2026-01-15 00:00:00'
  AND event_time < TIMESTAMP '2026-01-16 00:00:00'

Iceberg keeps partition information in its metadata. From the filter, it can determine which daily partitions may contain matching rows and prune partitions that cannot match.

Spark can then read the relevant files instead of scanning files from every day in the table.

Partitioning does not mean that a three-year table becomes free to query. Iceberg still plans the scan, and one busy day may contain several data files. But the amount of data considered by a one-day query no longer has to grow at the same rate as the table’s full history.

Identity Partitions and Partition Transforms

The simplest partition field uses a column value directly. This is called an identity partition.

Here is the general form:

ALTER TABLE oleander.namespace.table
ADD PARTITION FIELD col

For data written with this partition specification, each distinct value of col becomes a logical partition value. If col has five distinct values, the data can be divided among five corresponding partition values.

Identity partitioning works naturally for columns such as event_date, which already represents one day.

It is not a good choice for event_time.

event_time contains an exact timestamp. Many events have different timestamps, so identity partitioning could create an enormous number of tiny partition values. We want days, not one partition for every moment someone looked at a mug.

Iceberg solves this with partition transforms. A transform derives the partition value from a source column.

For our raw event table, we will use:

day(event_time)

The day transform converts each timestamp into its calendar-day partition value. Iceberg tracks the relationship between event_time and that hidden value.

This is called hidden partitioning. We do not need to add an event_date column to the raw table, calculate it when writing, or mention a separate partition column when querying. We continue filtering event_time, and Iceberg derives the relevant daily partitions when possible.

Partitioning the Raw Clickstream Table

As in the earlier parts, keep the Spark session timezone set to UTC. That keeps our daily boundaries consistent with the UTC intervals used by the jobs:

spark.conf.set("spark.sql.session.timeZone", "UTC")

Then run the following statement from the interactive Spark shell:

spark.sql("""
    ALTER TABLE oleander.tutorial.retail_clickstream_events
    ADD PARTITION FIELD day(event_time)
""")

This changes the table’s current partition specification. New data written to retail_clickstream_events will use the daily transform.

Adding a partition field is a metadata change. It does not require us to drop and recreate the table, change the table schema, or rewrite the jobs that query event_time.

This ability to change partitioning without replacing the table is called partition evolution.

Partitioning the Derived Tables

The derived tables already contain DATE columns at the daily grain. For those tables, an identity partition is sufficient.

We will show the statements for the Spark SQL destination tables. You can apply the same partitioning to the equivalent DataFrame example tables with the _df suffix.

Run each of the following statements once:

spark.sql("""
    ALTER TABLE oleander.tutorial.retail_daily_traffic_summary
    ADD PARTITION FIELD event_date
""")

spark.sql("""
    ALTER TABLE oleander.tutorial.retail_sessions
    ADD PARTITION FIELD session_date
""")

spark.sql("""
    ALTER TABLE oleander.tutorial.retail_session_products
    ADD PARTITION FIELD event_date
""")

spark.sql("""
    ALTER TABLE oleander.tutorial.retail_daily_product_funnel
    ADD PARTITION FIELD event_date
""")

spark.sql("""
    ALTER TABLE oleander.tutorial.retail_daily_product_cart_abandonment
    ADD PARTITION FIELD event_date
""")

These statements have no IF NOT EXISTS clause. If a field has already been added, do not run its statement again.

We chose daily fields because our jobs and analytical queries usually process daily intervals. A useful partition strategy follows how a table is actually queried, not merely which columns happen to be available.

What Happens to Existing Data?

After the ALTER TABLE statements finish, the tables have new current partition specifications.

However, Iceberg does not automatically rewrite the existing data files.

New writes follow the new specification and place rows under the appropriate daily partition values. Existing files remain associated with the older unpartitioned specification.

This is safe. Iceberg tracks the partition specification used by each data file and can read files written under different specifications as one table. Partition evolution changes the layout without changing the logical rows returned by a query.

It also means that the new partition field cannot fully prune the older unpartitioned files. A query whose date range includes those files may still need to inspect them.

For this tutorial, leaving the old seven-day sample unpartitioned would not be a disaster. Future daily writes would use the new partition layout, and new daily processing would benefit from it.

For a larger existing table, we may also want the older data reorganized under the current specification.

Rewriting Existing Data Files

Iceberg provides the rewrite_data_files procedure for reorganizing data files.

To rewrite all existing files in the raw clickstream table using its current partition specification, run:

spark.sql("""
    CALL oleander.system.rewrite_data_files(
      table => 'tutorial.retail_clickstream_events',
      options => map('rewrite-all', 'true')
    )
""").show(truncate=False)

The procedure’s output specification defaults to the table’s current partition specification. Because we set rewrite-all to true, all selected data files are rewritten rather than only files that meet the procedure’s normal size or file-count thresholds.

The rewritten files organize the current table data using day(event_time). After the operation commits, queries against the current snapshot can use daily partition pruning across the historical range as well.

The same procedure can be applied to a derived table by changing the table name. For example:

spark.sql("""
    CALL oleander.system.rewrite_data_files(
      table => 'tutorial.retail_daily_traffic_summary',
      options => map('rewrite-all', 'true')
    )
""").show(truncate=False)

Repeat it for other tables only when rewriting their existing data is worthwhile.

Rewriting is a Spark job that reads and writes data files. On a large table, it can consume substantial time and compute resources. Partition evolution itself is inexpensive; reorganizing years of historical data is the part that sends the bill.

The rewrite creates a new Iceberg snapshot. Older snapshots can still refer to the previous files until snapshot-expiration and file-cleanup maintenance removes data that is no longer needed. We will return to table maintenance later in the series.

The Iceberg Spark procedures documentation describes the rewrite options in more detail.

Querying a Hidden Daily Partition

After partitioning retail_clickstream_events, the query itself still uses event_time:

spark.sql("""
    SELECT
      event_type,
      COUNT(*) AS event_count
    FROM oleander.tutorial.retail_clickstream_events
    WHERE event_time >= TIMESTAMP '2026-01-01 00:00:00'
      AND event_time < TIMESTAMP '2026-01-02 00:00:00'
    GROUP BY event_type
    ORDER BY event_type
""").show(truncate=False)

We do not filter a manually maintained partition column. Iceberg knows that day(event_time) is the partition transform and projects the timestamp range onto daily partition values.

This keeps the logical query independent from the table’s physical layout. If the partition strategy changes later, readers can continue asking questions using the real table columns.

For a derived table with an identity partition, we filter its date column directly:

spark.sql("""
    SELECT *
    FROM oleander.tutorial.retail_daily_traffic_summary
    WHERE event_date = DATE '2026-01-01'
""").show(truncate=False)

Iceberg can use that equality condition to prune other daily partitions.

The Iceberg partitioning documentation explains hidden partitioning and transform-based pruning in more detail.

Choosing an Appropriate Partition Size

Partitioning is useful, but more partition fields do not automatically make a table faster.

A very fine-grained partition field can produce many small partitions and small files. That increases planning and metadata overhead. Identity-partitioning event_time would be a good example of enthusiasm getting slightly out of hand.

A partition that is too coarse can have the opposite problem. Partitioning several years of clickstream data only by year would not help much when nearly every query asks for one day.

Daily partitioning is a practical fit for this tutorial because:

• the raw data grows over time
• the pipeline processes half-open daily intervals
• the derived metrics are grouped by date
• analysts frequently ask for individual days or short date ranges

The best granularity still depends on real data volume and query patterns. A very high-volume event stream might eventually need hourly partitions, while a smaller table might work well with monthly partitions.

The useful question is not, “Which partition transform is the most impressive?” It is, “Which partition layout helps the queries we actually run?”

Summary

In this part, we returned to the partition concept from Part 3 and applied it to our retail pipeline.

We used the day(event_time) transform to give the raw clickstream table hidden daily partitions. For derived tables that already contain event_date or session_date, we used identity partitions.

We learned that partition evolution is a metadata operation. New files use the new specification, while existing files remain valid under their previous specification.

When historical data also needs the new layout, rewrite_data_files can rewrite it using the current partition specification. That operation can improve pruning for older ranges, but it performs real data work and should be planned accordingly.

Most importantly, partitioning follows access patterns. Our data grows over time, and our queries usually request days, so daily partitioning gives Spark and Iceberg a useful way to skip unrelated files.

In the next part, we will add late-arriving clickstream events and use Iceberg snapshots and time travel to understand how historical metrics change.