Snowflake Search Optimization Service: Point Lookups Without the Full Scan

Some tables have two lives. Ninety-nine percent of the time they feed aggregations that scan big date ranges, and natural load order serves that fine. Then a support engineer needs one order by its ID, right now, and that single-row lookup scans four terabytes because an equality predicate on a UUID prunes nothing.

Search Optimization Service exists for exactly that second life. It builds a persistent data structure — Snowflake calls it a search access path — that knows which micro-partitions contain which values. A point lookup consults the path, touches the three partitions that matter, and returns in seconds instead of minutes. No physical reordering, no change to the table, no change to the query.

Like clustering keys, it is a standing purchase with a meter running. Unlike clustering, it handles the high-cardinality columns clustering handles worst. Knowing which tool serves which predicate is most of the skill.

How the access path works

Micro-partition pruning normally relies on min/max ranges per partition. For a UUID column, every partition’s range spans nearly the whole keyspace, so no partition can be excluded — the mechanics are in the pruning post. The search access path replaces range metadata with something closer to an inverted index: for a given value, which partitions could contain it.

flowchart LR
  q["WHERE order_uuid = 'a3f...'"] --> path["Search access path"]
  path --> hit["3 candidate partitions"]
  hit --> scan["Scan 3 partitions, not 40,000"]
  maint["Serverless maintenance service"] --> path
  churn["New loads and DML"] --> maint

The access path answers "which partitions could hold this value" so the scan never starts wide.

A serverless service builds the path when you enable the feature and keeps it current as DML lands. Queries use it automatically when the optimizer sees a supported predicate — there is no hint, no query change, and no way to force it. That last part matters: if the optimizer estimates the lookup is not selective enough, it scans anyway, and you paid for a path that run did not use.

Enabling it: columns, not tables

The default form enables optimization for every eligible column. Do not do that. Storage and maintenance scale with what you index, and most columns will never see a point lookup:

-- The scoped form. This is almost always what you want.
alter table analytics.silver.orders
  add search optimization on equality(order_uuid, customer_email);

-- Substring and regex hunting on a text column
alter table analytics.bronze.app_logs
  add search optimization on substring(raw_message);

-- Point lookups inside a VARIANT payload
alter table analytics.bronze.events
  add search optimization on equality(payload:device_id);

show search optimization on analytics.silver.orders;

ON EQUALITY covers =, IN, and equality predicates in joins. ON SUBSTRING covers LIKE '%needle%' and gets you out of the worst full-scan pattern in log tables. VARIANT path support means you can serve lookups on semi-structured columns without materializing them first — one of the few times I index a VARIANT instead of extracting the column.

Before any of this, price it. Snowflake will estimate the build cost, the ongoing maintenance, and the storage before you commit a credit:

select system$estimate_search_optimization_costs(
  'analytics.silver.orders',
  'EQUALITY(order_uuid, customer_email)'
);

I treat this the way I treat a --dry-run. The estimate returns build credits, monthly maintenance credits at current churn, and added storage. If the support-lookup workload it serves is a handful of queries a day on a small warehouse, the estimate often tells you the honest answer: let those queries scan.

What it costs, and where to watch it

Three meters run once you enable the service.

Build cost: a one-time serverless charge proportional to table size. On multi-terabyte tables this is real money — schedule it like a migration, not a worksheet experiment.

Maintenance: serverless credits proportional to DML churn. An append-only table is cheap to maintain. A table with scattered updates rewrites partitions constantly, and every rewritten partition means access path work. Same churn sensitivity as clustering, same discipline:

select
  table_name,
  to_date(start_time) as day,
  sum(credits_used) as credits
from snowflake.account_usage.search_optimization_history
where start_time > dateadd('day', -30, current_timestamp())
group by 1, 2
order by credits desc;

Storage: the access path is stored alongside the table, typically a 10-25 percent overhead depending on data types and how many columns you scoped. It shows up in TABLE_STORAGE_METRICS under the search optimization columns, and it is the quietest of the three costs — nobody notices storage until the cost review does.

The payback side is warehouse time saved on the lookups. Measure it the same way as any performance claim: pull the target queries from QUERY_HISTORY before and after, compare partitions scanned and elapsed time in the Query Profile. A lookup that went from 90 seconds on a Large to 2 seconds is easy to defend. If the profile shows the access path was not used, scope was wrong or the predicate is not supported — find out which before the monthly maintenance invoice does.

Search Optimization vs clustering keys

The two features answer different predicate shapes, and the decision is mostly mechanical:

flowchart TD
  pred["What does the slow predicate look like?"] --> eq{"Equality on high-cardinality column?"}
  eq -->|yes| sos["Search Optimization ON EQUALITY"]
  eq -->|no| range{"Range or moderate-cardinality filter?"}
  range -->|yes| ck["Clustering key"]
  range -->|no| sub{"LIKE substring or regex?"}
  sub -->|yes| sos2["Search Optimization ON SUBSTRING"]
  sub -->|no| neither["Neither. Fix the SQL or accept the scan."]

Predicate shape picks the tool. Table size decides whether either is worth buying.

Clustering physically sorts the table, so it accelerates ranges, inequalities, and anything that benefits from locality — and it helps every query that filters on the key. Search Optimization leaves the layout alone and accelerates specific predicate types on specific columns. They coexist fine: I run tables clustered by event_date with search optimization on session_id, and each serves its own workload without disturbing the other.

Two cases where Search Optimization is the only real option: columns whose cardinality makes clustering economically absurd, and tables that already have a clustering key serving a more important workload. One case where clustering wins outright: the lookup column also appears in range scans, because the physical sort serves both while the access path serves only the lookups.

Pitfalls

Enabling it table-wide. The unscoped ADD SEARCH OPTIMIZATION indexes everything eligible. Storage and maintenance track column count. Scope to the columns with a named workload behind them.

Skipping the estimate. ESTIMATE_SEARCH_OPTIMIZATION_COSTS exists so the build cost is not a surprise. On a big table, the surprise is large.

Expecting it to help analytics. Aggregations over ranges do not use the access path. If the slow query scans a month of data, this feature does nothing for it.

Forgetting churn. A high-DML table pays maintenance every day. If the table is rebuilt nightly by a dbt incremental model gone wrong — full refresh every run — the path is rebuilt with it.

Assuming the optimizer will always use it. Low-selectivity predicates fall back to scanning. Verify usage in the profile before declaring victory.

Leaving it on after the workload dies. The support tool got rebuilt, lookups stopped, the meter kept running. Quarterly review of SEARCH_OPTIMIZATION_HISTORY against actual usage catches it.

FAQ

Does Search Optimization speed up joins? It can, when the join filters to a small set of values on the indexed column — effectively a batched point lookup. Broad joins across large key ranges see no benefit.

Does it work on views? The optimization lives on the base table, and queries through views benefit when their predicates reach the indexed columns. You cannot enable it on the view itself.

How long does the initial build take? Proportional to table size, running in the background without blocking queries. Multi-terabyte tables take hours. Queries simply do not benefit until the path is built; check build progress in SHOW TABLES search optimization progress column.

Can I use it on a table with a masking policy? Yes, the access path operates below the policy layer and results still pass through masking as usual. The lookup gets fast; the governance stays intact.

Is there a warehouse size interaction? The lookup still runs on your warehouse, but touching three partitions instead of forty thousand means an X-Small often suffices where a Large used to struggle. Downsizing the lookup warehouse is part of the payback.

What this means for data engineers

Search Optimization is the right buy when someone needs single rows out of big tables and the predicate is equality or substring on a high-cardinality column. It is the wrong buy for analytics, for small tables, and for columns nobody actually looks up.

The workflow that keeps it honest: name the workload, run the cost estimate, scope to exact columns, verify in the Query Profile that the path gets used, and put the maintenance history in the same monthly review as clustering credits.

Point lookups and analytical scans are different physics. Buy the access path for one, keep load order or a clustering key for the other, and let each meter justify itself.