What does Snowflake's search optimization service speed up, and when is it the wrong tool?

## The problem it solves Snowflake prunes micro-partitions using per-column min/max metadata. That works beautifully when the filtered column correlates with how rows are physically arranged — typically the load order, usually time. It works badly for a needle-in-haystack lookup on a high-cardinality column that is scattered everywhere: if every micro-partition contains some range of `user_id` values that spans your target, min/max cannot exclude any of them, and a query returning three rows scans the whole table. The search optimization service addresses exactly that case by maintaining a separate persistent data structure — a search access path — that maps values to the micro-partitions that can contain them, so a selective predicate resolves to a handful of partitions. ## Enabling it ```sql -- Whole table, default coverage ALTER TABLE events ADD SEARCH OPTIMIZATION; -- Targeted: cheaper, narrower ALTER TABLE events ADD SEARCH OPTIMIZATION ON EQUALITY(user_id, device_id); ``` Coverage can be scoped by predicate type — `EQUALITY` for `=` and `IN`, `SUBSTRING` for substring and pattern matching, `GEO` for geospatial predicates — and to particular columns, including fields inside semi-structured data. Narrowing coverage reduces both the storage and the maintenance the service has to do, so specifying columns is usually better than blanket enablement on a wide table. It is an Enterprise Edition (and above) feature. ## When it wins - A very large table. - A predicate that is **highly selective** — a few rows out of many millions. - On a **high-cardinality** column whose values are spread across the whole table. - Queried **often enough** that repeated savings pay back a permanent maintenance bill. Typical shapes: customer-lookup APIs served from the warehouse, support tools that fetch one entity's rows, deduplication or existence checks on a natural key, needle lookups inside `VARIANT` fields. ## When it loses - **Analytical queries.** A `GROUP BY` over a month of data reads most of the relevant partitions regardless; a search access path cannot help and its maintenance still bills. - **Low-cardinality columns.** A status column with six values matches a large fraction of partitions, so there is nothing to exclude. - **Small tables.** The scan is cheap already; the maintenance is not. - **Rarely-run lookups.** Maintenance is continuous while the queries are occasional, which is a losing trade. - **When the filter column is already what the data is ordered by.** Pruning already works; adding a second mechanism just adds cost. ## The cost model Two ongoing costs: **storage** for the access path, and **serverless maintenance credits** to keep it current as the table changes. Both scale with table size and churn. Snowflake provides `SYSTEM$ESTIMATE_SEARCH_OPTIMIZATION_COSTS` to size the build and maintenance before you commit, and `ACCOUNT_USAGE` carries search-optimization consumption so you can verify after. This is a feature you should measure into and measure out of; enabling it across a schema "to be safe" is a reliable way to grow a bill quietly. ## How it relates to the other accelerators Snowflake offers three broad ways to make a query read less or recompute less, and they answer different questions. **Data layout** helps range and prefix filters on the dimension the data is organised by; the search optimization service helps needle lookups on dimensions it is not organised by; a **materialized view** helps when the cost is recomputation of an aggregate rather than finding rows. A table can genuinely want more than one, but each carries its own permanent maintenance bill, so "which one does this workload actually need?" is the question, not "which shall we add?". ## Verifying the benefit Measure before and after with `USE_CACHED_RESULT = FALSE` so the result cache does not flatter the second run, and read the Query Profile's pruning statistics: the evidence that it worked is **partitions scanned collapsing** relative to partitions total, not merely a faster wall clock. If the ratio has not moved, the predicate or the column choice is not what the service covers.