Bird
Raised Fist0
SnowflakeHow-ToBeginner · 4 min read

How to Optimize Query Performance in Snowflake

To optimize queries in Snowflake, use clustering keys to organize large tables, apply pruning by filtering on clustered columns, and avoid SELECT * by selecting only needed columns. Also, use result caching and monitor query profiles to identify bottlenecks.
📐

Syntax

Here are key Snowflake features and syntax to optimize queries:

  • Clustering Key: Organizes data to improve pruning.
    ALTER TABLE table_name CLUSTER BY (column1, column2);
  • Result Cache: Snowflake automatically caches query results for faster repeated queries.
  • Selective Columns: Choose only needed columns instead of SELECT *.
  • Query Profile: Use EXPLAIN or the UI to analyze query steps.
sql
ALTER TABLE sales CLUSTER BY (region, sale_date);
💻

Example

This example shows how to create a clustering key and run a filtered query that benefits from pruning.

sql
CREATE OR REPLACE TABLE sales (
  id INT,
  region STRING,
  sale_date DATE,
  amount NUMBER
);

ALTER TABLE sales CLUSTER BY (region, sale_date);

SELECT region, SUM(amount) AS total_sales
FROM sales
WHERE region = 'North' AND sale_date >= '2024-01-01'
GROUP BY region;
Output
REGION | TOTAL_SALES North | 123456.78
⚠️

Common Pitfalls

Common mistakes when optimizing Snowflake queries include:

  • Using SELECT * which reads unnecessary data.
  • Not defining clustering keys on large tables, causing full scans.
  • Ignoring query profile insights that show slow steps.
  • Over-clustering small tables, which adds overhead.
sql
/* Wrong: selects all columns and no clustering */
SELECT * FROM sales WHERE region = 'North';

/* Right: selects only needed columns and uses clustering */
SELECT region, amount FROM sales WHERE region = 'North';
📊

Quick Reference

Optimization TechniqueDescription
Clustering KeysOrganize large tables by columns to speed up filtering
Selective ColumnsAvoid SELECT *; choose only needed columns
Result CachingSnowflake caches query results automatically
Query ProfileAnalyze query execution to find bottlenecks
Avoid Over-ClusteringUse clustering only on large, frequently filtered tables
✅

Key Takeaways

Use clustering keys on large tables to improve query pruning and reduce scan time.
Select only necessary columns instead of using SELECT * to reduce data scanned.
Leverage Snowflake's automatic result caching for faster repeated queries.
Regularly check query profiles to identify and fix performance bottlenecks.
Avoid clustering small tables to prevent unnecessary overhead.