Skip to content
Tutorials Center
My learning

Topics

Lesson 25 of 38 ยท Warehouses and Performance

Search Optimization, Materialized Views and Query Acceleration

Compare Snowflake's search optimization service, materialized views and query acceleration service: what each speeds up, what it costs and when to choose it.

Clustering helps queries that read ranges of a big table. Other query shapes need other tools. Snowflake offers three performance services that work behind the scenes and that you pay for with serverless credits and storage: the search optimization service, materialized views, and the query acceleration service. This lesson shows what each is for so you can pick the right one, and builds a runnable model of the materialized view idea.

All three are Enterprise edition features.

Search optimization service

Pruning is excellent for ranges but weak for point lookups in huge tables, such as "find the one row with this order_id" or "find log lines containing this error code". The search optimization service builds an extra persistent search access path (a structure of which micro-partitions can hold which values), so Snowflake can jump straight to the few partitions that contain the value.

SQL
ALTER TABLE logs ADD SEARCH OPTIMIZATION;

-- or only for chosen columns and predicate types
ALTER TABLE logs ADD SEARCH OPTIMIZATION
  ON EQUALITY(order_id), SUBSTRING(message);

-- inspect the state and the estimated cost before enabling
SELECT SYSTEM$ESTIMATE_SEARCH_OPTIMIZATION_COSTS('logs');
SHOW TABLES LIKE 'logs';   -- search_optimization columns show progress

It helps with:

  • equality and IN lookups on high-cardinality columns,
  • LIKE, ILIKE, RLIKE and substring searches,
  • lookups in VARIANT fields, geospatial predicates and some joins.

It does not help broad scans that return a large part of the table. The cost is storage for the access path and serverless credits to build and maintain it, so enable it on selective-query tables, not everywhere.

Materialized views

A materialized view (MV) stores the result of a query as physical data, and Snowflake keeps it up to date automatically as the base table changes. Queries that match the view read the small precomputed result instead of recomputing a large aggregation.

SQL
CREATE MATERIALIZED VIEW daily_sales AS
SELECT order_date, region, SUM(amount) AS revenue, COUNT(*) AS orders
FROM sales
GROUP BY order_date, region;

SELECT * FROM daily_sales WHERE region = 'EMEA';

Rules that matter:

  • An MV is defined on one table (no joins) and supports a restricted set of SQL: simple aggregates, filters and projections, but not window functions or non-deterministic functions.
  • Maintenance runs on serverless compute and is billed in credits; MV storage is billed too.
  • The optimizer can use the MV transparently, even if your query names the base table, when the view can answer it.
  • Always-current: queries see data that matches the base table, because Snowflake merges any not-yet-refreshed changes at query time.

Use MVs for expensive, repeated aggregations on a table that changes slowly relative to how often it is read. For multi-table transformations, use a dynamic table instead.

Modelling a materialized view

The shop database lets us model the same trade-off with a summary table and a trigger that maintains it. It is a stand-in: Snowflake does the maintenance for you.

SQL A summary table kept current (stand-in) ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

The summary and the full GROUP BY agree after the insert, but the first reads a handful of rows, while the second scans the whole table. That is the entire value proposition of a materialized view.

Python Choose the right tool (decision helper)

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

The rules are a simplification of the guidance, written for practice; real decisions also consider cost estimates.

Query acceleration service

The query acceleration service (QAS) offloads parts of a query that would otherwise take a long time, such as large scans with selective filters, to shared serverless compute. It suits warehouses with a mix of short and occasional very long queries, where sizing the warehouse for the outliers would waste credits.

SQL
ALTER WAREHOUSE bi_wh SET
  ENABLE_QUERY_ACCELERATION = TRUE
  QUERY_ACCELERATION_MAX_SCALE_FACTOR = 8;

-- Which past queries would benefit?
SELECT query_id, eligible_query_acceleration_time
FROM snowflake.account_usage.query_acceleration_eligible
ORDER BY eligible_query_acceleration_time DESC
LIMIT 10;

The scale factor caps how much extra serverless compute can be used (as a multiple of the warehouse size), and therefore the extra cost.

Which one when?

Problem Tool
Range filter on a huge table scans too much Clustering key
Needle-in-haystack lookup, text or VARIANT search Search optimization
The same single-table aggregate runs again and again Materialized view
Multi-table transformation refreshed on a schedule Dynamic table
A few long outlier queries on a busy warehouse Query acceleration
One heavy query that scales well Larger warehouse

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. Which workload is the search optimization service built for?

    It builds a search access path so Snowflake can find the few partitions containing a value quickly.
  2. 2. A materialized view in Snowflake can be defined over...

    MVs support a single base table with simple aggregates, filters and projections. Use dynamic tables for joins.
  3. 3. Who maintains a materialized view as data changes?

    Maintenance is automatic and serverless, so it costs credits even when nobody queries the view.
  4. 4. The query acceleration service is best when...

    QAS lends serverless compute to outlier queries so you need not oversize the whole warehouse.

Summary

  • Search optimization speeds up selective lookups and text or VARIANT search.
  • Materialized views precompute single-table aggregations and stay current automatically.
  • Query acceleration adds serverless compute for outlier queries.
  • All three trade serverless credits and storage for speed; use them selectively.

Next: Roles and privileges