Snowflake is designed to transform and store your heavy volume data and perform analytical queries on your heavily loaded data.
Many times, we face performance issues while querying this massive data, and it’s time-consuming. It comes with the cost of waiting for your query to run longer.
Whether you’re managing your setup internally or working with a Snowflake consulting partner, optimizing query performance in Snowflake not only enhances efficiency but also reduces costs.
Here are eight effective techniques you should try for performance improvement in Snowflake.
1 Query performance in Snowflake
Query optimization in Snowflake should be the very first approach to improve your query performance. The way queries are structured plays a very important role in performance. Follow these best practices and rewrite the query:
- Select only required columns: Specify the columns you require in your output, avoid ‘Select *’
- Use a WHERE clause to limit the result set: This will help in not fetching the unnecessary data hence helping with faster performance.
- Choose appropriate joins: Avoid unnecessary joins and use INNER JOIN where possible.
- Use EXISTS instead of IN condition: EXISTS is more efficient than IN condition as EXISTS returns true or false and breaks the loop however IN retrieves all the values in the subquery
Using IN Condition
SELECT Employee_ID, Employee_name
FROM Employee
WHERE Employee_ID IN (SELECT manager_id from Manager)Using EXISTS Condition
SELECT Employee_ID, Employee_name FROM
Employee E
WHERE EXISTS IN (SELECT 1 FROM Manager M where M.manager_ID = E.Employee_ID)2) Use Query Profile
To check a query profile, one should first understand how to use a query profile. The Query Profile in Snowflake’s user interface provides detailed insights into how a query was executed. It provides a visual representation of the query plan, displaying each node and its connections. You can view execution details and performance statistics for both individual nodes and full queries.
If you are stuck with ‘How to improve query in snowflake’ then this will help you to understand loopholes in your query and help in recognizing improvement areas.
Query profile is accessible from snowsight -> Monitoring -> Query History -> Query Profile
3) Materialized view in Snowflake
Materialized view stores the precomputed results in the cache. Hence allowing faster query execution by retrieving cached data and improve the performance.
Whenever you have tables like type tables whose data doesn’t change frequently, you can easily make use of a materialized view.
Make a note that materialized views come with their own cost. So, whenever you think that query performance is costing more than maintaining a materialized view, you can always choose a materialized view.
Example:
CREATE MATERIALIZED VIEW AS
SELECT employee_ID, COUNT(*) as employee_count
FROM training_tbl
GROUP BY employee_ID;4) Make use of the result cache
Snowflake is very efficient in storing the result of your query in the result cache for upto 24 hours.
If you have such datasets where underlying data is not changed frequently, you can always make use of the result cache. This can significantly speed up recurring queries.
— First Execution (Caches Results, Execution Time – 15 seconds)
SELECT count(*) from employee where region = ‘North’;— Subsequent Executions (Uses Cached Results, Execution Time – 10 milliseconds)
SELECT count(*) from employee where region = ‘North’;5) Try clustering the table
Clustering defines the partitioning in Snowflake which helps us to store similar patterns of data in the same micro partition. Hence it takes very minimal time to retrieve data from a clustered table. This is beneficial for large tables frequently queried on specific columns. Hence, whenever possible, try clustering your table to improve performance.
You can cluster tables by using the below query.
ALTER TABLE employee CLUSTER BY (employee_ID);This improves performance when filtering by employee_ID, as Snowflake will scan very few partitions.
6) Use Search Optimization Service (SOS)
If you have a query where you are filtering based on a column multiple times and a column with high cardinality values, then you can go for SOS. You should implement this if you have a non-clustered table with filters.
ALTER TABLE employee ADD SEARCH OPTIMIZATION;SOS follows the search access path which helps in keeping track of values likely present in which micro partition. So, while scanning the table, it skips the unwanted partitions and hence produces your results faster. This is an extra service on top of Snowflake’s regular strategy to track the minimum and maximum value present in each micro-partition for filtering the results.
SOS is a cost-effective service by saves your processing time and cost.
7) Use Query Acceleration Service (QAS)
Query Acceleration service is all about providing additional clusters to your long-running query.
There is an eligibility criteria to check if your query is compatible with QAS. If yes, then QAS settings can be enabled on the warehouse level.
If your query falls under the below criteria, then it is eligible to avail of QAS service.
1 Query has filters or aggregations
2 Filters in query scan many rows.
How to check if your is eligible for QAS:
Select PARSE_JSON (system$estimate_query_acceleration('8cd54bf0-1651-5b1c-ac9c-6a9582ebd20f'));How to enable QAS:
ALTER SESSION SET USE_QUERY_ACCELERATION = TRUE;8) Alter warehouse settings
If you see slack in your query performance, you can check your warehouse settings. Follow the best practices to set your warehouse settings.
- Increase warehouse size for complex queries.
- Use auto-suspend and auto-resume to optimize resource consumption.
- Enable multi-cluster warehouses for handling concurrent workloads.
You can either do it using UI or with a query.
ALTER WAREHOUSE warehouse_name SET WAREHOUSE_SIZE = ‘LARGE’;9) Try altering the data structures
Choose data types wisely to improve query performance.
- Use INTEGERs instead of STRINGs for categorical data.
- Normalize large tables where applicable.
- Replace VARCHAR with BOOLEAN or numeric values for flags.
This reduces storage size and speeds up filtering operations.
-- Instead of storing 'Male'/'Female' as VARCHAR
ALTER TABLE users ADD COLUMN gender_flag BOOLEAN;
-- Use 1 for Male, 0 for FemaleSee more:
