TopolearnSome workloads don't prune with clustering alone, and these services exist exactly for them.
Clustering helps range filters on a few columns. Two Enterprise-edition services cover other patterns, each with background maintenance billed as serverless credits plus extra storage.
Search optimization builds a persistent search access path, a kind of index, so highly selective lookups can skip most micro-partitions:
- equality and
INfilters on high-cardinality columns (find one customer's rows among billions), - substring and regex searches (
ON SUBSTRING(col)), - lookups inside VARIANT fields and on geospatial values.
Enable it per column and method: ALTER TABLE t ADD SEARCH OPTIMIZATION ON EQUALITY(customer_id). It suits queries returning a few rows; it does little for large scans.
Materialized views store a pre-computed query result that Snowflake keeps current in the background. Queries on the view are always consistent with the base table: if maintenance is behind, Snowflake combines the stored result with the newer base data. The optimizer can even rewrite queries against the base table to use a matching view. Limits: a materialized view queries a single table with no joins, and supports a restricted set of functions and aggregates.
Choose by pattern: repeated heavy aggregations on one table call for a materialized view; needle-in-haystack filters call for search optimization; multi-table transformations call for dynamic tables. Check the maintenance cost with SEARCH_OPTIMIZATION_HISTORY and MATERIALIZED_VIEW_REFRESH_HISTORY before keeping them.
Example
-- Point lookups on a high-cardinality column
ALTER TABLE events ADD SEARCH OPTIMIZATION ON EQUALITY(customer_id), SUBSTRING(url);
SHOW TABLES LIKE 'EVENTS'; -- search_optimization: ON, progress: 100 when built
SELECT * FROM events WHERE customer_id = 'c_8f2a91'; -- prunes most partitions
-- Pre-aggregate a heavy, repeated query on one table
CREATE MATERIALIZED VIEW daily_event_counts AS
SELECT TO_DATE(event_ts) AS day, event_type, COUNT(*) AS n
FROM events
GROUP BY TO_DATE(event_ts), event_type;
-- Always consistent with events, even right after new inserts
SELECT * FROM daily_event_counts WHERE day = CURRENT_DATE();Remember
- Search optimization accelerates selective equality, substring and VARIANT lookups
- Materialized views keep single-table aggregates fresh and always consistent
- Both add serverless maintenance and storage costs: measure them
Watch out for
- Creating a materialized view on a table that changes constantly, where maintenance credits outweigh savings.