Time-window file filter

The time-window file filter tells an External Table to ingest only the Iceberg data files that can hold data from the last N days or hours. Files that are provably older are left out of the table’s snapshot, so queries don’t see them. The window rolls forward on its own, so the table keeps a moving slice of recent data without any change to the Iceberg table.

What changes

Which files get ingested. At each sync, every data file in the Iceberg snapshot is dated. A file is skipped only when its metadata proves that all of its data is older than the cutoff. Every other file is ingested as before.
Whole files, not rows. The filter decides per file. A file that spans the cutoff is kept whole, older rows included. It is not an exact row-level time predicate.
The cutoff. For Nd, the cutoff is midnight UTC today minus N days. For Nh, it is the start of the current UTC hour minus N hours. So 30d keeps today plus the 30 previous full UTC days.
How the window rolls. When the UTC day (or hour) cutoff moves forward, the next sync run re-evaluates the table, even if nothing new was committed to Iceberg. Files that have aged out are dropped from the table at that run.
Snapshot and row counts. The published snapshot lists only the kept files, so COUNT(*) reflects the window, not the full Iceberg table. If every file in a snapshot is outside the window, the table serves zero rows for it.
Files that age out. They disappear from query results at the next sync run. The Pinot segments behind them are not deleted right away: they are cleaned up later by snapshot retention, like segments of files removed from Iceberg. Keep snapshot retention enabled. The filter never deletes or changes your Iceberg data files.
Queries over old dates. A query for dates before the cutoff returns no rows, except rows from files the filter had to keep (see Limits and what gets kept).

Requirements

StarTree Cloud 0.18.0 or later.
An Iceberg REST catalog: catalogType=iceberg-rest. Raw S3 or GCS listings and native Hive tables are rejected.
The snapshot sync path: executor=controller, with no enabled key in the ExternalTableSyncTask config.
enableSnapshotConsistency=true. This is the default; don’t set it to false. It lets brokers hide the files the filter excludes.
Every broker, server and controller runs StarTree Cloud 0.18.0 or later. Until the last one is upgraded, sync runs on a filtered table fail. See Limits and what gets kept.
To filter by a column, that column must exist in the table’s Pinot schema.

Enable or disable

Add the key iceberg.snapshotProcessing.fileFilter to task.taskTypeConfigsMap.ExternalTableSyncTask. The value is a JSON list with exactly one predicate, written as a string.

Field	Type	Default	Allowed values	What it does
`type`	string	required	`timeWindow`	The only predicate type.
`period`	string	required	`Nd` or `Nh`, positive, at most `36500d` or `876000h`	Window length: the last N UTC days, or the last N hours.
`column`	string	none	A column in the Pinot schema	Date files by this column’s values instead of the partition or commit time.
`format`	string	none	A Pinot DateTime format, such as `1:DAYS:SIMPLE_DATE_FORMAT:yyyy-MM-dd` or `1:MILLISECONDS:EPOCH`	How `column` is encoded. Requires `column`.

Any other field is rejected, so a typo can’t silently widen the filter.

How files are dated

The filter picks one of three dating modes.

Mode	When it applies	How a file is dated
Time partition	No `column`, and the Iceberg table has a `year`, `month`, `day` or `hour` partition on the table’s Pinot time column (`segmentsConfig.timeColumnName`)	By the file’s partition value. No extra metadata is read.
Commit time	No `column`, and no such time partition (or no Pinot time column)	By when the file’s data was committed to Iceberg.
Column	`column` is set	By the column’s minimum and maximum values in Iceberg file metadata, and its partition value when the table is partitioned on it.

Notes on each mode:

Time partition. Only a time partition on the Pinot time column counts. A time partition on another column, such as month(birth_date), is ignored because it may not date the rows. A file the partition can’t decide (null partition value, or a partition that spans the cutoff) falls back to commit time. A backfill or compaction written into an old partition is dated by the partition, so it is excluded.
Commit time. A file committed long ago but rewritten recently (compaction, import, migration) looks new and is kept. If the snapshot that added a file has expired, the file can only be bounded by a later retained snapshot. If nothing bounds it, it is kept. Tables onboarded after their history expired, and Iceberg format v1 tables, should use a time partition or column instead.
Column. A DATETIME field or a TIMESTAMP field needs no format. Any other column (a string ds partition, an epoch number) needs format. A format you set wins over a DateTime field’s own format. String patterns must sort chronologically: yyyy-MM-dd, yyyyMMdd or yyyy-MM-dd-HH. A tz(...) zone in the format is honored; otherwise values are read as UTC. The column is matched by its Iceberg field id when the Pinot schema has one, so renaming the column in Iceberg keeps the filter working.

Set it with SQL DDL

CREATE TABLE ... WITH (...) passes unrecognized options through to the ExternalTableSyncTask config, so you can set the filter at creation time (see SQL DDL):

CREATE TABLE events_ext WITH (
  type = 'iceberg',
  catalog_type = 'rest',
  catalog_uri = 'https://catalog.example.com/api/catalog',
  catalog_name = 'analytics',
  schema_name = 'prod',
  table_name = 'events',
  'iceberg.snapshotProcessing.fileFilter' = '[{"type":"timeWindow","period":"30d"}]'
)

The response includes a warning that the option was passed through verbatim. That is expected. Quote the option name so its case is kept.

Change the window

Edit the value and update the table config. The change applies when the current pass finishes: the next run lists the snapshot again with the new window. A wider window brings back files that are now inside it. A narrower one hides files that fall out.

Remove the filter

Delete the key and update the table config. The next run lists the snapshot again with no filter, and files that were excluded come back into query results. No new Iceberg commit is needed. Expect that run to ingest every file that was previously skipped.

Example

Day-partitioned table, last 30 days

The Iceberg table is partitioned by day(event_ts), and event_ts is the Pinot time column. Leave out column: files are dated by their day partition.

{
  "tableName": "events_ext_OFFLINE",
  "tableType": "OFFLINE",
  "segmentsConfig": {
    "timeColumnName": "event_ts",
    "replication": "1"
  },
  "tenants": {},
  "tableIndexConfig": {},
  "metadata": {},
  "task": {
    "taskTypeConfigsMap": {
      "ExternalTableSyncTask": {
        "catalogType": "iceberg-rest",
        "executor": "controller",
        "enableSnapshotConsistency": "true",
        "catalog.iceberg-rest.restUri": "https://catalog.example.com/api/catalog",
        "catalog.iceberg-rest.serviceType": "rest",
        "catalog.iceberg-rest.warehouse": "analytics",
        "catalog.iceberg-rest.table.namespace": "prod",
        "catalog.iceberg-rest.table.tableName": "events",
        "schedule": "0 */5 * * * ?",
        "iceberg.snapshotProcessing.fileFilter": "[{\"type\":\"timeWindow\",\"period\":\"30d\"}]"
      }
    }
  }
}

curl -X PUT "$BASE_URL/tables/events_ext" \
  -H "Content-Type: application/json" \
  -d @events_ext_table_config.json

Keep the rest of your existing table config (tenants, indexes, credentials) as it is. Only the fileFilter line is new.

String `ds` column with a format

The Iceberg table is partitioned by a string ds column (2026-11-04), kept as a dimension in the Pinot schema. Name the column and give its format. Keep the last 60 days:

"task": {
  "taskTypeConfigsMap": {
    "ExternalTableSyncTask": {
      "catalogType": "iceberg-rest",
      "executor": "controller",
      "enableSnapshotConsistency": "true",
      "catalog.iceberg-rest.restUri": "https://catalog.example.com/api/catalog",
      "catalog.iceberg-rest.serviceType": "rest",
      "catalog.iceberg-rest.warehouse": "analytics",
      "catalog.iceberg-rest.table.namespace": "prod",
      "catalog.iceberg-rest.table.tableName": "page_views",
      "schedule": "0 */5 * * * ?",
      "iceberg.snapshotProcessing.fileFilter": "[{\"type\":\"timeWindow\",\"period\":\"60d\",\"column\":\"ds\",\"format\":\"1:DAYS:SIMPLE_DATE_FORMAT:yyyy-MM-dd\"}]"
    }
  }
}

curl -X PUT "$BASE_URL/tables/page_views_ext" \
  -H "Content-Type: application/json" \
  -d @page_views_ext_table_config.json

This works even when the table’s old Iceberg snapshots have expired, because each file is dated by its ds value, not by commit history. With no column, the same table would fall back to commit time and keep the old files. Other valid values:

[{"type":"timeWindow","period":"12h"}]
[{"type":"timeWindow","period":"30d","column":"event_ts"}]
[{"type":"timeWindow","period":"7d","column":"event_epoch","format":"1:MILLISECONDS:EPOCH"}]

Query

Queries don’t change. The table simply holds less data. To confirm the window:

-- Oldest and newest time in the table
SELECT MIN(event_ts), MAX(event_ts) FROM events_ext

-- Rows per day: the oldest day should be at or near the cutoff
SELECT DATETRUNC('DAY', event_ts) AS day, COUNT(*) AS rows
FROM events_ext
GROUP BY day
ORDER BY day
LIMIT 100

-- String ds column
SELECT ds, COUNT(*) FROM page_views_ext GROUP BY ds ORDER BY ds LIMIT 100

A query for a range before the cutoff returns an empty result:

SELECT COUNT(*) FROM events_ext WHERE event_ts < ago('P90D')
-- 0, unless some old rows came in with a kept file

Indexes

Nothing specific to this feature. Indexes are built on the files that are ingested, as usual.

Verify

Check the sync checkpoint:
curl "$BASE_URL/tables/events_ext_OFFLINE/externalTable/checkpoint"
Expect "state": "DONE". If it shows FAILED, read errorMessage and consecutiveFailures.
Check the controller log. Each listing batch logs a line like:
Batch: 1200 enumerated, 40 new for execution, 1100 skipped by the file filter, isComplete=true, listing took 850 ms
Each run also logs the dating mode, for example dating files by the time partition on time column 'event_ts', no time partition on time column '...', or the table has no Pinot time column.
Run the MIN/MAX and count-by-day queries above. Expect the oldest day at or after the cutoff, plus any older rows from kept files.
The controller meter EXTERNAL_TABLE_SYNC_FILES_SKIPPED_BY_FILTER counts skipped files per table.

Limits and what gets kept

Whole-file granularity. A file is dropped only when its metadata proves it is older than the window. A file that spans the cutoff is kept whole.
Undatable files are kept. If the filter can’t date a file, it keeps it. With commit time, a file whose adding snapshot has expired and isn’t bounded by a later retained snapshot can’t be dated.
Nulls are kept. In column mode, a file with any null value in the column is kept. In partition mode, a file with a null partition value falls back to commit time.
The filter never blocks a sync. If it can’t apply to a snapshot (the column is missing from the Iceberg schema, the type is unsupported, a value can’t be parsed), that snapshot is listed unfiltered and the controller logs a warning starting File filter ... cannot apply to snapshot ...; every file is kept. Save-time checks see only the Pinot schema, so a format that fits the Pinot type but not the Iceberg type (a Pinot TIMESTAMP over an Iceberg long) saves and then hits this case.
Commit-time clock skew. Commit-time dating allows 1 hour of clock difference between Iceberg writers. Writers whose clocks differ by more can cause a file to be wrongly excluded when its adding snapshot has expired.
Cost. Partition and commit-time modes cost about the same as no filter. Column mode reads per-file column statistics from manifests, which uses more controller CPU and memory on wide tables.
Segment groups. Supported. Each filtered run regroups the kept files. Prefer time-bucketed groups so the window drops whole groups. Every controller must be upgraded before you save a filter on a segment-group table.
Rolling upgrade. Sync runs on a filtered table fail until every broker, server and controller runs StarTree Cloud 0.18.0. After iceberg.snapshotProcessing.maxRetries failed runs in a row (default 3), the snapshot goes FAILED. It moves forward again when a new Iceberg snapshot is committed, or when you set iceberg.snapshotProcessing.retryFailedSnapshot=true (default false), which retries the failed snapshot on each sync run. To avoid the failures, add the filter after the upgrade finishes.
Downgrade. Removing the filter doesn’t make a downgrade safe. Once a filtered table has synced, keep brokers, servers and controllers on StarTree Cloud 0.18.0 or later.

Common errors

Save-time errors come back from the table create or update call as:

External table '<table>' has an invalid iceberg.snapshotProcessing.fileFilter (not overridable by forceConfig): <error>; <error>

forceConfig doesn’t bypass them.

Error	Cause	Fix
`fileFilter is not valid JSON: …`	The value isn’t JSON.	Escape the inner quotes when the value is a string in the table config.
`fileFilter must be a JSON list of predicates`	The value is an object, not a list.	Wrap the predicate in `[ ]`.
`fileFilter must contain exactly one predicate, got N`	Zero or several predicates.	Send a list with one object.
`Unknown fileFilter predicate key '…'; allowed: […]`	A misspelled or extra field.	Use only `type`, `period`, `column`, `format`.
`Unsupported fileFilter predicate type '…'; only 'timeWindow' is supported`	Wrong or missing `type`.	Set `"type":"timeWindow"`.
`fileFilter timeWindow requires 'period'`	No `period`.	Add `period`, such as `30d`.
`fileFilter period '…' must match \d+[dh] (e.g. 30d, 12h)`	Unsupported unit or format, such as `1w` or `30 d`.	Use whole days or hours: `30d`, `12h`.
`fileFilter period '…' must be positive`	`0d` or `0h`.	Use a period of at least `1h`.
`fileFilter period '…' must be at most 36500d` (or `876000h`)	Period over 100 years.	Use a shorter period, or remove the filter.
`fileFilter '…' must be a string`	A field value isn’t a JSON string.	Quote it, such as `"period":"30d"`.
`fileFilter 'format' requires 'column'`	`format` without `column`.	Add `column`, or remove `format`.
`fileFilter 'format' '…' is invalid: …`	Not a valid Pinot DateTime format.	Use a format such as `1:DAYS:SIMPLE_DATE_FORMAT:yyyy-MM-dd`.
`fileFilter column '…' does not exist in the Pinot schema`	The column isn’t in the schema.	Use a column from the table’s Pinot schema.
`fileFilter column '…' must be a DateTime field, a TIMESTAMP field, or have a 'format' in the filter; it is a …`	A plain string or number column with no `format`.	Add `format`.
`column '…' uses SIMPLE_DATE_FORMAT '…', which does not sort chronologically; supported: […]`	A date pattern such as `MM/dd/yyyy`.	Use `yyyy-MM-dd`, `yyyyMMdd` or `yyyy-MM-dd-HH`.
`column '…' is <TYPE> but its format … needs a column of type […]; the filter could not bind and would list every file`	The format doesn’t fit the column’s Pinot type.	Use a `STRING` column for a date pattern, or `INT`, `LONG` or `TIMESTAMP` for an epoch.
`column '…' is an INT epoch in milliseconds, which overflows for current timestamps; store it as LONG`	Sub-second epoch in an `INT` column.	Make the column `LONG`.
`requires enableSnapshotConsistency=true so brokers hide excluded files`	Snapshot consistency is off.	Set `enableSnapshotConsistency` to `true`, or remove it (the default is `true`).
`requires catalogType=iceberg-rest, got '…'`	Not an Iceberg REST catalog.	The filter works only on Iceberg REST catalogs.
`requires executor=controller and no 'enabled' key (only the snapshot path applies the filter)`	Not on the snapshot sync path.	Set `executor=controller` and remove `enabled`.
`External-table file filtering on a segment-group table requires every live Pinot controller to support it; upgrade instance …`	Saving a filter on a segment-group table during an upgrade.	Finish upgrading every controller, then save again.
`External-table file filtering requires every live broker, server and Pinot controller to support generation manifests; upgrade instance …`	Sync run during a rolling upgrade. Shows in the checkpoint `errorMessage`.	Finish the rolling upgrade. See the rolling-upgrade note above.

Get Started

Ingestion

External Tables

Query Data

Manage Data

Observability

Visualize Data

Cluster Operations

Manage Security

Release Notes

Reference

What changes

Requirements

Enable or disable

How files are dated

Set it with SQL DDL

Change the window

Remove the filter

Example

Day-partitioned table, last 30 days

String `ds` column with a format

Query

Indexes

Verify

Limits and what gets kept

Common errors

​What changes

​Requirements

​Enable or disable

​How files are dated

​Set it with SQL DDL

​Change the window

​Remove the filter

​Example

​Day-partitioned table, last 30 days

​String ds column with a format

​Query

​Indexes

​Verify

​Limits and what gets kept

​Common errors

What changes

Requirements

Enable or disable

How files are dated

Set it with SQL DDL

Change the window

Remove the filter

Example

Day-partitioned table, last 30 days

String `ds` column with a format

Query

Indexes

Verify

Limits and what gets kept

Common errors