Best practices for Optimizing Snowflake Queries

Optimizing your Snowflake queries is essential for improving performance and reducing query response times. In this article, you will find some best practices to help you achieve optimal performance.

1. Choose the Right Warehouse Size

Start with an X-SMALL warehouse and scale up only as needed.
Avoid over-provisioning to reduce compute costs.
Monitor query performance and adjust based on workload.

2. Use Snowflake’s Built-In Caching

Snowflake uses three caching layers to reduce compute time:

Result Cache: Stores query results for 24 hours. Ideal for repeated queries.
Local Disk Cache: Caches frequently accessed data on the warehouse.
Remote Disk Cache: Shares cache across warehouses for consistency.

Example
Run a query like:

SELECT COUNT(*) FROM SALES WHERE REGION = 'US';

If you run the same query again within 24 hours without any table updates, Snowflake will use the Result Cache, returning the result instantly with zero compute cost.

Using caching properly can dramatically improve performance and reduce costs.

3. Leverage Materialized Views

Use materialized views for frequently run, complex queries (joins, aggregations).
They store precomputed results and reduce execution time.
Best when the underlying data changes infrequently.

Example:
You frequently run:

SELECT PRODUCT_ID, SUM(SALES) 
FROM SALES 
GROUP BY PRODUCT_ID;

Instead of computing this each time, create a materialized view:

CREATE MATERIALIZED VIEW MV_PRODUCT_SALES AS
SELECT PRODUCT_ID, SUM(SALES) AS TOTAL_SALES
FROM SALES
GROUP BY PRODUCT_ID;

Now queries on MV_PRODUCT_SALES are much faster, using precomputed results.

4. Apply Clustering Keys for Large Tables

Clustering organizes data for more efficient pruning.
Improves performance for queries that filter on clustered columns.
Ideal for large, frequently queried tables.

Example

Your ORDERS table is often filtered by ORDER_DATE:

SELECT * FROM ORDERS WHERE ORDER_DATE >= '2024-01-01';

Define a clustering key to improve pruning:

ALTER TABLE ORDERS CLUSTER BY (ORDER_DATE);

This helps Snowflake scan only relevant micro-partitions, speeding up large scans.

5. Enable Query Acceleration Service (QAS)

Snowflake automatically allocates extra compute for large queries.
Improves performance without manual scaling.
Charges only for the extra resources used.

Example:

For a large aggregation query:

SELECT REGION, AVG(REVENUE) FROM SALES_DATA GROUP BY REGION;

Enable QAS on your warehouse:

ALTER WAREHOUSE my_warehouse SET QUERY_ACCELERATION = TRUE;

Snowflake will dynamically scale compute for complex queries, improving speed without resizing the warehouse.

6. Activate Search Optimization Service (if applicable)

Optimizes point lookup queries (e.g., WHERE id = '123') on large datasets.
Maintains extra metadata to accelerate row-level access.
Useful for large transactional tables with high lookup demand.

Example

You often run:

SELECT * FROM USERS WHERE USER_ID = 'abc123';

If USERS is a large table, enable search optimization:

ALTER TABLE USERS SET SEARCH_OPTIMIZATION = ON;

This maintains metadata for fast lookups on USER_ID, significantly reducing query latency.

7. Monitor & Troubleshoot with Snowflake Tools

Monitoring is key to ongoing optimization. Here’s how to use Snowflake’s built-in tools effectively:

Query History: Track performance and identify slow queries.
- Go to the History tab in the Snowflake UI.
- Use filters to search by user, time range, warehouse, or query ID.
- Check columns like Execution Time, Bytes Scanned, and Status to identify slow or failing queries.
- Click any query to view detailed execution stats or to open the Query Profile.
Query Profile: Visualize query execution and detect bottlenecks.
1. Open a completed query from the History view.
2. Click "Profile" to visualize the execution plan.
3. Look for:
  - Stages with high execution time
  - Steps with high data volume
  - Operators marked in red which indicate bottlenecks.
Use this information to identify inefficient joins, missing pruning, or compute-heavy transformations.
Account Usage Views: Analyze usage patterns and warehouse activity.

1. Run queries on Snowflake's SNOWFLAKE.ACCOUNT_USAGE or INFORMATION_SCHEMA views.

2. Recommended views:
- QUERY_HISTORY – Details about all executed queries.
- WAREHOUSE_LOAD_HISTORY – CPU/memory usage across time.
- TABLE_STORAGE_METRICS – Storage size and partition details.
3. Use these to monitor warehouse activity, peak usage periods, and potential overuse.

By following these best practices, you can significantly optimize your Snowflake queries, ensuring faster response times and more efficient data processing. Regularly review and adjust your strategies based on query performance metrics to maintain optimal performance with Dataslayer.

As always, please contact us via our live chat on our website or via email if you still have doubts or questions. We are happy to help!