Boosting Query Performance with Snowflake’s Query Acceleration Service


Snowflake is a modern data warehousing platform that is designed to meet the challenges of changing data landscape. One of the key features of the Snowflake is its Query Acceleration Service, which is designed to help users improve the performance of queries. In this blog, we will look at how the Query Acceleration Service works and how it can help you boost the performance of your queries in Snowflake.

What is Snowflake’s Query Acceleration service?

Snowflake Query Acceleration Service is a feature that enables users to accelerate their queries by providing a high-performance computing environment. The service uses a combination of hardware and software to optimize query execution and reduce the time it takes to run queries and improve overall performance.

How Does the Query Acceleration service work?
The Query Acceleration Service works by caching the results of frequently executed queries in memory. When a query is executed, Snowflake checks to see if the results are already cached. If they are, Snowflake retrieves the results from the cache, which significantly reduces the time it takes to run the query. If the results are not in the cache, Snowflake executes the query and caches the results for future use.

The Query Acceleration Service also optimizes query execution by using multiple processors and cores to run queries simultaneously. This parallel processing of queries significantly reduces the time it takes to run queries, making the entire process faster and more efficient.

Benefits of Query Acceleration Service

There are several benefits to using the Query Acceleration Service in Snowflake, including:

1. Improved Query performance:
By caching frequently executed queries and executing queries in parallel, the Query Acceleration Service significantly improves the performance of queries. This means that you can run complex queries faster and get the results you need more quickly.
2. Increased Efficiency:

The Query Acceleration Service helps you save time and resources by reducing the time it takes to run queries. This means that you can get more done in less time, freeing up resources to focus on other important tasks.

3. Cost Savings:

By reducing the time it takes to run queries, the Query Acceleration Service can help you save money on computing costs. The faster your queries run, the less time you will need to spend on computing, which results in significant cost savings.

4. Automatic Scaling:
  • With automatic scaling, QAS can dynamically adjust resources allotted to the virtual warehouse. It will Auto-scale up/down resources based on query requirements, this means that users don’t have to manually adjust the resources to optimize the performance of queries.
5. Greater Control:

QAS offers greater control to users by allowing them to set a scaling factor. The scaling factor controls the number of resources allotted to a virtual warehouse, giving users cost control benefits, as users can limit the number of resources allocated to the warehouse to avoid incurring unnecessary costs.

6. Improved user experience:

With fast query performance users experience responsive and efficient analysis. It improves user satisfaction and productivity.

7. Insights and Monitoring query profile

monitoring users can better understand how much credit is being consumed by each query, including those that are accelerated by QAS. It enables users to track and manage their credit usage, ensuring that they don’t exceed their credit limits and incur expected limits.

How to use the Query Acceleration Service in Snowflake

To use the Query Acceleration Service in Snowflake, you simply need to enable it for your account. This can be done through the Snowflake web Interface or through the Snowflake API. Once you have enabled the Query Acceleration Service, you can start using it to optimize your queries and boost your performance.

How to enable the Query Acceleration Service?

It is not enabled by default, but it can be quickly deployed against a warehouse using:

alter warehouse TEST set
enable_query_acceleration = true
query_acceleration_max_scale_factor = 16;

The above query Activates the Query Acceleration Service which expands the capabilities of the virtual warehouse to allocate up to 16 times the size of the original warehouse. For example, an XSMALL warehouse with just one database server will automatically expand to 16 servers, while a large warehouse with 8 database servers would grow to 128 servers. This allows you to run massive queries on servers. You also have the option to set the scaling factor to zero, which gives Snowflake flexibility to scale QAS as large as necessary for the query.

· Max_scale_factor is an upper bound to the amount of compute resources a warehouse can lease for query acceleration.
· If the scale factor is not set, then the default value is 8.
· Setting the scale factor to 0, eliminates the upper bound limit and allows queries to lease as many resources as necessary.

Which Queries benefit from Query Acceleration Service?

Large and Complex queries: Queries that require a large amount of data processing and manipulation, such as those that involve complex aggregations, join operations, and data transformations, are good candidates for acceleration with QAS. By offloading some of the processing to dedicated compute resources, QAS can help speed up the execution of these types of queries and improve overall performance.
Data exploration queries: Queries that are used to explore and analyze large data sets, such as those that generate reports or perform data analysis. These types of queries, typically involve scanning large amounts of data and performing complex calculations, so QAS can help improve their performance and reduce query execution time.
Time-sensitive queries: Queries that are required to run in real-time, such as those that support business-critical processes, can also benefit from QAS, by accelerating these queries, QAS can help ensure that results are delivered quickly and accurately, which is essential for time-sensitive applications.
Repeatable queries: Queries that run repeatedly, such as those used for scheduled reporting or data analysis, can also benefit from QAS. By accelerating these queries, QAS can help reduce the amount of time required to run them, which can be especially useful for organizations that need to perform these types of queries frequently.

Identifying Warehouses and Queries that benefit from query acceleration

To identify queries that benefit from query acceleration you can use:





Copy the query_id generated after running the result, and paste that id into the syntax as shown below
If the output query is already eligible for QAS, it will give status as eligible.
“originalQueryTime”: 451.064,
“status”: “eligible”,
upperLimitScaleFactor: 17
This response indicates that, a query is eligible for QAS and the original query time will be reduced by applying QAS.
o If the output of the query is ineligible, then the status of QAS is also ineligible.

“estimatedQueryTimes”: {},   
“originalQueryTime”: 816.106,   
“queryUUID”: “01aa579d-3200-a7b3-0003-51900011402”,   
“status”: “ineligible”,   
“upperLimitScaleFactor”: 0

Overall, this response means that the QAS cannot provide any estimated query times or scaling factors for it. The original query time is 0, indicating that it may not have run successfully. It also indicates that the query with a specific Id is not found.

    “estimatedQueryTimes”: {},
    “originalQueryTime”: 53.404,
    “queryUUID”: “01aa7382-3200-a91b-0003-519200040046”,
    “status”: “accelerated”,
    “upperLimitScaleFactor”: 0

    The response indicates that the query is ineligible for QAS and the original time cannot be reduced by applying QAS.

    • If the output query is accelerated, then the status of QAS is accelerated

    It is a system-defined view in the Snowflake that provides information about the queries that are eligible for query acceleration. This view is used to identify the queries for acceleration and to determine the acceleration eligibility of a particular query.

    By querying this view, users can get insights into the queries that are eligible for acceleration and optimize their queries accordingly. The view provides a useful way to understand which queries are the best for acceleration in Snowflake.

    Monitoring Query Acceleration

    This SQL query retrieves data from four tables -store sales, store returns, item, and date dim and retrieves aggregated data on sales and returns of a specific brand of products.

    Overall, the query retrieves aggregated data on sales and returns of a specific brand of products, grouped by year, brand ID, and brand name, and sorted by certain columns. The query results are limited to 200 rows.

    select d_year AS ss_sold_year, i.i_brand_id ,i.i_brand ,
    sum(ss_quantity) ss_qty,sum(ss_wholesale_cost) ss_wc,sum(ss_sales_price) ss_sp
    from store_sales s,store_returns sr,item i,date_dim d
    s.ss_ticket_number = sr.sr_ticket_number and s.ss_sold_date_sk = d.d_date_sk and
    s.ss_item_sk = i.i_item_sk and i.i_manufact_id = 939 and d_qoy = 4
    group by d_year, i_brand_id, i_brand
    order by 1,4,5,2
    limit 200;

    Monitoring Query Profile
    • After enabling the Query Acceleration Service, you can check the Query Profile’s overview panel to observe the impact of query acceleration on the query performance.
    • This panel displays various statistics of query, including the results of multiple advanced operations.
    • If there are multiple accelerated operations in a query, the results are combined in this panel to show the total amount of work done by the Query Acceleration Service.
    • The provided screenshot is an example of the type of statistics that are displayed in the Profile Overview panel.
      Partitions scanned by service- This shows a number of files sent for scanning to the QAS.
    • Scans selected for acceleration- This indicates the number of table scans that have been chosen for acceleration.
    Monitor Billing from classic web interface Insights
    • When Query Acceleration is enabled for an account, the billing page on the web interface provides information about the usage of the services across all warehouses in the account.
    • This includes dedicated warehouses in the account. This includes a dedicated warehouse called QUERY_ACCELERATION that displays all the credits consumed by the service.
    • The provided screenshot shows an example of the billing information that appears on the page for the QUERY_ACCELERATION warehouse.


    Data Engineer

    Boolean Data Systems

    Naziya works as a Data Engineer at Boolean Data Systems and has built many end-end Data Engineering solutions. She is a Matillion Associate certified engineer who has proven skills and expertise with Snowflake, Matillion, Python to name a few.


    Snowflake’s Query Acceleration Service is a powerful tool for improving the performance of your queries. By caching frequently executed queries and optimizing query execution, the Query Acceleration Service can help you get the results you need more quickly and efficiently. If you are looking to boost the performance of your queries in Snowflake, the Query Acceleration service is a must-have tool.

    About Boolean Data

    Boolean Data Systems is a Snowflake Select Services partner that implements solutions on cloud platforms. we help enterprises make better business decisions with data and solve real-world business analytics and data problems.

    Head Quarters

    1255 Peachtree Parkway, Suite #4204, Alpharetta, GA 30041, USA.
    Ph. : +1 678-261-8899
    Fax : (470) 560-3866