Boost Your Snowflake Queries: Top Strategies for Faster Results

Snowflake is a cloud-based data warehousing solution that offers unlimited scale, concurrency, and performance. However, even with all of its advanced capabilities, Snowflake query performance can still be impacted by large volumes of data and complex queries. That’s where query optimization comes in. By fine-tuning queries to minimize the amount of data scanned and processed, Snowflake users can significantly improve query performance and reduce costs. In this article, we will guide you to Boost Your Snowflake Queries: Top Strategies for Faster Results.

To make the information more accessible, we have divided this article into two parts.

In Part 1, we will cover the concept of Query Optimization, the Snowflake Query Processing Layer, and the Query Optimization Techniques, including Snowflake Search Optimization Service (SOS), Minimize Data Movement, Use of appropriate Data Types, Use of Materialized Views, Use Clustering Keys and Use of Query Profiling.

In Part 2, we will go deeper into Snowflake Query Optimization Techniques taking your Snowflake query optimization to the next level. So, whether you’re a seasoned Snowflake user or a newcomer to the platform, read on to learn everything you need to know about optimizing Snowflake queries.

So, let’s begin our journey with some basic concepts of Query Optimization and Query Processing layer in Snowflake.

Table of content:

1. What are Query Optimization and its benefits?
2. Snowflake Query Processing Layer
3. Snowflake Query Optimization Techniques
   • Snowflake Search Optimization Service (SOS)
   • Minimize Data Movement
   • Use of Appropriate Data Types
   • Use of Materialized Views
   • Use Clustering Keys
   • Use of Query Profiling
4. Conclusion

What is Query Optimization?

Query optimization is the process of transforming a query into an equivalent form that may be evaluated more efficiently. The goal of query optimization is to find an execution plan that reduces the time required to process a query. Query optimization is of great importance for the performance of a relational database, especially for the execution of complex SQL statements.

Query optimization is important for Snowflake users because it can help them:

• Reduce cost: Executing a query requires resources. The more resource is consumed the higher the cost. In Snowflake, the query cost is tied directly to a Virtual Warehouse. The longer the query runs, the more expensive it gets. Query optimization can help reduce the amount of data scanned and processed by the queries, which can lower the cost.
• Reduce query run time: Some queries may have a certain SLA that they need to meet, not hitting those targets may result in delays in the delivery of data that could negatively impact the business. Query optimization can help improve the performance of queries by finding the optimal way to access and manipulate the data.

Snowflake Query Processing Layer

Snowflake’s Query processing layer handles the execution of queries on the centralized storage. To achieve this, Snowflake uses “Virtual Warehouses”, which separate the query processing layer from the disk storage layer. The queries are executed in the processing layer using the data from the storage layer.

For running queries, Snowflake relies on an MPP (massively parallel processing) compute cluster called Virtual Warehouse. It consists of multiple nodes with CPU and memory resources provisioned by Snowflake on the cloud. Multiple Virtual Warehouses can be created in Snowflake to meet various workload requirements. Each Virtual Warehouse is designed to work with one storage layer and has its own independent compute cluster in most cases. This approach makes the Snowflake platform highly scalable and flexible, making it a popular choice for data warehousing and analytics.

Query Optimization Techniques in Snowflake

Snowflake query optimization is the process of improving the performance and cost-efficiency of queries on the Snowflake data warehouse.It also provides some features and functions that can help boost the query performance, such as:

1. Snowflake Search Optimization Service (SOS)

Search Optimization Service works best to improve the performance of a query when the table is frequently queried on columns other than the primary cluster key and analytical queries that use an extensive set of predicates for filtering. Here are the use cases of search optimization :

• Business users need fast response times for critical dashboards with highly selective filters.
• Data scientists who are exploring large data volumes and looking for specific subsets of data.
• Data applications retrieve a small set of results based on an extensive set of filtering predicates.
• Equality Searches.
• Substring and regular expression searches
• Searches in Variant data types
• Searches of Geography columns using geospatial function

To enable search optimization for your account you must enable this feature for specific columns or fields in columns or for the entire table for example:

# Adding seach optimization for for specific column
ALTER TABLE mytable ADD SEARCH OPTIMIZATION ON EQUALITY(myvariantcol);
ALTER TABLE t1 ADD SEARCH OPTIMIZATION ON EQUALITY(c4:user:uuid);

# Adding seach optimization for entire table
alter table test_table add search optimization;

#Verify if search optimization is enabled on a table
SHOW TABLES LIKE '%test_table%';

It’s important to note that search optimization is most effective for queries that involve searching for text within a large number of rows. If your queries only involve a small number of rows, or if the columns being searched are not text-based, search optimization may not provide significant performance benefits.

2. Minimize Data Movement

Minimizing data movement is key to optimizing query performance in Snowflake. Here are some additional tips to achieve this:

Use partition pruning: Partition pruning is a technique used in Snowflake to improve query performance by reducing the amount of data that needs to be scanned when querying large tables that are partitioned. Partitioning involves dividing a table into smaller, more manageable parts called partitions, based on a specific column or set of columns.

When you run a query in Snowflake that includes a WHERE clause that filters on a partition key column, Snowflake can use partition pruning to eliminate partitions that do not contain any relevant data, reducing the amount of data that needs to be scanned.

For example, suppose you have a large table of sales data that is partitioned by date, with each partition containing sales data for a specific date range. If you run a query that filters on a specific date range, Snowflake can use partition pruning to eliminate all the partitions that do not contain any sales data for that date range, reducing the amount of data that needs to be scanned.

Partition pruning can significantly improve query performance and reduce query costs, especially for large tables that are partitioned by commonly filtered columns. However, it’s important to ensure that your partitioning strategy is optimized for your specific use case and always choose partition keys that are commonly used in your queries.

If your table is partitioned, Snowflake can eliminate entire partitions from a query if they don’t match the filter conditions. This can greatly reduce the amount of data scanned.

For example, if you have a partitioned table on the “date” column, you can use the following query to only scan the partitions for the month of January:

SELECT *
FROM my_table
WHERE date >= '2022-01-01' AND date < '2022-02-01';

• Use appropriate sorting: If your query requires sorting, you can use the ORDER BY clause to specify the sort order. This can help Snowflake minimize data movement by sorting the data on each node before sending it to the query execution node.

For example, if you need to sort a table by the “name” column, you can use the following query:

SELECT *
FROM my_table
ORDER BY name;

3. Use Appropriate Data Types

Choosing the right data type can have a big impact on query performance in Snowflake. Here are some additional tips:

• Use fixed-width data types when possible: Fixed-width data types, such as INTEGER and DATE, are faster to process than variable-width data types, such as VARCHAR and TEXT.
• Use the smallest data type that can accommodate your data: Using a smaller data type can reduce storage requirements and improve query performance. For example, if you know that a column will only contain values between 0 and 100, you can use the TINYINT data type instead of INTEGER.

4. Use Materialized Views

Materialized views can be used to speed up queries that require complex calculations or aggregations. Materialized views refresh automatically as soon as your data changes. A background service updates the materialized view after changes are made to the base table. Here are some additional tips:

• Use appropriate aggregation levels: Materialized views can be created with different levels of aggregation. Choosing the appropriate level of aggregation can help improve query performance by reducing the amount of data that needs to be scanned.

For example, if you have a sales table with millions of rows, you can create a materialized view that aggregates the data at the monthly level:

CREATE MATERIALIZED VIEW monthly_sales AS
SELECT DATE_TRUNC('MONTH', date) AS month, SUM(sales_amount) AS total_sales
FROM sales_table
GROUP BY 1;

5. Use Clustering Keys

Clustering keys are used to group data together on a disk based on a specific column or set of columns. This can improve query performance by reducing the amount of data that needs to be scanned. Here are some additional tips:

• Choose the appropriate clustering key: The clustering key should be chosen based on the most frequently used filter conditions in your queries. For example, if you frequently query a sales table by date range, you can use the date column as a clustering key.
• Use multi-column clustering keys: If your queries frequently filter on multiple columns, you can use a multi-column clustering key to group the data together more efficiently.

For example, if you frequently query a sales table by date range and product category, you can use the following clustering key:

CREATE TABLE sales_table (
    date DATE,
    product_category VARCHAR,
    sales_amount FLOAT,
    ...
)
CLUSTER BY (date, product_category);

6. Use Query Profiling

Query profiling is an essential tool to optimize the performance of queries in Snowflake. It allows you to identify the bottlenecks in your queries, such as long-running operations or excessive data movement and provides insights into how to optimize the query execution plan. Once you run a query click the query profile view and you should identify the following red flags:

1. Analyze the query profile to identify the slowest stages of the query plan. Look for stages with high “elapsed time” or “execution time” values. These stages are likely to be the bottlenecks in your query.
2. Once you have identified the slowest stages, you can try to optimize them. Here are some tips:
   • If a stage involves a large number of rows, consider adding a filter to reduce the number of rows processed.
   • If a stage involves a large amount of data movement, consider using a more efficient join strategy or partitioning the data.
   • If a stage involves a complex computation, consider simplifying the computation or using a more efficient algorithm.
   • Look for long-running or high-cost operations in the query plan. These are likely areas where the query is spending a lot of time or scanning a large amount of data.
   • Examine the number of rows processed by each step in the query plan. This can help you identify areas where the query is performing unnecessary processing.
   • Look for opportunities to minimize data movement by using appropriate sorting and partitioning.
   • Review the query code to identify areas for optimization, such as using appropriate data types, and minimizing the use of subqueries and joins.
3. After you have made changes to your query, rerun it to see if the changes have improved the query performance.

Conclusion

Snowflake provides a range of options for query optimization that can be applied depending on the use case and the query characteristics. By using the techniques to optimize the query in Snowflake, users can improve their query performance and reduce their query cost in Snowflake.