How you can use the Snowflake Query Acceleration Service to balance price and performance

The Query Acceleration Service (QAS) in Snowflake is a powerful serverless feature that can dramatically improve your query performance without any manual tuning. It automatically allocates temporary, extra capacity that can be used to accelerate long-running scans and filter operations. When you run a query on a QAS-enabled warehouse, QAS automatically assesses if there’s an opportunity to meaningfully accelerate your query by further parallelizing work on the pool of QAS compute nodes. If QAS doesn’t identify eligible queries, then there is no additional cost. In other words, the feature is completely “serverless” and you only pay when QAS is actively improving query performance.

QAS was released as Generally Available in February 2023. Since then, we’ve seen many customers use QAS to optimize the cost and performance of their Snowflake workloads. QAS is designed to be a cost-neutral service — essentially transferring the same amount of “work” to a larger compute pool and getting faster processing times in return. Your workload may be cost-neutral or even run at a lower cost if your warehouse turns off after the QAS-accelerated workloads are completed, or if you are able to downsize your warehouse once QAS is enabled.

In this blog, we’ll deep-dive into three common QAS usage patterns and show you how you can use QAS to improve query performance while maintaining or even cutting your compute costs.

Pattern 1: Save money while meeting performance SLA’s

With QAS enabled, you may be able to downsize your warehouse to save on overall compute costs while still meeting critical performance targets. In these cases, QAS may accelerate the long-running queries which may have previously required the larger warehouse size. By running these queries outside of the warehouse, you may find that you can downsize your warehouse to save money while minimally impacting performance. HelloFresh shared a great example of downsizing their warehouse with the help of QAS in their blog post in August 2023.

As shown below, after implementing QAS and moving to a smaller warehouse, HelloFresh’s median query execution time remained relatively unchanged.

Additionally, due to moving to a smaller WH, they saw an approximately 30% reduction in total compute costs (warehouse and QAS combined). Here’s a screenshot from their blog post which shows significant and immediate cost savings on their combined warehouse and QAS costs, which we might refer to as their “net warehouse cost”:

Summarizing their experience, Faysal Rehmat, Senior Data Engineer at HelloFresh, reported that by enabling QAS, they were able to downsize their warehouse and achieve “a 30% reduction in warehouse credit usage” with “only a 0.18s increase in median query execution time, which is an excellent return on investment.”

Pattern 2: Improve performance with no cost increase

A leading US online food delivery company used QAS to improve query execution with no cost increase. In their case, after enabling QAS, they saw their average query execution time on eligible queries decrease by over 50%. They configured a max scale factor of 4 on the vast majority of their warehouses and, in some cases, as low as 2. You can set a max scale factor to specify the maximum amount of resources allocated for query acceleration. For this customer, setting a low max scale factor limited the degree to which QAS could accelerate scans, but kept QAS costs low.

The below chart averages query performance across all of the QAS-enabled warehouses on the days before and after QAS was enabled.

Among eligible queries, they saw a reduction in average execution from about 6 minutes (370 seconds) to just over 2 minutes (145 seconds). As shown below, the raw QAS cost was less than 10% of their warehouse costs.

Additionally, if you look at their “net warehouse cost”, which we can define as warehouse cost plus QAS cost, there was effectively zero change, or at the very least, much less than the day to day “noise” in usage fluctuations! In this case, QAS significantly reduced the load on the warehouse so its cost was approximately the same as the reduction in warehouse costs after enabling QAS. By enabling QAS on a warehouse that shut down whenever the query was complete, the customer was able to double their query execution speed without significantly impacting query costs.

Pattern 3: Greatly improve performance for a modest premium

A large US telecommunications company used QAS to dramatically speed up ad hoc query performance on queries with filters to specific devices. These narrow filters were ideal candidates for acceleration. In order to achieve the best possible performance, they enabled QAS with a relatively high (>10) max scale factor. Configuring a high max scale factor allowed QAS to use more resources to accelerate query performance. As a result, their scan operations accelerated so much that query performance fell from an average of 1 hour (4 million milliseconds) down to less than 5 minutes!

In general, the more QAS accelerates scans, the higher the QAS cost. In this case, QAS accelerated many expensive scan operations, so the performance impact was extreme.

In this scenario, as shown in the below credits graph, more work was done by the QAS nodes than the warehouse nodes! Luckily, as we often see, enabling QAS reduced the usage of the warehouse nodes by almost 50% because they were suspended more quickly. Therefore, the increase in the customer’s “net warehouse cost” was much more modest compared to the performance improvement. Specifically, the customer paid about 30–40% more in net warehouse credits but, in return, improved their query performance by an order of magnitude for a mission critical, latency sensitive workload.

How to balance QAS cost and performance for your workloads

It’s easy to use QAS to improve query performance. The only setup required for QAS is selecting the max scale factor, which represents a multiple of your current warehouse size. The higher the max scale factor, the greater the amount of resources that can be used for QAS, so the higher the upper bound on costs. For example, if you use a small warehouse and set a max scale factor of 4, the absolute maximum hourly QAS bill would be 8 credits (2 credits x 4). In practice, because you only pay for additional QAS compute when it is actively improving query performance, it’s unlikely you would pay for the full additional capacity all the time.

Additionally, there can be a logarithmic relationship between the max scale factor and reductions in query execution time. As such, we recommend starting with a low max scale factor (4 or lower) and gradually increasing it, as opposed to starting with a high value. In general, the lower the max scale factor, the more likely that QAS will provide cost-neutral benefits. Additionally, in order to maximize the amount of time your warehouse is able to shut down after completing QAS-accelerated queries, ensure that you have auto-suspend enabled on your warehouse.

After you’ve enabled QAS, eligible queries will benefit immediately, and you can use these queries to analyze the impact on cost and performance. If the cost and performance trade-off is favorable, consider repeating the analysis with a higher max scale factor to assess whether you can achieve further performance gains with minimal cost increase. If the QAS net cost is too high relative to the performance tradeoffs, consider lowering the max scale factor or disabling QAS. Additionally, you may discover that, after enabling QAS on a warehouse, you are able to decrease its size.

It’s important to perform this analysis when the workload is relatively stable. There are few ways that you can identify whether your workload is stable:

• The number of eligible queries remained relatively constant (this is provided in this query)
• The total number of queries using the warehouse remained relatively constant
• The average eligible query execution time remained constant during the days before QAS was enabled (this is provided in this query)

In this blog, we showed how you can use QAS to improve query performance and potentially even save money on your net warehouse costs. Learn more about the Query Acceleration Service and get started accelerating your queries.