# Improving Query Performance with Partitioning > Growing Tips (Apache Iceberg & Spark 101), part 13 of 16. > Partition your tables so queries only walk the rows they need instead of trampling the whole field. > Source: https://oleander.dev/grow/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: ```sql 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: ```sql 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: ```sql 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: ```python spark.conf.set("spark.sql.session.timeZone", "UTC") ``` Then run the following statement from the interactive Spark shell: ```python 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: ```python 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: ```python 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: ```python 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](https://iceberg.apache.org/docs/1.10.1/spark-procedures/#rewrite_data_files) describes the rewrite options in more detail. ## Querying a Hidden Daily Partition After partitioning `retail_clickstream_events`, the query itself still uses `event_time`: ```python 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: ```python 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](https://iceberg.apache.org/docs/latest/partitioning/) 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.