Pruning for Iceberg: 90% of an Iceberg Is Underwater

Pruning for Parquet-based Iceberg tables

Pruning leverages efficient metadata operations to eliminate large data chunks before they are processed or loaded. It can be applied not only to simple filter predicates such as WHERE order_date < '2025-01-10' but also to more complex queries, including Top-K queries and joins.

Snowflake has consistently provided file-level pruning for Iceberg tables by default. However, because Iceberg tables often contain large Parquet files that can reach multiple gigabytes, sub-file-level pruning becomes essential for optimal performance.

Parquet file storage organization

The figure below illustrates the structure of Parquet files. At the sub-file level, Parquet files consist of three key entities relevant for pruning: row groups, column chunks and pages.

Parquet files are horizontally divided into row groups, spanning all columns. Each row group contains a set of consecutive rows, with a single Parquet file containing one or more row groups.

Within a row group, a segment of a specific column is called a column chunk. These column chunks store the actual column data, arranged sequentially in units called pages. Pages are the smallest, indivisible storage entities in a Parquet file.

The sizes of Parquet files, row groups and pages are adjustable using most Parquet writers and tools, allowing flexibility in optimizing storage and query performance.

Utilizing Parquet metadata for pruning

Understanding the structure of Parquet files is crucial for identifying pruning opportunities. Modern Parquet writers, when properly configured, generate and store metadata at multiple levels: file, row group and page. This metadata includes min/max values and offset information that indicate specific data locations within the file.

When Parquet files are read, the min/max metadata helps determine which parts of the file can be pruned, while the offset information helps ensure that only the relevant, nonpruned sections are physically loaded and accessed. Snowflake takes advantage of all available metadata to optimize query execution, reducing both execution time and I/O size.

This metadata is primarily stored in the footer at the end of a Parquet file. However, page-level metadata, due to its larger size, is stored separately near the footer rather than within it. Since Parquet files often contain significantly more pages than row groups, storing page metadata independently enables more efficient selective queries.

By having a dedicated metadata storage location, readers can first evaluate pruning opportunities and then proceed to load and process only the necessary parts of the Parquet file, further improving performance.

Parquet pruning workflow

Parquet pruning follows a top-down approach, operating from the largest level (file) to the smallest level (page). As mentioned earlier, a single Parquet file can contain multiple row groups, each consisting of numerous pages. To optimize efficiency, Snowflake begins by assessing file-level pruning opportunities to avoid unnecessary checks at lower levels.

If a complete Parquet file cannot be pruned, Snowflake evaluates its individual row groups. Similarly, if row group pruning isn’t possible, the system attempts to prune specific pages within those row groups.

The pruning decisions are based on quick and efficient comparisons with min/max metadata. For instance, consider the filter predicate WHERE order_date < '2025-01-10'. Snowflake can prune a row group if its min and max values indicate no overlap with the specified date range. Specifically, if  min(order_date) >= '2025-01-10' → prune the row group, the row group is pruned, helping ensure only relevant data is processed.

Page pruning

Parquet pages are the smallest units within Parquet files that can be pruned. Their relatively small size increases the likelihood of covering a narrow range of values, resulting in a higher chance of being pruned. As a result, effective page-level pruning is crucial for optimizing the performance of Iceberg queries.

However, unlike files or row groups, Parquet pages have a unique characteristic: They are unaligned across different columns. In other words, the column chunks within a row group may contain varying numbers of pages. This lack of alignment can complicate pruning efforts.

For example, as illustrated in the figure below, Column A (ColA) consists of four pages, Column B (ColB) contains two pages, and Column C (ColC) has only a single page. Understanding this structural complexity is essential for appreciating the challenges and optimizations involved in effective page-level pruning.

Due to the unaligned nature of Parquet pages, pruning decisions for one column cannot always be directly applied to others. In the figure above, a single row group contains three column chunks. If the min/max metadata indicates that ColA’s first page can be pruned, it doesn’t necessarily mean the first pages of ColB or ColC can also be pruned. For instance, ColA’s first page covers row indexes 0 to 24, while ColB’s first page spans row indexes 0 to 49 — some of which may still be necessary for the query result.

To navigate this complexity, Parquet’s OffsetIndex provides essential data for mapping pages to their respective row indexes and storage locations within the file. Using this information, Snowflake evaluates whether pruning decisions can be applied across columns. This optimizes pruning efficiency, reducing unnecessary I/O operations and computation.

Furthermore, Snowflake can intelligently transfer pruning decisions across different techniques, including filter predicate pruning, Top-K pruning and join-based pruning. For example, if a filter enables the pruning of the first two pages of ColA (covering row indexes 0 to 49) and Top-K pruning removes ColB’s second and final page (covering row indexes 50 to 99), Snowflake can infer that all pages of ColC (covering row indexes 0 to 99) — and by extension, the entire row group — can be pruned. This holistic approach enhances query performance by avoiding unnecessary data processing.

Real-world performance numbers

The effectiveness of row group and page-level pruning for Iceberg tables depends on various factors, including the nature of the queries, data distribution, Parquet file configurations (such as the number and size of row groups and pages) and the availability of metadata. Despite these variables, Snowflake’s pruning capabilities enable it to skip approximately 10% of the daily processed Iceberg data, in addition to the substantial data reduction achieved through file-level pruning.

Many customer queries have experienced significant performance gains thanks to these pruning techniques. For instance, one customer query saw its execution time reduced from over an hour to just eight minutes on a 4XL warehouse, as Snowflake pruned 280 out of 310 TB of data.

Additionally, a leading food delivery provider has observed major query speed-ups due to Iceberg pruning. Beyond reducing processing time, pruning reduces data loading by significantly lowering the number of cloud storage requests. For this customer, Snowflake’s Iceberg pruning has resulted in 5x to 10x fewer cloud storage requests, as of November 2024, leading to both time and cost savings.

How can you benefit from it?

Iceberg pruning works seamlessly out of the box, provided your queries, data and Parquet file metadata support it. To ensure optimal pruning effectiveness, it's important to verify that your Parquet files include the necessary metadata, such as page indexes, which are essential for enabling page-level pruning.