iceberg Table Function

Note

`retention_period` and `retain_last` override only the **table-level** retention defaults. Per-ref (branch/tag) retention overrides configured in the Iceberg table properties (e.g., `refs.<branch>.min-snapshots-to-keep`) are never overridden — they always take effect as specified in the table metadata.

**Example:**

```
SET allow_insert_into_iceberg = 1;

-- Create some snapshots by inserting data
INSERT INTO iceberg_table VALUES (1);
INSERT INTO iceberg_table VALUES (2);
INSERT INTO iceberg_table VALUES (3);

-- Expire using retention policy only
ALTER TABLE iceberg_table EXECUTE expire_snapshots();

-- Expire with a safety fuse: protect snapshots newer than the timestamp (positional syntax)
ALTER TABLE iceberg_table EXECUTE expire_snapshots('2025-01-01 00:00:00');

-- Same using the named argument form
ALTER TABLE iceberg_table EXECUTE expire_snapshots(expire_before = '2025-01-01 00:00:00');

-- Override retention parameters for one execution
ALTER TABLE iceberg_table EXECUTE expire_snapshots(retention_period = '3d', retain_last = 10);

-- Expire explicit snapshots
ALTER TABLE iceberg_table EXECUTE expire_snapshots(snapshot_ids = [101, 102, 103]);

-- Dry-run preview (no metadata updates, no file deletes)
ALTER TABLE iceberg_table EXECUTE expire_snapshots(retention_period = '1d', dry_run = 1);
```

**Output:**

The command returns a table with two columns (`metric_name String`, `metric_value Int64`) containing one row per metric. The metric names follow the [Iceberg spec](https://iceberg.apache.org/docs/latest/spark-procedures/#output):

|  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |
| --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- |
| metric\_name Description|  |  |  |  |  |  |  |  |  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | | `deleted_data_files_count` Number of data files deleted|  |  |  |  |  |  |  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | | `deleted_position_delete_files_count` Number of position delete files deleted|  |  |  |  |  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | | `deleted_equality_delete_files_count` Number of equality delete files deleted|  |  |  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | --- | --- | | `deleted_manifest_files_count` Number of manifest files deleted|  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | | `deleted_manifest_lists_count` Number of manifest list files deleted|  |  |  |  | | --- | --- | --- | --- | | `deleted_statistics_files_count` Number of statistics files deleted (always 0 currently)|  |  | | --- | --- | | `dry_run` `1` for dry-run mode, `0` for normal execution | | | | | | | | | | | | | | | |

The command performs the following steps:

1. Evaluates the retention policy (see below) to determine which snapshots must be preserved
2. If a timestamp argument was provided, additionally protects all snapshots at or newer than that timestamp
3. Expires snapshots that are neither retained by the policy nor protected by the timestamp fuse
4. Computes which files are exclusively associated with expired snapshots
5. In normal mode: generates new metadata without the expired snapshots
6. In normal mode: physically deletes unreachable manifest lists, manifest files, and data files
7. In `dry_run = 1` mode: skips steps 5 and 6 and only returns the calculated metrics

#### Snapshot Retention Policy[​](#iceberg-snapshot-retention-policy "Direct link to Snapshot Retention Policy")

The `expire_snapshots` command respects the [Iceberg snapshot retention policy](https://iceberg.apache.org/spec/#snapshot-retention-policy). Retention is configured via Iceberg table properties and per-reference overrides:

|  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |
| --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- |
| Property Scope Default Description|  |  |  |  |  |  |  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | | `history.expire.min-snapshots-to-keep` Table `iceberg_expire_default_min_snapshots_to_keep` (default `1`) Minimum number of snapshots to keep in each branch's ancestor chain|  |  |  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | --- | --- | | `history.expire.max-snapshot-age-ms` Table `iceberg_expire_default_max_snapshot_age_ms` (default `432000000`, 5 days) Maximum age (in ms) of snapshots to retain in a branch|  |  |  |  | | --- | --- | --- | --- | | `history.expire.max-ref-age-ms` Table `iceberg_expire_default_max_ref_age_ms` (default `∞`) Maximum age (in ms) for a snapshot reference (branch or tag) before the reference itself is removed | | | | | | | | | | | | | | | |

Each snapshot reference (`refs` in the Iceberg metadata) can override these with per-ref fields: `min-snapshots-to-keep`, `max-snapshot-age-ms`, and `max-ref-age-ms`.

**Retention evaluation:**

- **For each branch** (including `main`): the ancestor chain is walked starting from the branch head. Snapshots are retained while either of these conditions is true:
  - The snapshot is one of the first `min-snapshots-to-keep` in the chain
  - The snapshot's age is within `max-snapshot-age-ms` (i.e., `now - timestamp-ms <= max-snapshot-age-ms`)
- **For tags**: the tagged snapshot is retained unless the tag has exceeded its `max-ref-age-ms`, in which case the tag reference is removed
- **Non-main references** whose age exceeds `max-ref-age-ms` are removed entirely (the `main` branch is never removed)
- **Dangling references** that point to non-existent snapshots are removed with a warning
- **The current snapshot is always preserved**, regardless of retention settings

**Required privileges:**

The `ALTER TABLE EXECUTE` privilege is required, which is a child of `ALTER TABLE` in the ClickHouse access control hierarchy. You can grant it specifically or via the parent:

```
-- Grant only EXECUTE permission
GRANT ALTER TABLE EXECUTE ON my_iceberg_table TO my_user;

-- Or grant all ALTER TABLE permissions (includes ALTER TABLE EXECUTE)
GRANT ALTER TABLE ON my_iceberg_table TO my_user;
```

Note

- Only Iceberg format version 2 tables are supported (v1 snapshots do not guarantee `manifest-list`, which is required to safely identify files for cleanup)
- The current snapshot is always preserved, even if it is older than the specified timestamp
- Requires the `allow_insert_into_iceberg` setting to be enabled
- Requires the `allow_experimental_expire_snapshots` setting to be enabled
- The catalog's own authorization (REST catalog auth, AWS Glue IAM, etc.) is enforced independently when ClickHouse updates the metadata

## See Also[​](#see-also "Direct link to See Also")

- [Iceberg engine](/docs/engines/table-engines/integrations/iceberg)
- [Iceberg cluster table function](/docs/sql-reference/table-functions/icebergCluster)