5.4 Search Optimization, Materialized Views, and Query Best Practices

Search Optimization Service

The Search Optimization Service (SOS) is a table-level property that builds and maintains a search access path — an optimized data structure separate from the table — to dramatically speed up highly selective point-lookup queries (equality and IN predicates, and certain substring/geospatial searches). It is ideal for needle-in-a-haystack queries on very large tables, such as WHERE customer_id = 'X' returning a handful of rows from billions.

You enable it with ALTER TABLE t ADD SEARCH OPTIMIZATION (optionally scoped to specific columns/methods). Costs come in two forms: storage for the access path (often 1/4 the table size or more) and serverless compute to build and maintain it as data changes. SOS is not for analytical scans that read most rows — there, clustering or a bigger warehouse helps instead.

Clustering vs Search Optimization is a classic exam distinction: clustering improves pruning for range and ordered filters (dates, ranges) on data you can physically order; search optimization targets selective equality lookups, including on columns that have no useful sort order. They can coexist on one table.

SOS is serverless — it needs no running warehouse to maintain its access path, and it is enabled per table (with optional column/method scoping such as EQUALITY, SUBSTRING, or GEO). Because it adds storage and maintenance cost, reserve it for genuinely large tables where selective lookups are frequent; on small or rarely-queried tables the overhead is not justified.

Materialized Views vs Regular Views

A regular view stores only a query definition; it runs the underlying query every time it is referenced — no precomputed data, no extra storage, always current. A materialized view (MV) stores the precomputed result on disk and is automatically and transparently maintained by Snowflake as the base table changes, so queries read the ready answer instead of recomputing.

MVs accelerate expensive, frequently repeated aggregations or projections over a single large, slowly changing table. But they carry strict limitations:

Because maintenance costs credits on every base-table change, MVs suit tables that are queried far more often than updated. If you need joins or windowing, a materialized view is the wrong tool — use a scheduled CTAS table or a stream/task pipeline instead. Snowflake also has automatic query rewrite: the optimizer may transparently use an MV even when a query targets the base table.

Query Best Practices

Writing efficient SQL reduces both runtime and credits:

• Use LIMIT when exploring or sampling so you don't scan and return full result sets; combine with SAMPLE for statistical subsets.
• Avoid unnecessary ORDER BY — sorting a full result set is expensive and can cause spilling; only sort when the output order matters.
• Filter before you join and put the most selective predicates first so fewer rows enter joins; let pruning eliminate partitions early.
• Select only the columns you need — Snowflake is columnar, so SELECT * reads every column's micro-partition data, inflating I/O.
• Mind join order and cardinality: join small, filtered sets to large tables, and ensure join keys are correct to avoid exploding (near-cartesian) joins that spill.
• Right-size the warehouse: start small, scale up only when a query spills or runs long, and set AUTO_SUSPEND low so idle warehouses stop billing.

These habits, plus the cache layers from 5.2, mean many queries never touch a warehouse at all or finish far faster — the cost-aware behavior the exam consistently rewards.

Choosing the Right Acceleration Feature

The exam loves to put clustering, search optimization, and materialized views side by side and ask which fits. Use access pattern to decide:

Three quick rules of thumb: (1) if the query returns few rows by an equality predicate, think search optimization; (2) if the query filters a range on a giant table and prunes badly, think clustering; (3) if the query is an expensive aggregation run over and over, think materialized view — unless it needs a join, in which case fall back to a CTAS table refreshed by a task.

A frequent trap is reaching for a bigger warehouse when a structural feature is the right answer. Scaling up burns more credits every run; a clustering key, search-optimization path, or materialized view pays a one-time/maintenance cost and then makes every matching query cheaper. Conversely, do not over-engineer: on a small table that already prunes well and runs in milliseconds, none of these features is warranted, and "do nothing / leave as is" can be the correct exam answer.