> ## 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. # Time-window file filter > Ingest only recent data from an Iceberg table with iceberg.snapshotProcessing.fileFilter, a rolling time window applied at sync. 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](./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](#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](#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](https://docs.startree.ai/corecapabilities/external-table/sql-ddl.md)): ```sql theme={null} 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. ```json theme={null} { "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\"}]" } } } } ``` ```bash theme={null} 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: ```json theme={null} "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\"}]" } } } ``` ```bash theme={null} 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: ```json theme={null} [{"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: ```sql theme={null} -- 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: ```sql theme={null} 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 1. Check the sync checkpoint: ```bash theme={null} curl "$BASE_URL/tables/events_ext_OFFLINE/externalTable/checkpoint" ``` Expect `"state": "DONE"`. If it shows `FAILED`, read `errorMessage` and `consecutiveFailures`. 2. Check the controller log. Each listing batch logs a line like: ```text theme={null} 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`. 3. 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. 4. 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](https://docs.startree.ai/corecapabilities/external-table/segment-groups.md) 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: ```text theme={null} External table '' has an invalid iceberg.snapshotProcessing.fileFilter (not overridable by forceConfig): ; ``` `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 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. | This documentation is built and hosted on [Mintlify](https://mintlify.com), a developer documentation platform.