Accelerating Snowflake Queries for Enhanced Performance

Say you need to run a critical query, and then report the results to company leadership later that afternoon. Yet when you run your query on Snowflake, you run into performance bottlenecks, resource constraints, and frustrating delays. How do you stop this from happening, and how do you boost performance when you need it? 

One of the answers is Snowflake query acceleration. 

What is Snowflake Query Acceleration?

Snowflake query acceleration can be used to offload elements of query processing to serverless compute resources. This enables faster querying with less demands on the warehouse’s compute resources. 

Common examples of the types of workloads that can benefit from Snowflake query acceleration include:

• Workloads where data volume per query is unpredictable
• Ad hoc analytics
• Queries where large scans or selective filters are utilized

For example, take the case of a large retail chain that was trying to generate demand forecasts but was facing massive delays due to extended query execution times on their Snowflake data warehouse. 

These complex queries take multiple parameters into account, from aggregated historical data to weather information. 

The retailer was finding that queries were taking much longer to run than was expected, that these delays were negatively impacting decision-making, and that computer resources were fully utilized making it difficult to scale up without impacting other workloads. 

By using Snowflake’s Query Acceleration Service QAS), the retailer was able to solve these issues. Queries were handled more efficiently, execution time was reduced, and costs were reined in. The business outcomes were encouraging: 

1. Query execution times dropped significantly, enabling daily instead of weekly forecasting
2. Inventory management was improved, minimizing stockouts and overstocking
3. Customer satisfaction and sales both increased

The Benefits of Accelerating Queries in Snowflake

This example highlights the benefits of Snowflake query acceleration. These benefits include:

• Faster query execution times
• Increased efficiency
• Better user experience
• Enhanced scalability
• Enablement of complex analytics
• The ability to control costs
• Increased productivity

Ultimately, these lead to faster time to insights, which is critical in gaining and maintaining a competitive advantage. 

How Query Acceleration Service in Snowflake works

Snowflake’s Query Acceleration Service (QAS) can improve query performance by dynamically allocating additional serverless compute resources to handle resource-intensive portions of queries. It works as follows:

1. Dynamic Resource Allocation:
   • When a query is submitted to a warehouse with QAS enabled, Snowflake evaluates whether it can benefit from acceleration
   • If eligible, QAS offloads specific query processing tasks (for example large scans or aggregations) to serverless compute clusters
2. Query Segmentation:
   • Queries are broken into smaller tasks
   • Tasks requiring significant computational effort, such as filtering, aggregations, and scans over large datasets, are handled by the serverless resources
3. Execution with Additional Compute:
   • Serverless resources perform the accelerated tasks in parallel with the warehouse’s compute resources
4. Cost Management:
   • QAS allows you to set a max scale factor, limits the usage of serverless compute resources

Implementing and Monitoring Query Acceleration in Snowflake

To implement Snowflake query acceleration, specify ENABLE_QUERY_ACCELERATION = TRUE when creating a warehouse. 

This would look something like:

CREATE WAREHOUSE new_wh WITH

  ENABLE_QUERY_ACCELERATION = true;

Monitoring Snowflake query acceleration usage can be done in several ways, including through the web interface.

Available statistics include:

• Execution time breakdown
  • processing time
  • local disk I/O time
  • remote disk I/O time
  • network communication time
  • synchronization time
  • initialization time
• I/O statistics
  • scan progress percentage
  • total bytes scanned
  • percentage scanned from cache
• Network usage
  • total bytes sent over the network
• Pruning details
  • partitions scanned
  • total partitions available
• Query acceleration
  • partitions scanned by the query acceleration service
  • scans selected for acceleration
• Spilling
  • bytes spilled to remote storage

Common Challenges (and Solutions) in Snowflake Query Optimization

Anyone dealing with Snowflake and QAS will likely come across these common issues:

How to identify eligible queries

Snowflake itself provides details on how to identify queries that are eligible for Snowflake query acceleration. It recommends querying the QUERY_ACCELERATION_ELIGIBLE view or the SYSTEM$ESTIMATE_QUERY_ACCELERATION function. You can wrap the function in the PARSE_JSON function to make it easier to interpret the results. For example:

SELECT PARSE_JSON(system$estimate_query_acceleration(‘9de65cg1-2762-6c2d-bd0d-7b0693fc21g’)

Managing costs

Query acceleration is handled by serverless compute resources, which are billed for outside of the credits consumed by the warehouse itself. 

A warehouse’s scale factor can be used to manage costs related to the service. The scale factor is a cost control mechanism used to set a limit on how much compute resources can be leased by a warehouse for query acceleration. 

The SYSTEM$ESTIMATE_QUERY_ACCELERATION function can be used to check how the scale factor will affect the performance of a query.

Billing information can be viewed in the classic console, using the account usage QUERY_ACCELERATION_HISTORY view, or the organization usage QUERY_ACCELERATION_HISTORY view. 

Monitoring usage

Snowflake query acceleration usage can be monitored through the web interface, or by using the account usage QUERY_HISTORY view. An example provided by Snowflake illustrates the usage of this query. 

To find the queries with the largest number of partitions scanned by the query acceleration service in the past 24 hours:

SELECT query_id,

       query_text,

       warehouse_name,

       start_time,

       end_time,

       query_acceleration_bytes_scanned,

       query_acceleration_partitions_scanned,

       query_acceleration_upper_limit_scale_factor

  FROM SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY

  WHERE query_acceleration_partitions_scanned > 0 

  AND start_time >= DATEADD(hour, -24, CURRENT_TIMESTAMP())

  ORDER BY query_acceleration_partitions_scanned DESC;

FAQ

Q: Can caching improve Snowflake query performance?

A: Yes, caching can improve query performance for repeated queries. 

Q: Are there specific configurations for better query performance in Snowflake?

A: Clustering keys, warehouse size and query optimization parameters can contribute to better performance.

Q: How does Snowflake compare to other platforms in query speed?

A: Snowflake is competitive in terms of performance. 

Q: Is query acceleration in Snowflake cost-effective for large datasets?

A: This depends on numerous factors, and will differ depending on the query and organization. 

Meet SQream: Industry Leading GPU-Accelerated Data Processing

SQream redefines accelerated data processing with its cutting-edge GPU-accelerated technology, enabling organizations to process massive, complex datasets with unparalleled speed and efficiency. 

Key features of SQream include:

• Unmatched scalability: Handles petabyte-scale datasets effortlessly, enabling limitless growth.
• GPU-accelerated performance: Speeds up query execution for even the most complex tasks, reducing bottlenecks.
• Cost-efficiency: Achieves faster results while lowering hardware and operational expenses.
• Seamless integration: Works within existing data ecosystems, supporting tools like ODBC, JDBC, and popular programming languages.
• Flexible architecture: Separates compute and storage for enhanced adaptability and scalability.

SQream can take query acceleration to a whole new level. For example, in the TPCx-BB benchmark, SQream processed 30TB of data twice as fast as Snowflake, at half the cost.

The great news is that SQream complements existing platforms like Snowflake by accelerating complex analytics, enriching data ecosystems without requiring replacement​. 

This makes SQream a significantly better alternative in many cases to Snowflake Query Acceleration: across cost, performance, and other relevant metrics. 

Organizations using SQream have reported groundbreaking outcomes:

• Drastically reduced query times
• Enhanced insights, empowering timely, data-driven decision-making
• Streamlined operations, minimizing costs without sacrificing performance

This translates into real-world tangible business results: superior decision-making, enhanced planning, and ultimately improved performance across key business benchmarks. 

To start your journey with SQream, get in touch with the SQream team here. 

Summary

Forming the infrastructure backbone for so many organizations, the questions around Snowflake query acceleration will continue to swirl. One alternative is Snowflake’s Query Acceleration Service.

We looked into the benefits, challenges, and practical tips when it comes to this solution.

Another viable option is SQream, which has demonstrated incredible results both in terms of performance, and cost. 

Whichever one you choose, there’s no question that Snowflake query acceleration can provide faster time-to-insight and position your organization for long-term growth.