Best Practices and Configs

This feature requires StarTree release 0.15.0 or later, and must be enabled on demand — contact StarTree support to activate it.

Best practices for running External Tables, followed by the full reference for every configuration and query option: the table-level sync config, the S3 tier and caching properties, and the per-query SET options.

Two 0.16.0 features have their own dedicated config reference: Large External Tables (segmentGroupConfig) and Deletion Vectors (enableDeletionVectors and snapshot readiness).

Best practices

Onboarding

Enter the bucket name only (no s3://) and set the AWS region in the connection config — a region in config means you don’t depend on the AWS_REGION environment variable.
Grant the cluster access to the source bucket with an assumed IAM role (roleArn + optional externalId) or the cluster’s node role; verify with aws s3 ls s3://<bucket>/<prefix>/ before onboarding.
Keep the source bucket in the same AWS region as your cluster. Cross-region reads without an S3 interface endpoint go through the NAT Gateway and can add large AWS charges; see Networking and data transfer costs.
For very large sources, start with a smaller prefix subset and scale the cluster before onboarding the full dataset.
Set continueOnFileError=true if a sync run should skip a file that fails to process — unreadable, corrupt, or any other per-file failure, not just unreadable ones — instead of failing the whole run (default is to fail). Because it skips on any failure, it can silently drop more data than “unreadable file” suggests, so review sync logs after enabling it. This only affects files during a sync — it does not protect an already-ingested segment whose file is later deleted or compacted away (see the note below).
If your source compacts or rewrites files, onboard it through an Iceberg REST catalog, not the raw S3/GCS Data Lake source — a REST catalog reconciles compaction via snapshot metadata, while raw sources pin segments to physical file paths that break when files are rewritten. See Segments fail to load after upstream compaction.

Schema

Keep the data types the preview step infers; overriding them can break segment generation.
Set a time column where the data has one — it enables time-based segment pruning and retention. Recommended but not mandatory; tables without a time column still work.

Indexes

Add indexes for the columns you filter and group by — without them, queries scan remote data.
For dictionary-backed indexes (inverted, FST, IFST), keep the forward index RAW and add an explicit dictionary block.
Avoid grouping by derived/computed columns — it defeats segment pruning.

Tiered storage — data & index caching

Enable the page cache (enable.prefetch.page.cache, a per-table tier setting) so index data is served locally. See Caching and index pinning.
Pin small, hot indexes (dictionary, and anything besides a bloom filter) with preload.index.keys.override; let the index cache serve the larger/colder ones. An enabled bloom filter is pinned for you automatically — see the note below.
Turn on index consolidation to collapse a segment’s per-column index files into one mmap, avoiding the OS max_map_count limit on wide tables — see Caching and index pinning. Operations
Let the controller watcher schedule syncs; monitor health with the observability endpoints.

Networking and data transfer costs

External Tables and batch ingestion read data directly from S3, often in large volumes. Where the bucket sits relative to your StarTree cluster decides the network path those reads take, and that path decides what you pay AWS. This matters most for BYOC deployments, where the network charges land on your own AWS account.

Bucket location	Network path	What you pay
Same region as the cluster	S3 gateway endpoint (StarTree creates it by default for BYOC on AWS)	No NAT Gateway or endpoint charges
Different region, no endpoint	Node → NAT Gateway → Internet Gateway → public S3 endpoint	NAT Gateway per-GB processing plus inter-region data transfer. This can be expensive for large datasets
Different region, cross-region interface endpoint	AWS PrivateLink to S3 in the source region	Interface endpoint hourly + per-GB processing plus inter-region data transfer. Usually much cheaper than NAT

Recommendations

Co-locate the data and the cluster. Keep source buckets in the same region as your StarTree cluster whenever you can. It is the cheapest and fastest setup.
Weigh how often the data is read. For a one-time read, such as a single backfill, moving the data next to the cluster may not save much. Repeated reads of a large dataset are different: External Tables query S3 again and again, and the per-GB charges add up quickly.
Check the bucket region before onboarding. Compare it with your cluster’s region (aws s3api get-bucket-location --bucket <bucket>). A gateway endpoint only covers buckets in its own region.
For cross-region buckets, add an S3 interface endpoint before you onboard. Create a cross-region interface endpoint for S3 in your cluster’s VPC so reads skip the NAT Gateway. See the AWS guides for gateway endpoints for S3 and cross-region PrivateLink support. Contact StarTree support if you need help with the setup.
Watch NAT Gateway traffic during the first sync. A spike in the NAT Gateway’s CloudWatch byte counters during an initial onboarding usually means S3 reads are leaving the private network.

Caching and index pinning

External Table queries read indexes from remote storage, so keeping index data local is what makes them fast. There are two ways to do that — pinning and the index cache — and they’re used together.

	Pinning (preload)	Index cache
Enabled by	`preload.index.keys.override` (table setting)	`enable.prefetch.page.cache=true` (table setting)
What it does	Downloads the listed index files into local mmap at segment load and keeps them resident	Serves index byte ranges through an on-demand, evictable (LRU) cache
When it loads	Eagerly, at segment load	Lazily, on first access
Eviction	Never — always local	Evicted under memory pressure
Memory cost	Fixed and predictable	Elastic, bounded by the cache size

Pin the small, always-needed indexes; let the cache handle the rest. Pinning is resident memory, so reserve it for compact indexes you hit on most queries — bloom filters, dictionaries, and small range/inverted indexes. Everything larger or colder (JSON, text, wide columns) should go through the index cache, which fetches on demand and evicts what isn’t used.

preload.enable is not a table config key. Putting it in tierBackendProperties is silently accepted and does nothing; StarTree’s own onboarding flow currently writes it into new table configs, where it has no effect. Preloading is enabled server-side by StarTree, and preload.index.keys.override chooses what to pin.

"tierBackendProperties": {
  "preload.index.keys.override": "*.bloom_filter,*.dictionary,*.range_index",
  "enable.prefetch.page.cache": "true"
}

Pin keys are <column>.<indexKey> (or * for all columns), where indexKey is the index’s file key — e.g. inverted_index, range_index, bloom_filter, or dictionary. Examples: payment_type.inverted_index, *.bloom_filter, *.dictionary.

Bloom filters are pinned for you. Creating or updating an external table’s config — through the Data Portal or the preview/create/update API — adds *.bloom_filter to preload.index.keys.override on every S3/GCS remote tier automatically, whenever the table has an enabled bloom filter on any column. This runs on every create and update, not only at onboarding, and it only ever adds that one entry: it never removes or replaces keys you’ve set yourself. Contact StarTree support to turn it off cluster-wide.Starting with StarTree Cloud 0.18.0, saving an External Table’s config through any path, including SQL DDL and a plain POST /tables, also sets preload.index.keys.additional to *.bloom_filter on every tier that doesn’t already have that key. It applies whether or not the table has a bloom filter. Keys in preload.index.keys.additional are preloaded on top of whatever preload.index.keys.override selects. To turn this default off for one table, set preload.index.keys.additional to an empty string; a value you set, including an empty one, is kept. The preload.index.keys.override entry described above is separate and is still added while the table has a bloom filter.

Index consolidation. On a wide table, each column’s index is a separate memory-mapped file, and many segments × many columns can exhaust the OS memory-map limit (max_map_count) — surfacing as native-memory / mmap allocation failures and server restarts. Set preload.enable.index.consolidation=true to pack a segment’s index regions into a single file per segment, drastically cutting the mmap count. Onboarding enables this by default.

Where configs live

Scope	Set in	Applies to
Table config — `ExternalTableSyncTask`	`task.taskTypeConfigsMap.ExternalTableSyncTask` in the table config	Catalog connection, scheduling, snapshot processing.
Tier backend properties	`tierConfigs[].tierBackendProperties` (S3 tier)	Per-table caching and preload behavior.
Query option	`SET key = 'value'` per query	Per-query cache/prefetch behavior.

Table config — `ExternalTableSyncTask`

Catalog & mode

Key	Default	Description
`catalogType`	(required)	`s3`, `iceberg-rest`, or `gcs-interop`. Selects the catalog provider.
`executor`	(none — omitting it silently falls back to the deprecated minion path)	Always set `controller`. The minion executor path is legacy and unsupported for the current feature set — snapshot processing, the observability endpoints, ingestion-time segment grouping, and IAM assume-role auth all require the controller path. There is no default that pushes new tables toward `controller`: a hand-written config that omits this key runs on minion without any error or warning. Onboarding sets it automatically, so this only bites hand-written configs.
`enabled`	`true`	Enable/disable the per-table sync.
`forceConfig`	`false`	Bypass external-table config validation errors (RAW encoding, unsupported index types, derived-column rules) and proceed anyway. Use only when you understand why validation is failing.

External Tables read Parquet files only. There is no inputFormat config — the sync engine doesn’t read one, so setting any such key has no effect.

glue is not a supported catalogType. Starting with StarTree Cloud 0.18.0, table validation rejects it with an error that points to the supported setup; earlier releases accept it when the table is created, and sync then fails at runtime. Onboard AWS Glue-backed sources with catalogType=iceberg-rest and the Glue service type under catalog.iceberg-rest.* instead; see AWS Glue: Onboarding via API.

Connection keys

The catalog connection keys are prefixed catalog.s3.* or catalog.iceberg-rest.* (REST URI, service type — glue, s3Tables, unity, or rest (Nessie and other generic Iceberg REST catalogs) — warehouse/prefix, namespace/table, and auth.rest.* / auth.storage.* credentials). The full list with examples is in AWS Glue: Onboarding via API, Unity Catalog: Onboarding via API for Unity-specific auth, or Nessie: Onboarding via API for the generic rest adapter. Two further top-level keys select source storage independently of catalogType: sourceStorageProvider (azure, for an Azure Blob/ADLS source instead of S3) and externalTableAccessPolicy (unity for Unity-vended Azure credentials, or azure-sas for a configured SAS token via catalog.{catalogType}.auth.storage.sasToken / .credentialPrefix). Hive Metastore and Unity Catalog / generic REST catalogs accept both; AWS Glue and S3 Tables don’t. Onboarded today only through SQL DDL — not the Data Portal or REST onboarding flow.

Scheduling & task sizing

Key	Default	Description
`schedule`	—	Quartz cron for sync frequency, e.g. `0 /5 * * ?`.
`desiredSegmentSize`	`500MB`	Reserved for future use — currently has no effect. Each sync run generates one Pinot segment per source Parquet file; there is no size-based segment splitting or merging today.
`taskMaxNumFiles`	`100`	Max source files per task run (minion mode).
`taskMaxDataSize`	`1GB`	Max source bytes per task run (minion mode).
`tableMaxNumTasks`	(unbounded)	Not read by the External Table task generator itself; on ad-hoc `/tasks/schedule` triggers the shared task framework applies it, with an effective default of unbounded. (The Data Export Task reads the same key name with a different default — `10000`.)
`maxParallelOps`	(CPU cores)	Parallelism within a single run.
`continueOnFileError`	`false`	Skip an entire file that fails to process, for any reason — not limited to unreadable files — instead of failing the run. Because it’s not scoped to a specific failure type, it can silently drop more data than “skip an unreadable file” implies. Applies only during a sync run — see the note below. Distinct from `continueOnError`.
`catalog.s3.startAfter`	(unset)	New in 0.16.0. Seeds the lower bound for the very first S3 listing call (`ListObjectsV2`’s `startAfter`), as a bucket-relative object key. Only used before any checkpoint exists — once a checkpoint is recorded, it’s permanently ignored. Takes precedence over `catalog.s3.startFromLexLatestPartition`.
`catalog.s3.maxFilesToList`	`1000`	Max objects fetched per listing call for raw S3/GCS Data Lake sources (the per-run batching knob for `catalogType=s3`/`gcs-interop`; the Iceberg path batches by `iceberg.snapshotProcessing.batchSize` instead).
`catalog.s3.startFromLexLatestPartition`	`false`	Start the first listing from the lexicographically latest partition instead of the beginning of the prefix. Ignored when `catalog.s3.startAfter` is set.

Don’t use catalog.s3.prefix as a way to bound where ingestion starts within a large bucket — prefix is a permanent filter on which objects are ever considered, not a one-time starting cursor. Narrowing it to “skip ahead” permanently excludes everything outside that range, including future files, once the checkpoint reaches the end of the prefix. Use catalog.s3.startAfter instead, which only affects the first run.

continueOnFileError vs. continueOnError — two different configs. continueOnFileError (this ExternalTableSyncTask config) skips an entire file that fails to process, for any reason — not just unreadable ones — so one bad file doesn’t fail the run. continueOnError (an OSS field in the table’s ingestionConfig, not here) skips individual bad rows while reading a file or stream. Both apply only during ingestion — neither one protects a segment that is already ingested. In particular, if a source file is deleted or compacted away upstream after its segment exists, the server fails to serve that segment regardless of either flag; the durable fix for compacting sources is an Iceberg REST catalog.

Snapshot processing (Iceberg)

Key	Default	Description
`iceberg.snapshotProcessing.initialVersionSelector`	`LATEST`	Where to start: `LATEST`, `EARLIEST`, or `SNAPSHOT_ID`.
`iceberg.snapshotProcessing.initialSnapshotId`	—	Snapshot ID to start from (when selector is `SNAPSHOT_ID`).
`iceberg.snapshotProcessing.batchSize`	`10000`	Max new files ingested per run.
`iceberg.snapshotProcessing.maxFilesScannedPerRun`	`100000`	Upper bound on files enumerated per run (≥ `batchSize`).
`iceberg.snapshotProcessing.fileFilter`	(unset — no filter)	New in StarTree Cloud 0.18.0. Ingests only recent data: Iceberg files whose metadata proves they are older than a rolling time window are left out of each snapshot. JSON list with one predicate, e.g. `[{"type":"timeWindow","period":"30d"}]`. Requires `catalogType=iceberg-rest`, `executor=controller`, and `enableSnapshotConsistency=true`. See Time-window file filter.
`iceberg.snapshotProcessing.maxRetries`	`3`	Consecutive failures before a snapshot is marked `FAILED`.
`iceberg.snapshotProcessing.retryFailedSnapshot`	`false`	Keep re-arming a `FAILED` snapshot from its last cursor on every cycle instead of abandoning it after `maxRetries`. See Sync says COMPLETED but no data lands.
`iceberg.snapshotProcessing.lagMaxDiffFiles`	`100000`	Cap on the manifest set-diff used by the exact ingestion-lag calculation (`?includeLag=true&pendingExact=true`). Beyond it, lag degrades to summary-based estimates with an explanatory `message`. Only affects the numbers returned by the lag/status endpoint — it has no effect on ingestion itself.

Checkpointing & schema evolution

Key	Default	Description
`enableCheckpointing`	(always on)	Declared but not currently read — checkpointing is unconditionally enabled for `ExternalTableSyncTask`; this key has no effect.
`checkpointTTLSec`	(unused)	Declared but not currently read for this task type.
`schemaEvolution.enabled`	`false`	Refine the Pinot schema to the source schema before each sync.
`schemaEvolution.failFast`	`false`	Abort (vs. log and continue) when a schema change is rejected.
`schemaEvolution.typeWidening.enabled`	`false`	Apply a source-side `INT`→`LONG` widening instead of rejecting it. `INT`→`LONG` is the only supported widening. Requires `schemaEvolution.enabled=true`; takes effect on the periodic-watcher path only (not snapshot-sync). See Schema Evolution.
`storeNestedSchema.enabled`	`true`	Stamp the source’s nested-schema metadata onto complex (JSON) columns so field names inside `struct` / `array<struct>` / `map<_,struct>` values resolve to your logical names. Only matters for Delta UniForm / Unity Catalog tables that use column mapping — see the note below.

How schema evolution resolves changes (0.16.0+). When schemaEvolution.enabled=true on a catalogType=iceberg-rest source, each sync unions the current schema against the source’s Iceberg field-id/alias history instead of matching by column name alone. This means a source column rename is tracked as an alias rather than dropping and re-adding a column — the existing Pinot column name is preserved. Resolution only covers top-level fields (nested-field renames aren’t resolved). A detected type widening is rejected by default, unless schemaEvolution.typeWidening.enabled=true opts the table into applying it instead — set schemaEvolution.failFast=true if you want a rejection (widening or otherwise) to abort the run instead of being logged and skipped. When fail-fast aborts a run, the status API reports failurePhase: UPDATING_SCHEMA.Type widening (opt-in, StarTree Cloud 0.18.0+). schemaEvolution.typeWidening.enabled=true lets a table adopt a source-side widening instead of rejecting it. INT→LONG is the only supported widening; FLOAT→DOUBLE and anything else are still rejected. It applies only on the periodic-watcher path (an executor=controller, enabled=true table) — snapshot-sync ingestion skips schema evolution entirely and keeps the registered schema unchanged. Widening is safe for External Tables specifically: a segment’s forward index is the backing Parquet file, so adopting the wider type is a metadata-only edit, not a physical rewrite. It defaults to off because applying it opens a transient window, until every segment has reloaded onto the new type, where a query spanning both generations of segments on the widened column can fail (ORDER BY / GROUP BY / DISTINCT) or drop mismatching rows (a plain SELECT) — aggregations like SUM / COUNT are unaffected. Enabling it is a deliberate choice to accept that window during rollout.Decimal precision changes aren’t supported. BIG_DECIMAL (Pinot’s only decimal type) carries no precision or scale of its own, so an Iceberg decimal precision increase produces an identical Pinot schema and the sync doesn’t notice it. The physical Parquet encoding still varies by precision (decimal(9,2) in an INT32, decimal(18,2) in an INT64, wider in a FIXED_LEN_BYTE_ARRAY), and reading across files written before and after the change isn’t validated.For the full list of supported changes, an end-to-end widening example, and common errors, see Schema Evolution.

Nested logical names under column mapping (storeNestedSchema.enabled). Delta UniForm tables created with delta.columnMapping.mode (name or id) don’t store your column names in the Parquet files — each physical column is an opaque col-<uuid>, and the logical name plus a stable field-id live only in table metadata. Top-level column names are always resolved back to logical names, with or without this flag. Names inside a complex value are only resolved when storeNestedSchema.enabled is on (the default) for the table’s ExternalTableSyncTask config; set it to false to opt out, and nested keys are then stored under their physical names, so JSON_EXTRACT_SCALAR(customer, '$.name', ...) returns NULL even though the data is there. Only an explicit, case-insensitive false opts out — an unset, blank, or unrecognized value keeps the default on.The flag applies to every schema-writing path (onboarding, preview, and the schema-evolution union during sync) and is independent of schemaEvolution.enabled. It defaults to on because leaving it off silently materializes the wrong JSON keys for complex columns on column-mapped tables; the only cost of stamping is a larger ZK-persisted schema on tables that don’t need it, and it does nothing for native Iceberg or add_files tables where physical names already equal logical names. Changing it on an already-onboarded table takes effect only on the next schema write (re-preview/re-register, or the next sync if schema evolution is on), followed by a segment reload — resolution happens at read time, so segments don’t need regenerating. A table onboarded before this default flipped gets that one-time schema update and segment reload automatically on its first sync after upgrade, if it has complex columns and no explicit storeNestedSchema.enabled setting. See Unity Catalog — schema options for column mapping.

Tier backend properties (caching & preload)

Set on the S3 tier in tierConfigs[].tierBackendProperties. The onboarding/preview flow sets sensible defaults.

Two-tier scaffold for skipSegmentPreprocess=true (create-time only). If the table config already sets indexingConfig.skipSegmentPreprocess=true when it’s created — through the preview API or CREATE TABLE ... WITH (type='iceberg', ...) — the enrichment scaffolds two tiers instead of the usual single fixed-selector tier: <tableName>_preprocessed_tier (segmentAge=2h) and <tableName>_s3_tier (segmentAge=0s), both keyed on creationTime. They share the same bucket and region but get distinct pathPrefix values (the preprocessed tier appends /preprocessed), so a segment aging from one tier to the other is a real physical move, and matching indexingConfig.tierOverwrites entries are written so segment preprocessing runs on the preprocessed tier and stays skipped on the s3 tier. New segments land on the s3 tier and relocate to the preprocessed tier once they pass the 2-hour threshold. This applies only at create time: adding skipSegmentPreprocess=true to an existing table via PUT /tables/<name>/config does not scaffold the second tier, and turning the flag back off does not remove one already created.

Common

Key	Default	Description
`bucket` / `pathPrefix`² / `region`	(required)	S3 location of the tier storage.
`enable.prefetch.page.cache`	`false`¹	Route this table’s index reads through the page cache.
`preload.enable.index.consolidation`	`false`¹	Pack a segment’s index regions into one file to reduce mmap count on wide tables.
`enable.delegate.v2`	`true`	Use the v2 delegate segment format. Already on by default — onboarding also writes this key, but that has no effect since it was already `true`.
`enable.startree.index`	`false`	Load StarTree index files for this tier. Off by default — while it’s off, a StarTree index built on an external table is generated at index-build time but never used to serve queries. Turn this on if the table has (or will have) a star-tree index.

¹ The onboarding/preview flow sets these to true on newly scaffolded tables (preload.enable.index.consolidation, enable.prefetch.page.cache). Existing tables are never re-enriched — if a table predates these defaults, add the keys manually. ² pathPrefix is not validated. Nothing errors if it’s missing or wrong — files simply land at the bucket root instead of under a per-table prefix. Always set it explicitly so each table’s files get their own folder.

preload.enable, preload.dir, preload.total.size, and preload.load.existing.buffers are not tier/table settings, even though they sound like they belong here next to preload.enable.index.consolidation. Setting them here does nothing.

Preload tuning

Key	Default	Description
`preload.index.keys.override`	(empty, unless the table has an enabled bloom filter — then `.bloom_filter` is added automatically)*	Comma-separated index keys to preload for this table, e.g. `.inverted_index,.range_index`. See Caching and index pinning.
`preload.index.keys.additional`	`*.bloom_filter` on External Tables, written into the tier config when the table config is saved (StarTree Cloud 0.18.0 and later)	Comma-separated index keys preloaded in addition to whatever `preload.index.keys.override` selects. Set to an empty string to preload nothing extra.

The S3 tier also exposes advanced on-demand buffer, mmap, and read-ahead sub-properties (ondemand.*, mmap.*, readAhead.*). These are internal tuning knobs — leave them at defaults unless StarTree support advises otherwise.

Query options

Set per query with SET "key" = 'value'. Where an option is also a tier setting, the query option overrides it for that one query.

Query option	Default	Effect
`enable.prefetch.page.cache`	`false`	Route index reads through the page cache for this query (overrides read-ahead).
`prefetch.projection.queue.size`	`10`	Projection blocks prefetched ahead on a scan queue refill (prefetch depth).
`readAhead.enable`	`false`	Enable read-ahead buffering for index reads on this query.
`skip.remote.table.cache`	`false`	Bypass the Parquet page cache — the cache that holds fetched remote-table data pages in memory/on disk — for both reads and writes. Covers data pages only. To measure a genuinely cold query, also set `skip.columnIndex.cache`; index reads otherwise still come from the local cache and the result will look faster than a real cold run.
`skip.columnIndex.cache`	`false`	Bypass the column index cache for this query. Pair with `skip.remote.table.cache` for a truly cold-cache measurement.
`snapshotVersionByTable`	—	Time-travel: pin the query to specific Iceberg snapshots, as a JSON map `{"table": snapshotId}`. See Time travel.
`concurrentProjectionPrefetch`	`true`	Concurrent per-column prefetch for wide projections (`SELECT *`). Set `'false'` as a kill switch if a wide scan misbehaves.
`pinot.parquet.pagereader.max.in.flight.chunk.reads`	`64`	Per-query cap on concurrent chunk reads. Positive values only.
`pinot.parquet.prefetch.segment.lookahead.enabled`	`false`	Warm upcoming segments’ chunks ahead of time for this query.
`pinot.parquet.prefetch.filter.ahead.enabled`	`false`	Evaluate the query filter ahead of the scanners and warm projection pages for matched rows. Best effort: query execution still applies the authoritative filter. StarTree can disable this cluster-wide, in which case the option has no effect.
`pinot.parquet.prefetch.filter.ahead.depth`	`8`	Max segments filter-ahead filters concurrently for this query. Can lower, not raise, the cluster-wide ceiling.
`pinot.parquet.prefetch.filter.ahead.max.outstanding.futures`	`128`	Per-query cap on outstanding filter-ahead prefetch futures. Can lower, not raise, the cluster-wide ceiling.
`enable.tiered.storage.trace`	`false`	Collect a tiered-storage trace for this query. The trace is written to server logs only — set the standard `trace` query option together with this one if you also want it back in the query response.

Example — bypass the cache for one query:

SET "skip.remote.table.cache" = 'true';
SELECT count(*) FROM nyc_taxi_trips WHERE fare > 100;

Time travel (query-time snapshot pinning)

By default, a query against an Iceberg external table reads the newest fully-synced snapshot, resolved per query at the broker. The snapshotVersionByTable query option pins the query to a specific snapshot instead — the broker routes it to exactly that snapshot’s file set, so you get the data (and row-level deletes) as of that snapshot:

SET "snapshotVersionByTable" = '{"nyc_taxi_trips_OFFLINE": 1234567890123456789}';
SELECT count(*) FROM nyc_taxi_trips;

The value is a JSON map of table name → snapshot ID, so a single query can pin several tables (one entry per table). Table names are canonicalized — nyc_taxi_trips, nyc_taxi_trips_OFFLINE, and mydb.nyc_taxi_trips all resolve to the same entry. The option works on both the single-stage and multi-stage query engines.

Caveats

Snapshot IDs only, no SQL clause. There is no FOR VERSION AS OF / FOR TIMESTAMP AS OF syntax and no timestamp-based variant. Pass the numeric Iceberg snapshot ID (as a JSON number or numeric string) via the query option.
Bounded time-travel window. Only snapshots still in the table’s active snapshot list can be pinned. Snapshot retention keeps the newest iceberg.snapshotProcessing.retention.maxActiveVersions versions (default 2). A configured value below 2 is ignored outright and the default of 2 — also the floor — is used instead. This is not arbitrary Iceberg-history time travel — it is “pick one of the last N snapshots StarTree has synced and still retains.”
Iceberg catalog sources only. Supported for Iceberg REST catalog sources (AWS Glue, S3 Tables, Unity Catalog, generic REST). On raw S3/GCS Data Lake tables the option is silently ignored — file listings have no snapshot concept.
Expired or unknown snapshots fail the query. Pinning a snapshot that is not (or no longer) active returns an error (Iceberg snapshot <id> is not active for table <table>) rather than silently falling back to the latest snapshot. Malformed JSON in the option also fails the query.
No pin = latest ready snapshot. Without the option, each query reads the newest snapshot that is fully synced and cache-ready at that moment. Two queries issued at different times may therefore read different snapshots; pin a snapshot when you need repeatable reads across queries.
On some tables the broker sets it automatically. Every query on a deletion-vector-enabled table runs against a pinned snapshot automatically — and so does every query on a table with snapshot consistency enabled, or one using segment groups. You only set the option yourself to override the default (latest) choice.
Not the same as initialSnapshotId. The table-config keys iceberg.snapshotProcessing.initialVersionSelector / initialSnapshotId (and the DDL snapshot_mode / snapshot_id) choose where ingestion starts when the table is created. They do not affect which snapshot a query reads.
Finding snapshot IDs. GET …/externalTable/status?includeLag=true returns the newest synced snapshot (lag.synced.snapshotId) and the upstream head (lag.upstream.snapshotId) — see Observability. For older retained snapshots, get IDs from the source catalog (e.g. the Iceberg table’s snapshots metadata); there is currently no API that lists all active snapshot IDs.

Get Started

Ingestion

External Tables

Query Data

Manage Data

Observability

Visualize Data

Cluster Operations

Manage Security

Release Notes

Reference

Best Practices and Configs

Best practices

Networking and data transfer costs

Caching and index pinning

Where configs live

Table config — `ExternalTableSyncTask`

Catalog & mode

Connection keys

Scheduling & task sizing

Snapshot processing (Iceberg)

Checkpointing & schema evolution

Tier backend properties (caching & preload)

Common

Preload tuning

Query options

Time travel (query-time snapshot pinning)

Caveats

​Best practices

​Networking and data transfer costs

​Caching and index pinning

​Where configs live

​Table config — ExternalTableSyncTask

​Catalog & mode

​Connection keys

​Scheduling & task sizing

​Snapshot processing (Iceberg)

​Checkpointing & schema evolution

​Tier backend properties (caching & preload)

​Common

​Preload tuning

​Query options

​Time travel (query-time snapshot pinning)

​Caveats

Best practices

Networking and data transfer costs

Caching and index pinning

Where configs live

Table config — `ExternalTableSyncTask`

Catalog & mode

Connection keys

Scheduling & task sizing

Snapshot processing (Iceberg)

Checkpointing & schema evolution

Tier backend properties (caching & preload)

Common

Preload tuning

Query options

Time travel (query-time snapshot pinning)

Caveats