> ## Documentation Index
> Fetch the complete documentation index at: https://docs.startree.ai/llms.txt
> Use this file to discover all available pages before exploring further.

# Iceberg Partition Pruning

> Import an Iceberg table's partition spec into an External Table so the broker sends each query only to the segments whose partition can match. Covers supported transforms, query shapes, partition evolution and limits.

> [!WARNING]
>
> This feature requires StarTree Cloud 0.18.0 or later. It is opt-in and off by default.

Partition pruning imports the partition spec of an Iceberg table into the Pinot External Table. Each segment is stamped with the partition of the data file it reads. The broker then skips every segment whose partition can't match the query's filter.

It works with Iceberg hidden partitioning. You filter on the source column, such as `customer_id`, not on a partition column.

## What changes

When partition pruning is on, the importer makes three changes to the table.

**Table config.** For every partition field it can reproduce exactly, the importer adds the source column to `tableIndexConfig.segmentPartitionConfig.columnPartitionMap`. It then adds `partition` to `routing.segmentPrunerTypes`. Existing pruner types stay in place.

| Iceberg transform | Pinot partition function | `numPartitions` |
| - | - | - |
| `bucket[N]` | `IcebergBucket` (same hash as Iceberg) | `N`, taken from the spec |
| `identity` on a string | `Murmur3` | 32 |
| `identity` on int, long or decimal | `IcebergTruncate` with width 1 | 32 |
| `truncate[W]` | `IcebergTruncate` with width `W` | 32 |

Iceberg records no partition count for `identity` or `truncate`. Pinot hashes those values into 32 partitions. The count is fixed and you can't configure it.

**Task config.** The importer records what it owns in the `ExternalTableSyncTask` config: `partitionPruning.imported`, `partitionPruning.importedColumns` and `partitionPruning.prunerImported`. Don't set or edit these keys.

**Segments.** As each data file becomes a segment, the segment's metadata is stamped with that file's partition id. The id comes from the spec the file was written with, not from the table config. A file with no supported partition field is left unstamped.

**Query fan-out.** For a filter like `customer_id = 42`, the broker computes the partition for `42` and routes the query only to segments stamped with that partition. Unstamped segments are always queried, so results stay correct. With `bucket[16]`, a point lookup reads about one sixteenth of the stamped segments.

## Requirements

* StarTree Cloud 0.18.0 or later on every broker and server. Older brokers and servers don't know the `IcebergBucket` and `IcebergTruncate` functions.
* An Iceberg table read through a catalog. A table over raw S3 or GCS files has no partition spec to import.
* Sync runs on the controller in snapshot mode: `executor=controller` and no `enabled` key in the `ExternalTableSyncTask` config. Tables created with [SQL DDL](https://docs.startree.ai/corecapabilities/external-table/sql-ddl.md) get `executor=controller` automatically.
* A Pinot column for each partition source column, with a matching type (see [Limits](#limits)). Tables onboarded from the catalog get the right types by default.

## Enable or disable

| Key | Where | Default | Values |
| - | - | - | - |
| `enable_partition_pruning` | [SQL DDL](https://docs.startree.ai/corecapabilities/external-table/sql-ddl.md) `WITH` option | `false` | `'true'` or `'false'` (case-insensitive; anything else is rejected) |
| `enablePartitionPruning` | `ExternalTableSyncTask` task config | `false` | `"true"` or `"false"` |

The DDL option writes `enablePartitionPruning` into the task config. Both control the same feature.

### At create time

Add the option to the DDL:

```sql theme={null}
CREATE TABLE orders_ext WITH (
  type = 'iceberg',
  catalog_type = 'rest',
  catalog_uri = 'https://<catalog-host>/iceberg',
  schema_name = 'sales',
  table_name = 'orders',
  storage.region = 'us-east-1',
  enable_partition_pruning = 'true'
);
```

The partition config and the `partition` pruner are written when the table is created. Every segment from the first sync is stamped.

### On an existing table

1. Get the table config:

   ```bash theme={null}
   curl -s "$BASE_URL/tables/orders_ext?type=offline" | jq '.OFFLINE' > orders_ext.json
   ```

2. Add `"enablePartitionPruning": "true"` under `task.taskTypeConfigsMap.ExternalTableSyncTask`.

3. Put the config back:

   ```bash theme={null}
   curl -X PUT -H "Content-Type: application/json" \
     -d @orders_ext.json "$BASE_URL/tables/orders_ext_OFFLINE"
   ```

The next sync run imports the spec and turns on the pruner, even if there are no new files.

Segments that already exist are **not** restamped. They have no partition id, so the broker keeps querying them. Only segments created from files synced after the change are pruned. To prune the whole table, create it with the option on.

### Turn it off

Set `enablePartitionPruning` to `"false"` and put the config back. On the next sync run, the importer removes only what it added:

* Partition columns it wrote and you haven't changed.
* The `partition` pruner, unless you still have your own partition columns. In that case it stays and becomes yours.
* Its `partitionPruning.*` keys.

Segment stamps stay in segment metadata but have no effect without the pruner.

### Owning your own `segmentPartitionConfig`

The importer never changes partition config it didn't write:

* If the table already has a `segmentPartitionConfig` when you turn the feature on, the importer leaves the table alone. It adds no columns, doesn't add the `partition` pruner and doesn't stamp segments.
* If you edit a column the importer wrote, that column becomes yours. The importer stops updating it, stops stamping it, and leaves it in place when you turn the feature off.

## Example

An Iceberg table partitioned by `bucket(16, customer_id)` and `day(order_ts)`, created in Spark SQL:

```sql theme={null}
CREATE TABLE sales.orders (
  order_id    BIGINT,
  customer_id BIGINT,
  order_ts    TIMESTAMP,
  status      STRING,
  amount      DECIMAL(12, 2)
)
USING iceberg
PARTITIONED BY (bucket(16, customer_id), days(order_ts));
```

Create the External Table through the controller:

```bash theme={null}
curl -X POST -H "Content-Type: application/json" "$BASE_URL/sql/ddl" -d '{
  "sql": "CREATE TABLE orders_ext WITH (type = '\''iceberg'\'', catalog_type = '\''rest'\'', catalog_uri = '\''https://<catalog-host>/iceberg'\'', schema_name = '\''sales'\'', table_name = '\''orders'\'', storage.region = '\''us-east-1'\'', enable_partition_pruning = '\''true'\'')"
}'
```

Add `?dryRun=true` to see the table config without creating the table.

The importer writes this partition config. Iceberg `long` maps to Pinot `LONG`, so `customer_id` qualifies. `day(order_ts)` has no Pinot partition function and is left out:

```json theme={null}
{
  "tableIndexConfig": {
    "segmentPartitionConfig": {
      "columnPartitionMap": {
        "customer_id": {
          "functionName": "IcebergBucket",
          "numPartitions": 16,
          "functionConfig": { "icebergType": "long" }
        }
      }
    }
  },
  "routing": {
    "segmentPrunerTypes": ["partition"]
  }
}
```

The controller log names every field that was left out:

```text theme={null}
Iceberg partition fields [order_ts_day (day)] of table sales.orders have no Pinot partition function and will not drive broker pruning. A temporal transform still prunes ranges through column min/max on the server.
```

Each segment's metadata carries its own partition:

```json theme={null}
"segment.partition.metadata": {
  "columnPartitionMap": {
    "customer_id": {
      "functionName": "IcebergBucket",
      "numPartitions": 16,
      "partitions": [5],
      "functionConfig": { "icebergType": "long" }
    }
  }
}
```

## Query

Pruning applies when the filter compares the bare partition column with literals.

| Query shape | Example | Prunes? |
| - | - | - |
| `=` | `WHERE customer_id = 42` | Yes |
| `IN` | `WHERE customer_id IN (42, 77, 91)` | Yes, to the union of the partitions |
| `AND` with other filters | `WHERE customer_id = 42 AND status = 'SHIPPED'` | Yes. One prunable branch is enough |
| `OR`, all branches on partition columns | `WHERE customer_id = 42 OR customer_id = 77` | Yes |
| `OR` with any other branch | `WHERE customer_id = 42 OR status = 'SHIPPED'` | No |
| Range | `WHERE customer_id BETWEEN 40 AND 50` | No. Hashing doesn't keep order |
| `!=`, `NOT IN`, `NOT` | `WHERE customer_id <> 42` | No |
| Function on the column | `WHERE ABS(customer_id) = 42` | No |
| Temporal partition column | `WHERE order_ts >= '2026-01-01'` | Not by partition. Column min/max can still skip segments on the server |
| Hidden partitioning | `WHERE customer_id = 42` (source column, not a partition column) | Yes. This is the only form |
| Multi-stage engine | `SET useMultistageEngine = true; SELECT ... WHERE customer_id = 42` | Yes. Each table scan is pruned by its own filter |
| Join | `... FROM orders_ext o JOIN customers c ON o.customer_id = c.id WHERE o.customer_id = 42` | Each side is pruned by its own filter. Join keys alone don't prune |

The broker parses each literal as the column's type before it computes the partition. An integral value written with a decimal point, such as `customer_id = 42.0` on a `LONG` column, is read as `42`. A literal that can't equal any stored value, such as `customer_id = 42.5`, matches no rows, so skipping segments for it is safe.

For a bucketed `date` column, the literal is the Iceberg day count, such as `event_date = 20454`, because Pinot stores the column as days since epoch.

In the [multi-stage engine](https://docs.startree.ai/corecapabilities/query_data/query_languages/msqe.md), broker pruning is on by default. To turn it off for one query, use `SET useBrokerPruning = false;`. Partition pruning doesn't set up colocated joins.

### See pruning on a query

Compare these fields in the query response:

* `numSegmentsQueried`: segments the broker sent the query to.
* `numSegmentsPrunedByBroker`: segments the broker skipped.

```bash theme={null}
curl -s -X POST -H "Content-Type: application/json" "$BROKER_URL/query/sql" \
  -d '{"sql": "SELECT COUNT(*) FROM orders_ext WHERE customer_id = 42"}' \
  | jq '{numSegmentsQueried, numSegmentsPrunedByBroker}'
```

Run the same query with a filter on a non-partition column. The difference is what pruning saves.

## Indexes

Pruning happens at the broker, before any index is used. The partition column needs no index, and [indexes](https://docs.startree.ai/corecapabilities/external-table/indexes.md) on other columns still apply to the segments that are queried.

## Verify

1. Check the table config:

   ```bash theme={null}
   curl -s "$BASE_URL/tables/orders_ext?type=offline" \
     | jq '.OFFLINE | {partitions: .tableIndexConfig.segmentPartitionConfig, pruners: .routing.segmentPrunerTypes}'
   ```

   Expect your partition columns under `columnPartitionMap` and `partition` in the pruner list.

2. Check one segment's stamp:

   ```bash theme={null}
   curl -s "$BASE_URL/segments/orders_ext_OFFLINE/<segmentName>/metadata" \
     | jq '."segment.partition.metadata"'
   ```

   Expect one partition id per partition column. A `bucket` on a `date` column, and `identity` or `truncate` on a `decimal` column, also list partition `0` on every segment, so older brokers stay correct during an upgrade. Segments synced before you turned the feature on have no stamp.

3. Run a point query on a partition column. Expect `numSegmentsQueried` well below the table's segment count and `numSegmentsPrunedByBroker` above zero.

## Partition evolution

Each data file is stamped from the spec it was written with. Iceberg keeps every spec the table has used, so files under old and new specs coexist in one Pinot table. For example, `bucket[4]` and `bucket[8]` segments for the same column each prune correctly. The broker reads the function and partition count from each segment, not from the table config.

On every sync run, the importer reads the current spec:

* A new supported partition column is added to the table config and the pruner, as long as the importer owns the partition config.
* A column from an older spec stays in the config while it is still valid, so older segments keep pruning.
* A column that becomes invalid is removed from the config. That happens if it's dropped from the Pinot schema, becomes multi-value, changes type, or becomes the output of an ingestion transform.
* If the catalog is unreachable, the importer keeps the last valid config and tries again on the next run.

A source column renamed in Iceberg still prunes. Pinot keeps the original column name and resolves the new name as an alias.

## Limits

* Only `bucket`, `identity` and `truncate` prune. `year`, `month`, `day`, `hour` and `void` are skipped.
* Supported source types:

  | Transform | Iceberg source types | Pinot column type |
  | - | - | - |
  | `bucket[N]` | int, long, string, decimal, date, binary, fixed, uuid | INT, LONG, STRING, BIG\_DECIMAL, LONG with `EPOCH\|DAYS` format (date), BYTES (binary, fixed, uuid) |
  | `identity`, `truncate[W]` | int, long, string, decimal | INT, LONG, STRING, BIG\_DECIMAL |

  The Pinot type must match exactly. A bucket on an Iceberg `int` needs a Pinot `INT`, not a `LONG`. `time`, `timestamp`, `float`, `double` and `boolean` sources never prune.
* Partition fields on nested struct fields are skipped. Pinot stores the struct as one JSON column.
* Multi-value columns, and columns produced by an ingestion transform, are skipped.
* If one column has two partition fields, such as `identity(x)` and `bucket[8](x)`, only one is kept. The later field in spec order wins and the controller logs a warning.
* `identity` and `truncate` always use 32 partitions. Columns with many distinct values still share 32 partition ids.
* Ranges never prune by partition.
* Existing segments aren't restamped when you turn the feature on.
* Tables on the periodic watcher path (an `enabled` key in the task config) are not pruned. If such a table has config written by the importer, the watcher removes it.
* Each skipped field only loses pruning. Results stay correct.

## Common errors and symptoms

| Error or symptom | Cause | Fix |
| - | - | - |
| `Invalid enable_partition_pruning: '<value>'; expected 'true' or 'false'.` | The DDL option has a value other than `true` or `false`. | Use `'true'` or `'false'`. |
| No pruning. Controller log: `Iceberg partition fields [...] of table <ns>.<table> have no Pinot partition function and will not drive broker pruning.` | Every partition field is temporal, `void`, or on an unsupported type. | Expected. Filter on a time column for min/max pruning, or partition the Iceberg table by `bucket`, `identity` or `truncate`. |
| No pruning, no log line, `segmentPartitionConfig` missing | The Pinot column type doesn't match the Iceberg type, the field is nested or multi-value, or the column is a transform output. | Make the Pinot column type match the [Limits](#limits) table. |
| No pruning, `segmentPartitionConfig` present but no `partition` pruner | The table already had its own `segmentPartitionConfig` when you turned the feature on. | Add `"partition"` to `routing.segmentPrunerTypes` yourself, or remove your config and let the importer write it. |
| No pruning, `enablePartitionPruning` is `"true"` but nothing is written | Sync isn't on the controller snapshot path. | Set `executor=controller` and remove the `enabled` key from the task config. |
| Only new data prunes | Segments synced before you turned the feature on have no stamp. | Expected. Create the table with the option on to prune all of it. |
| Controller log: `Column <col> of table <ns>.<table> has more than one Iceberg partition field. Pinot keys partitioning by column, so only one is kept: ...` | Two partition fields on one column. | Expected. Filter on the kept field's column; the log names it. |
| Controller log: `Relinquished ambiguous imported partition ownership for table <table>; preserving its partition config and broker pruner` | The `partitionPruning.*` keys were edited or removed by hand. | The importer stops managing the table's partition config, which is now yours. To hand it back, remove `segmentPartitionConfig` and the `partition` pruner, then let the next sync import it again. |
| Controller log: `Failed to load live partition metadata for <table>; retaining the locally revalidated contract` | The catalog was unreachable during sync. | Fix catalog access. Pruning keeps using the last valid config. |
| Controller log: `Cannot refresh imported partitioning for <table>: schema not found` | The table has no schema. | Restore the schema. |


This documentation is built and hosted on [Mintlify](https://mintlify.com), a developer documentation platform.