Breadcrumbs

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:

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:

SQL
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:

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