STORAGE.SEARCH_OPTIMIZATION

Target database: Snowflake, Google BigQuery

STORAGE.SEARCH_OPTIMIZATION option enables:

• Snowflake Search Optimization Service for an entity.

• Google BigQuery Search Index for an entity.

Search optimization/indexing is aimed to improve performance of selective point lookup queries on large tables.

See also:

• Understanding the Search Optimization Service (Snowflake documentation)

• Search indexes with column granularity in BigQuery (Google Bigquery documentation)

• Work with text analyzers (Google BigQuery documentation)

• STORAGE.SEARCH_OPTIMIZATION_OPTIONS

• SEARCH_INDEX_KEY

Usage

STORAGE.SEARCH_OPTIMIZATION: Boolean

Default: false

Notes

Snowflake:

• Search optimization is available in Snowflake Enterprise Edition (or higher).

• Search optimization can be enabled for tables.

Google Bigquery:

• Search indexing can be enabled for tables.

• Search index can be set on global or column granularity.

Google Bigquery has index handling limitations, those might affect on active feature modification phase (https://cloud.google.com/bigquery/quotas#index_limits).

In August 2025:

• single project can have 500 CREATE INDEX operations done per day per region

• single table can have 20 CREATE/DROP INDEX operations done per day

Examples

Snowflake: Enabling Search Optimization for a table

Setting STORAGE.SEARCH_OPTIMIZATION to true for a table (e.g. F_TRIP):

STORAGE.SEARCH_OPTIMIZATION: true

DDL generated by Agile Data Engine:

ALTER TABLE publish.F_TRIP ADD SEARCH OPTIMIZATION;

Google BigQuery: Enabling Search Index for a table

Setting STORAGE.SEARCH_OPTIMIZATION to true for a table (e.g. F_TRIP):

STORAGE.SEARCH_OPTIMIZATION: true

DDL generated by Agile Data Engine:

CREATE SEARCH INDEX SIDX_PUBLISH_F_TRIP ON publish.F_TRIP (ALL COLUMNS);