Optimization Methods

1. Optimization Methods

In this video you'll learn the four performance optimization methods Snowflake gives you, and how to pick the right one - or combine them - when a query is slow.

2. More Compute isn't Always the Fix

When a query is slow, the first instinct is often to give it a bigger warehouse. Sometimes that works. But sometimes the warehouse is fine and the query is scanning data it shouldn't, or computing results it could reuse. Snowflake has four optimization methods that target different bottlenecks. Picking the right one - or combining them - saves money and makes the query faster.

3. Four Different Approaches

Before diving in, let's understand the four different approaches. Search Optimization for high-cardinality point lookups. Query Acceleration Service for unpredictable heavy scans that overwhelm the warehouse. Automatic Clustering for tables where the same filter columns appear in almost every query. And Materialized Views when the same expensive aggregation runs repeatedly on slowly changing data.

4. Search Optimization

Search Optimization Service helps with queries that look for a small number of specific rows in a large table. Think point lookups such as a shipment ID, a UUID, a tracking number. Without it, Snowflake scans every micro-partition that might contain that value. With it, Snowflake builds a persistent access path so most of those partitions can be skipped. You enable it per table with ALTER TABLE ADD SEARCH OPTIMIZATION. It costs ongoing storage and compute to maintain. It's an Enterprise Edition feature, so check your account before reaching for it.

5. Query Acceleration Service

Query Acceleration Service handles the opposite problem - large, unpredictable scans that overwhelm a warehouse. QAS spins up serverless compute on the side to absorb part of the work. It supports SELECT, INSERT, CREATE TABLE AS SELECT, and COPY INTO. You enable it with ENABLE QUERY ACCELERATION equals TRUE on the warehouse. The scale factor caps how much serverless compute it can lease — the default is 8. Note that setting it to zero means unlimited, not disabled. To turn QAS off, set ENABLE QUERY ACCELERATION to FALSE. Like Search Optimization, this is an Enterprise Edition feature.

6. Automatic Clustering

Automatic Clustering helps when the same columns appear in almost every filter - for Harbr, that's region and dispatch date. By default, Snowflake stores rows in load order. If your queries filter by region, all the regions get mixed across micro-partitions, and therefore pruning can't help. Define a clustering key with ALTER TABLE CLUSTER BY and Snowflake reorders the table in the background so matching rows sit together. Reclustering is serverless - no warehouse to manage. The trade-off is the ongoing compute cost as new data arrives.

7. Materialized Views

Materialized Views work when the same expensive query runs over and over against slowly-changing data. Snowflake pre-computes the result and stores it. Subsequent queries read the stored result instead of scanning the source table. A background process keeps it in sync when the underlying data changes - you don't refresh it manually. The catch is that materialized views have real SQL restrictions: no HAVING clause, limited joins, no window functions. Check the docs before you design one. Costs are storage for the result plus compute for ongoing refresh.

8. Choosing the Right Method

Here's the decision framework. The signal in the Query Profile tells you which method fits. Equality lookup scanning almost everything - Search Optimization. Heavy unpredictable scans - Query Acceleration Service. Same filter columns in most queries with weak pruning - Automatic Clustering. Same expensive aggregation over and over - Materialized View. And they're not exclusive. Search Optimization and QAS work together - prune first, accelerate the rest. The Query Profile is what tells you which lever to pull.

9. Let's practice!

You've covered the four optimization methods and when each applies. Let's practice matching the right tool to the right problem.

Create Your Free Account

By continuing, you accept our Terms of Use, our Privacy Policy and that your data is stored in the USA.