STORAGE.SEARCH_OPTIMIZATION_OPTIONS

Target database: Snowflake, Google BigQuery

STORAGE.SEARCH_OPTIMIZATION_OPTIONS option enables:

• Snowflake Search Optimization Service method specification for an entity.

• Google BigQuery Search Index options specification 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

• SEARCH_INDEX_KEY

Usage

STORAGE.SEARCH_OPTIMIZATION_OPTIONS: String

Default: empty

Requires the STORAGE.SEARCH_OPTIMIZATION physical option to be set as true

Notes

Snowflake:

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

• With STORAGE.SEARCH_OPTIMIZATION_OPTIONS one can specify wanted search methods to be used in the search optimization on entire table (https://docs.snowflake.com/en/sql-reference/sql/alter-table#label-alter-table-searchoptimizationaction-add )

  • for example EQUALITY(*),SUBSTRING(*),GEO(*)

  • Snowflake will choose the suitable columns per search method

Google Bigquery:

• Search indexing can be enabled for tables.

• With STORAGE.SEARCH_OPTIMIZATION_OPTIONS one can specify wanted general options to be used in the search index, for example index_granularity or data_types

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 using SUBSTRING as search method

Setting STORAGE.SEARCH_OPTIMIZATION to true for a table (e.g. F_TRIP) and STORAGE.SEARCH_OPTIMIZATION_OPTIONS as SUBSTRING(*)

STORAGE.SEARCH_OPTIMIZATION: true
STORAGE.SEARCH_OPTIMIZATION_OPTIONS: SUBSTRING(*)

DDL generated by Agile Data Engine:

ALTER TABLE publish.F_TRIP ADD SEARCH OPTIMIZATION ON SUBSTRING(*);

Google BigQuery: Enabling Search Index for a table for datatypes STRING and INT64

Setting STORAGE.SEARCH_OPTIMIZATION to true for a table (e.g. F_TRIP) and STORAGE.SEARCH_OPTIMIZATION_OPTIONS as data_types = ['STRING', 'INT64']

STORAGE.SEARCH_OPTIMIZATION: true
STORAGE.SEARCH_OPTIMIZATION_OPTIONS: data_types = ['STRING', 'INT64']

DDL generated by Agile Data Engine:

CREATE SEARCH INDEX SIDX_PUBLISH_F_TRIP ON publish.F_TRIP (ALL COLUMNS) OPTIONS(data_types = ['STRING', 'INT64']);