Search Optimization burning credits on tables with almost no point lookups

The maintenance meter behind the feature

Search Optimization keeps a persistent access path alongside your table, and Snowflake maintains it with serverless compute every time the underlying data changes. Those maintenance credits accrue whether or not any query benefits. The feature pays for itself on tables where a meaningful share of queries are highly selective lookups; on a table scanned in bulk by ELT and dashboards, the maintenance line is a recurring charge with no offsetting speedup.

How the no-benefit verdict is reached

Two 30-day sums, both from ACCOUNT_USAGE. First, snowflake.table.sos_credits must be greater than 0, meaning the table is actually spending maintenance credits, sourced from SEARCH_OPTIMIZATION_HISTORY. Second, snowflake.table.point_lookup_queries must be below 10. A point lookup is defined mechanically: a query whose partitions_scanned / partitions_total ratio came in under 0.001, found by joining QUERY_HISTORY to ACCESS_HISTORY on query_id and unnesting the base objects each query touched. Fewer than 10 such queries in a month means the access path is essentially never exercised.

Audit the spend per table

SELECT database_name || '.' || schema_name || '.' || table_name AS table_uid,
       SUM(credits_used) AS sos_credits_30d
FROM SNOWFLAKE.ACCOUNT_USAGE.SEARCH_OPTIMIZATION_HISTORY
WHERE start_time >= DATEADD('day', -30, CURRENT_TIMESTAMP())
GROUP BY 1
ORDER BY 2 DESC;

Any table appearing here that you cannot name a lookup workload for is a candidate.

The heuristic, stated honestly

Partition-scan ratio is a proxy, not ground truth. Search Optimization also accelerates substring and geospatial predicates whose queries may scan more than 0.1% of partitions and still benefit, so a table serving those could be flagged despite genuine value. The join also depends on ACCESS_HISTORY, an Enterprise-edition view that lags up to 3 hours, and the maintenance history lags similarly, so verify against a full recent month before acting.

Removing the property

ALTER TABLE my_db.app.events DROP SEARCH OPTIMIZATION;

Maintenance charges stop immediately. Be deliberate: re-enabling later rebuilds the entire access path from scratch, which itself costs credits proportional to table size, so confirm with the table’s consumers that no selective-lookup workload is planned. If only one column ever serves lookups, a narrower middle path is dropping the table-wide property and re-adding Search Optimization scoped to that single column with an ON clause. Maintenance then tracks one access path instead of every eligible column, and the monthly credit line shrinks accordingly.