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
- 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.
| Feature | Best for | Edition | Cost model |
|---|---|---|---|
| Materialized view | Repeated aggregations or filters on one table | Enterprise or higher | Storage plus serverless refresh credits |
| Search optimization service | Selective lookups on large tables | Enterprise or higher | Storage plus serverless maintenance credits |
| External table | Querying files in cloud storage in place | All | Warehouse credits when queried, plus metadata refresh |
| Data caching | Repeated reads of the same data | All | No extra charge |
| Query acceleration service | Large scans in outlier queries | Enterprise or higher | Serverless compute credits |
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.
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_FACTORcaps serverless compute as a multiple of the warehouse size. Its default is 8, and 0 removes the cap.- The
QUERY_ACCELERATION_ELIGIBLEview lists past queries that would have benefited. QUERY_ACCELERATION_HISTORYshows the serverless credits used.
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_ELIGIBLEview or callSYSTEM$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.