Snowflake Search Optimization Service Trade-Offs

Snowflake's Search Optimization Service (SOS) builds a separate access path that speeds up a narrow set of query patterns against a table, and it does nothing at all for the queries that fall outside that set. SOS is a maintenance process that constructs and keeps up a structure tuned for specific predicate types, and the gains it delivers only apply when a query matches one of those types.

Snowflake's documentation names those patterns directly. SOS targets selective point lookup queries that return one row or a small handful of distinct rows out of a much larger table. It covers substring and regular expression searches run through LIKE, ILIKE, or RLIKE, with one hard constraint: substring matching only works when the literal being searched for runs five characters or longer. It also covers character data and IP address lookups through the SEARCH and SEARCH_IP functions. It extends to semi-structured data too: queries against VARIANT, OBJECT, and ARRAY columns using equality, IN, ARRAY_CONTAINS, ARRAYS_OVERLAP, full-text SEARCH, substring or regex matching, and missing-value checks all qualify. Structured ARRAY, OBJECT, and MAP columns get coverage as well, for equality, IN, and substring predicates run against STRING fields inside them. A set of geospatial functions for GEOGRAPHY values closes out the list.

Access to the service itself comes with a licensing floor: SOS runs only on Enterprise Edition and above. Teams running Standard Edition cannot turn it on at all, regardless of how well their workload might otherwise fit the pattern list above.

Once you enable it on a qualifying column, the service works as a background maintenance process. Turning it on does not block reads or writes against the table while the access path gets built, but it also does not produce any query acceleration until that build finishes. Progress on the build is visible through the search_optimization_progress column in SHOW TABLES output, which gives engineers a concrete way to check whether a table is actually ready to benefit before they expect speedups to show up.

SOS only pays off when the queries that matter to your system actually fall inside that pattern list. A table full of broad analytical scans, multi-row aggregations, or filters that don't match the five supported categories gets no benefit from SOS at all, no matter how large or how slow it is. Before any cost question gets asked, the query pattern has to be checked against that list first.

The two cost streams SOS creates the moment it is enabled

Turning SOS on starts two separate, ongoing cost streams at the same moment, and both keep running independent of how often, or whether, anyone actually issues a query that benefits from the service. Neither cost depends on query volume against the optimized table.

On the storage side, Snowflake's own cost documentation says you should expect the search access path to need close to one-quarter of the original table's size in additional storage. The driving variable is cardinality. A column with a huge number of distinct values, sitting on a table that's already wide, is the most expensive combination for storage, because the access path has to track far more distinct entries than a low-cardinality column would need.

On the compute side, the same documentation describes a per-second billing model at a standard serverless rate, charged at a multiplier above the base serverless compute rate, the same multiplier Snowflake applies to automatic clustering and to materialized view maintenance. SOS cannot. It runs continuously once enabled, so it consumes credits until an engineer explicitly disables it, not until some idle timeout kicks in.

Maintenance compute scales roughly with how much data gets ingested or modified in the underlying table. So the size of the ongoing compute bill is tied directly to how much churn the table sees, a detail that becomes central in the next section.

The initial build itself scales with the size of the table being enrolled. No query sees any acceleration until that build is done. The clock on cost starts well before the clock on benefit does.

Write-heavy tables and the cost of a latency win

High churn combined with SOS is the single scenario most likely to turn a performance win into a net financial loss, because the maintenance cost keeps climbing with every write to the table while the latency benefit for any given query stays fixed. If a table gets rewritten constantly, the search access path has to rebuild constantly, and every rebuild bills compute at the elevated serverless rate described above, whether or not a single accelerated query runs in that window.

The failure plays out in a predictable sequence. The team checks the bill weeks later and finds a steady, climbing compute charge that has nothing to do with how often the optimized queries actually ran.

Two table patterns reliably trigger this outcome. Streaming ingestion tables take a continuous stream of inserts, so they keep the access path in a near-permanent rebuild state. Tables trimmed by frequent small deletes, hourly rather than daily, generate the same effect from the opposite direction: every delete operation triggers maintenance work, and running that trim hourly multiplies the maintenance events compared to running it once a day.

Snowflake recommends two concrete practices to blunt this. For tables that get periodically reclustered in full, drop the SEARCH OPTIMIZATION property before the full recluster runs and re-add it afterward, instead of leaving it active through an operation that rewrites large portions of the table at once.

Any engineer who has already decided SOS sounds like the right call for a given table should check churn rate first, before configuration, not after the first invoice arrives.

Where SOS delivers clear value

SOS earns its ongoing cost when three conditions hold at the same time: the table is large, the queries that matter are highly selective, and low lookup latency has a real effect on users or downstream systems. All three have to be true together. If even one of these conditions fails to hold, the case for paying continuous storage and compute cost weakens considerably.

Each condition does distinct work in that test. Table size matters because the performance gap between a full scan and a pruned scan only becomes meaningful once a table has enough micro-partitions for the pruning structure to skip a large share of them; on a small table, that gap is negligible, and SOS has little to offer. And latency has to matter economically: a query that users or systems tolerate waiting on, run rarely enough that speed is not the point, does not generate enough value to offset a compute bill that runs continuously regardless of query frequency.

Snowflake's own documentation points to three business contexts that reliably satisfy all three conditions together. Data scientists who explore large volumes of data to isolate specific subsets make up the third.

Where those conditions align, the upside is substantial. The service can cut query times by a wide margin for patterns that qualify, and Panther Labs recorded point-lookup speedups of more than 100x, a two-order-of-magnitude improvement, after deploying it against workloads that fit this profile. That result describes a narrow scenario, not a general-purpose speedup. It describes a workload built around highly selective point lookups on a large table where latency had direct consequences. It does not describe a general-purpose speedup that any table or any query pattern should expect to see.

The three-condition test turns a judgment call into something closer to a checklist: size, selectivity, and latency sensitivity, all three, before the service gets enabled on a single column.

SOS versus clustering versus materialized views: choosing the right tool before spending anything

Choosing SOS without first comparing it against clustering and materialized views is the architectural mistake most likely to produce wasted spend, because each of these three tools carries its own cost profile and fits a different query pattern that the others do not replicate. Picking the wrong one does not just underperform. It bills.

Clustering works by physically reordering a table so that rows matching common predicates sit close together on disk, so it improves pruning for any query that filters on the clustering key, including broad analytical scans that touch a large share of the table. SOS takes a different approach: it builds a separate access path that the query optimizer consults, without touching the table's physical layout. That makes SOS the right choice when a workload needs fast lookups but the physical layout can't or shouldn't be committed to a single clustering dimension, or when several distinct selective lookup patterns need to coexist on the same table at once.

The decision rule that falls out of this contrast is straightforward. You can run the two tools together on the same table, but combining them on a high-churn table compounds the maintenance cost itself, rather than reducing it, because clustering maintenance and SOS maintenance both scale with write volume on the same underlying data.

Materialized views solve a different problem. SOS does not precompute anything. It helps when the base table still makes sense as the thing being queried directly, but the filter applied to it is too selective for a full scan to handle efficiently. The distinction comes down to what the query is trying to do: finding a small subset of rows right now favors SOS, while precomputing a transformed result so a downstream query runs cheaper favors a materialized view or a dynamic table instead.

Snowflake's documentation also places Query Acceleration Service in this same comparison set, alongside materialized views in both clustered and unclustered form. Each of these tools addresses a different bottleneck, and each deserves a look before a team defaults to SOS simply because it sounds like the obvious answer to a slow query.

Column-scoped configuration and the economics of SOS

The ON clause changed what SOS costs in practice: you can enroll only the columns and predicate types your actual queries use, instead of paying to build and maintain an access path across every eligible column in a table. That single syntactic choice is the clearest lever available for controlling SOS spend once the decision to use it has already been made.

The contrast between the two configuration styles is direct. Running ALTER TABLE orders ADD SEARCH OPTIMIZATION with no ON clause enrolls every eligible column in the table, building and maintaining a search access path for all of them, whether or not any of those columns ever appears in a selective query. Running ALTER TABLE orders ADD SEARCH OPTIMIZATION ON EQUALITY(customer_id, status) instead scopes the access path down to the named columns and to a specified predicate type, chosen from EQUALITY, SUBSTRING, GEO, or FULL_TEXT. Snowflake's documentation recommends the scoped form as standard practice.

Deliveroo's experience shows what that change is worth in practice. Once column-scoped optimization became available, Deliveroo cut its SOS maintenance cost sharply compared to the full-table optimization it had been running before, and it kept the same performance benefit on the queries that mattered, for a fraction of the ongoing spend. If your team is running an older, unscoped ADD SEARCH OPTIMIZATION configuration inherited from earlier guidance, treat it as a legacy setup worth auditing, since it is likely paying for columns nobody queries selectively.

Predicate-type choice carries its own cost implications. EQUALITY and IN predicates represent the default high-value case, suited to point lookups on high-cardinality identifier columns like customer IDs or order numbers. GEO scoping belongs only on tables where geospatial predicates actually dominate the query pattern, not as a default addition alongside other predicate types.

Estimating and monitoring costs before and after enabling SOS

Running SYSTEM$ESTIMATE_SEARCH_OPTIMIZATION_COSTS before turning SOS on for any table, and especially before scoping it to SUBSTRING or VARIANT columns, is the single habit that separates teams that deploy this service profitably from teams that find out what it costs only when the monthly bill arrives. The function gives you an estimate of build and maintenance cost ahead of time, against the specific columns and predicate types you are considering, so you don't have to learn those numbers from production billing after the fact.

That estimate matters most precisely where the cost risk runs highest: substring and VARIANT equality optimization, which Snowflake's own documentation flags as disproportionately expensive to build and maintain compared to straightforward equality predicates on structured columns. If you run the estimate before you enable the service, you turn an open-ended cost exposure into a number you can check against the expected query benefit before any storage or compute charges begin accruing.

Monitoring doesn't end once the service is live. The search_optimization_progress column in SHOW TABLES output shows whether the access path has finished building, so an engineer can tell whether the lack of observed speedup on a given query reflects an incomplete build or a genuine mismatch between the query pattern and what SOS accelerates. Checking that signal before concluding the service isn't working avoids a false negative that might otherwise lead a team to abandon a configuration that simply hadn't finished building yet.

Combined with the churn-rate check from earlier and the three-condition test for query and table fit, pre-estimation and post-enablement monitoring round out the operational discipline this service demands. SOS rewards teams that measure before they commit and verify after they deploy, and it penalizes, steadily and invisibly, teams that treat it as a switch to flip and forget.