Performance Monitoring and Tuning · Topic 6 of 16

Performance Improvements - Materialized Views, Search Optimization, External Tables and Query Acceleration

Choose the right performance feature: materialized views, search optimization service, external tables, data caching and the query acceleration service.

  • 5 min
In a nutshell
  • Materialized view: single table, background refresh, Enterprise or higher, costs storage and credits.
  • Search optimization: selective lookups, equality, substring, semi structured and geospatial; Enterprise or higher.
  • External table: data in cloud storage, partition it, refresh metadata, add a materialized view.

What it is

Once you know why a query is slow, the next step is picking the feature that fits. Each one targets a different shape of workload, and the exam describes the workload and asks you to choose.

FeatureBest forEditionCost model
Materialized viewRepeated aggregations or filters on one tableEnterprise or higherStorage plus serverless refresh credits
Search optimization serviceSelective lookups on large tablesEnterprise or higherStorage plus serverless maintenance credits
External tableQuerying files in cloud storage in placeAllWarehouse credits when queried, plus metadata refresh
Data cachingRepeated reads of the same dataAllNo extra charge
Query acceleration serviceLarge scans in outlier queriesEnterprise or higherServerless compute credits
Workload shapewhat repeatsmatchesFeatureMV, SOS, QAS, cacheconfigured byALTER or CREATESQLverified inQuery Profilebefore and after

How it works

Materialized views

A materialized view stores the precomputed result of a query on one table and Snowflake keeps it current in the background. The optimizer can also rewrite a query on the base table to use the view automatically.

Use one when the base table is large, changes slowly relative to how often it is queried, and the query is a costly aggregation or selective filter. Limits to remember:

  • The view queries a single table; joins are not allowed.
  • Refresh is automatic and serverless, so it consumes credits, and the stored result consumes storage.
  • Heavy change on the base table makes maintenance costly.
  • It is not the answer for multi table transformations; dynamic tables suit those.
CREATE MATERIALIZED VIEW daily_sales AS
  SELECT order_date, SUM(amount) AS total
  FROM orders GROUP BY order_date;

Materialized views can also be built over external tables to speed up queries on files.

Search optimization service

The search optimization service builds a persistent search access path on a table so that very selective queries read only a few micro partitions. It helps point lookups with equality, substring and regular expression searches, semi structured data and geospatial functions.

ALTER TABLE events ADD SEARCH OPTIMIZATION;
ALTER TABLE events ADD SEARCH OPTIMIZATION ON EQUALITY(user_id);
SELECT SYSTEM$ESTIMATE_SEARCH_OPTIMIZATION_COSTS('events');

It needs Enterprise Edition or higher. Adding it requires the OWNERSHIP privilege on the table and the ADD SEARCH OPTIMIZATION privilege on the schema. It costs storage for the access paths and serverless credits to build and maintain them, and it is best on tables that are not changing constantly.

Exam tip

Clustering helps range filters and sorting on a few columns. Search optimization helps needle in a haystack lookups on any column. A question about finding one order by ID in a huge table points to search optimization.

External tables

An external table lets you query files that stay in cloud storage by defining it over an external stage. Columns are read from a VALUE variant, and metadata such as METADATA$FILENAME is available. Because data is outside Snowflake storage there are no micro partitions of its own, so queries are slower than on native tables.

To improve performance:

  • Partition the external table on a path pattern so queries read only needed files.
  • Keep the metadata refreshed, ideally with automatic refresh driven by cloud event notifications.
  • Create a materialized view over the external table for the queries you repeat.

Use data caching

Caching is the free improvement. Keep a warehouse running long enough that its cache stays warm for repeating workloads, send similar queries to the same warehouse, and use the result cache for repeated identical queries. The previous page covers how each cache behaves.

Query acceleration service

The query acceleration service (QAS) offloads parts of eligible queries to serverless compute so a large scan does not hold up the warehouse. It suits ad hoc analytics and queries with unpredictable size, where a few outliers scan much more data than the rest.

ALTER WAREHOUSE bi_wh SET ENABLE_QUERY_ACCELERATION = TRUE
                          QUERY_ACCELERATION_MAX_SCALE_FACTOR = 8;
SELECT SYSTEM$ESTIMATE_QUERY_ACCELERATION('<query_id>');
  • QUERY_ACCELERATION_MAX_SCALE_FACTOR caps serverless compute as a multiple of the warehouse size. Its default is 8, and 0 removes the cap.
  • The QUERY_ACCELERATION_ELIGIBLE view lists past queries that would have benefited.
  • QUERY_ACCELERATION_HISTORY shows the serverless credits used.
Warehouseruns the queryoffloads scan work toServerless computequery accelerationreturns rows toWarehousefinishes the query

Common mistakes

Using a materialized view for a join. It supports one table only.

Adding search optimization to a heavily updated table. Maintenance cost can exceed the gain.

Treating external tables as fast. They trade speed for leaving data in place; partitioning and materialized views reduce the gap.

Enabling query acceleration for a steady, uniform workload. It pays off for outliers; a uniform load needs a larger warehouse.

What to remember for the exam

  • Materialized view: single table, background refresh, Enterprise or higher, costs storage and credits.
  • Search optimization: selective lookups, equality, substring, semi structured and geospatial; Enterprise or higher.
  • External table: data in cloud storage, partition it, refresh metadata, add a materialized view.
  • QAS is enabled per warehouse with ENABLE_QUERY_ACCELERATION; default scale factor 8.
  • Both materialized views and search optimization use serverless credits.

Check yourself

  • A dashboard repeats the same heavy aggregation on one slowly changing table. Which feature?

    A materialized view.

  • Which feature speeds point lookups of a single ID in a huge table?

    The search optimization service.

  • How do you find which past queries would benefit from query acceleration?

    Query the QUERY_ACCELERATION_ELIGIBLE view or call SYSTEM$ESTIMATE_QUERY_ACCELERATION.

Topic 6 of 16 in Performance Monitoring and Tuning

Test yourself on Performance Monitoring and Tuning

The full Snowflake ADA-C02 question bank has 814 questions, with timed practice exams and an explanation for every answer.

This study guide is independent and is not affiliated with, endorsed by, or sponsored by Snowflake. Vendor names, certification names and exam codes are trademarks of their respective owners and are used for identification only.