Snowflake Query Acceleration Service (QAS)

Published in

DemoHub Tutorials

6 min readJul 18, 2024

Note: This tutorial is self-contained, with sample data and scripts provided to ensure you can follow the exercise in your practice environment. For more tutorials, visit tutorials.demohub.dev

Imagine flipping a simple switch and slashing your query execution time by over 65% — that’s the kind of dramatic performance improvement Snowflake’s Query Acceleration Service (QAS) can deliver. By seamlessly offloading resource-intensive tasks to dedicated compute resources, QAS can supercharge your queries and deliver insights at lightning speed, all without the need to constantly upgrade your warehouse size.

In this hands-on tutorial, we’ll dive into how QAS works, how to enable it, and how to leverage it effectively using the TPC-H dataset to witness this incredible acceleration firsthand.

Enabling QAS: A Simple Flip of a Switch

Enabling QAS is remarkably easy. You simply need to set the QUERY_ACCELERATION parameter on your warehouse to TRUE:

-- Enable QAS on a new warehouse
CREATE WAREHOUSE my_wh
 QUERY_ACCELERATION = TRUE;

-- Enable QAS on an existing warehouse
ALTER WAREHOUSE my_wh
 SET QUERY_ACCELERATION = TRUE;

Controlling the Power: Scale Factor

With great power comes the potential for increased costs. Fortunately, Snowflake allows you to control the maximum scale factor for leasing compute resources using the QUERY_ACCELERATION_MAX_SCALE_FACTOR parameter. This value acts as a multiplier for the current warehouse size, determining the upper limit of resources available to QAS.

-- Set maximum scale factor (8x the current warehouse size) 
ALTER WAREHOUSE my_wh
 SET QUERY_ACCELERATION_MAX_SCALE_FACTOR = 8;

Setting a higher scale factor allows for greater acceleration potential but may incur higher costs. Snowflake intelligently decides how many resources to allocate based on the query’s needs, so you won’t always be using the maximum.

QAS Eligibility: Which Queries Benefit?

Not every query is eligible for QAS acceleration. Currently, QAS supports:

SELECT
INSERT-SELECT
CREATE TABLE AS SELECT (CTAS)

To benefit from QAS, these queries should also involve scanning or processing large amounts of data. Snowflake has built-in functions and views to help you identify which queries are eligible for acceleration and estimate the potential time savings:

-- Check eligibility for a specific query using its ID
SELECT SYSTEM$ESTIMATE_QUERY_ACCELERATION('<query_id>');

-- View all queries eligible for acceleration in the account
SELECT * FROM SNOWFLAKE.ACCOUNT_USAGE.QUERY_ACCELERATION_ELIGIBLE;

Monitoring QAS Usage and Cost

Snowflake provides transparency into QAS usage and costs:

QUERY_HISTORY View: This view includes columns indicating QAS usage and credits consumed for each query.

-- Get detailed QAS usage for each query
SELECT
 QUERY_ID,
 QUERY_TEXT,
 WAREHOUSE_NAME,
 EXECUTION_STATUS,
 EXECUTION_TIME,
 TOTAL_ELAPSED_TIME,
 QUERY_ACCELERATION_BYTES_SCANNED, -- Bytes scanned by QAS
 QUERY_ACCELERATION_PARTITIONS_SCANNED, -- Number of partitions scanned by QAS
 QUERY_ACCELERATION_UPPER_LIMIT_SCALE_FACTOR
FROM snowflake.account_usage.query_history
WHERE WAREHOUSE_NAME = 'WITH_QAS_WH' -- Replace with your warehouse name
 AND QUERY_ACCELERATION_BYTES_SCANNED > 0 -- Filter for queries that used QAS
ORDER BY START_TIME DESC;

QUERY_ACCELERATION_HISTORY View: This new view shows the total QAS cost per warehouse, helping you track the additional spending due to QAS.

-- Get total QAS cost per warehouse (month-to-date)
SELECT
 WAREHOUSE_NAME,
 SUM(CREDITS_USED) AS total_credits_used
FROM snowflake.account_usage.query_acceleration_history
WHERE START_TIME >= DATE_TRUNC('MONTH', CURRENT_DATE) -- Filter for the current month
GROUP BY 1
ORDER BY 2 DESC;

Hands-On Exercise:

Let’s experience the power of QAS first hand using Snowflake’s sample TPC-H dataset, designed to simulate complex queries on large datasets. We’ll compare query performance with and without QAS enabled.

1. Preparation: Set up Warehouses and Sample Data

Create two X-Small warehouses, one with QAS enabled and one without:

-- Warehouse with QAS Enabled
CREATE OR REPLACE WAREHOUSE with_qas_wh
 WAREHOUSE_SIZE = 'X-SMALL'
 AUTO_SUSPEND = 600
 AUTO_RESUME = TRUE
 MIN_CLUSTER_COUNT = 1
 MAX_CLUSTER_COUNT = 2 
 SCALING_POLICY = 'STANDARD' -- or 'ECONOMY'
 ENABLE_QUERY_ACCELERATION = true;

-- Warehouse without QAS Enabled
CREATE OR REPLACE WAREHOUSE without_qas_wh
 WAREHOUSE_SIZE = 'X-SMALL'
 AUTO_SUSPEND = 600
 AUTO_RESUME = TRUE
 MIN_CLUSTER_COUNT = 1
 MAX_CLUSTER_COUNT = 2 
 SCALING_POLICY = 'STANDARD'; -- or 'ECONOMY'

Load the TPC-H sample data from Snowflake’s shared repository:

-- Create a database from the share.
CREATE OR DATABASE SNOWFLAKE_SAMPLE_DATA FROM SHARE SFC_SAMPLES.SAMPLE_DATA;
 
-- Grant the PUBLIC role access to the database.
 -- Optionally change the role name to restrict access to a subset of users.
GRANT IMPORTED PRIVILEGES ON DATABASE SNOWFLAKE_SAMPLE_DATA TO ROLE PUBLIC;

2. Running the Test Queries

Query 1: Complex Aggregation on lineitem Table (6 Billion Rows)

This query aggregates and calculates various metrics on the lineitem table within the snowflake_sample_data.tpch_sf1000 schema:

WITHOUT QAS

-- Without QAS

USE WAREHOUSE without_qas_wh;
USE SCHEMA snowflake_sample_data.tpch_sf1000; 

select
       l_returnflag,
       l_linestatus,
       sum(l_quantity) as sum_qty,
       sum(l_extendedprice) as sum_base_price,
       sum(l_extendedprice * (1-l_discount)) as sum_disc_price,
       sum(l_extendedprice * (1-l_discount) * (1+l_tax)) as sum_charge,
       avg(l_quantity) as avg_qty,
       avg(l_extendedprice) as avg_price,
       avg(l_discount) as avg_disc,
       count(*) as count_order
 from
       lineitem
 group by
       l_returnflag,
       l_linestatus
 order by
       l_returnflag,
       l_linestatus;

WITH QAS

-- With QAS
USE WAREHOUSE with_qas_wh;
USE SCHEMA snowflake_sample_data.tpch_sf1000; 

select
       l_returnflag,
       l_linestatus,
       sum(l_quantity) as sum_qty,
       sum(l_extendedprice) as sum_base_price,
       sum(l_extendedprice * (1-l_discount)) as sum_disc_price,
       sum(l_extendedprice * (1-l_discount) * (1+l_tax)) as sum_charge,
       avg(l_quantity) as avg_qty,
       avg(l_extendedprice) as avg_price,
       avg(l_discount) as avg_disc,
       count(*) as count_order
 from
       lineitem
 group by
       l_returnflag,
       l_linestatus
 order by
       l_returnflag,
       l_linestatus;

3. Analyzing and Visualizing Results *

Use QUERY_HISTORY View:

SELECT QUERY_ID, WAREHOUSE_NAME, 
EXECUTION_STATUS, EXECUTION_TIME, TOTAL_ELAPSED_TIME, 
QUERY_ACCELERATION_TIME 
FROM snowflake.account_usage.query_history 
WHERE WAREHOUSE_NAME IN ('WITHOUT_QAS_WH', 'WITH_QAS_WH') 
ORDER BY START_TIME DESC;

This query will show you the execution time with and without QAS for the queries you ran, as well as other useful information like warehouse name, execution status, total elapsed time.

* Compare the execution times for both scenarios to quantify the impact of QAS.

Visualizing the Dramatic Performance Difference

Create a simple bar chart or table in SnowSight to compare the execution times of the query with and without QAS.

The percentage difference in duration between the two in this case is 65.57%. The difference can be dramatic, especially for queries that involve significant data processing.

View all queries eligible for acceleration in the account.

Monitor the query in Snowsight’s “Query Profile” or use the QUERY_HISTORY view to see if QAS was utilized and its impact on performance.

-- View all queries eligible for acceleration in the account
SELECT * FROM SNOWFLAKE.ACCOUNT_USAGE.QUERY_ACCELERATION_ELIGIBLE;

Key Takeaways:

QAS can significantly accelerate queries that involve large table scans or complex filters
The QUERY_HISTORY and QUERY_ACCELERATION_ELIGIBLE views provide valuable insights into QAS usage and potential cost savings.
Consider enabling QAS for workloads with varying query patterns to optimize performance and cost.

Resources

Clean Up

If needed, here’s the cleanup script focusing on removing the QAS warehouses and dropping the share created for the TPC-H data:

-- Drop the warehouses used for testing QAS
DROP WAREHOUSE IF EXISTS with_qas_wh;
DROP WAREHOUSE IF EXISTS without_qas_wh;
 -- Drop the SNOWFLAKE_SAMPLE_DATA database (removes TPC-H data)
DROP DATABASE IF EXISTS SNOWFLAKE_SAMPLE_DATA; 
 -- Revoke the imported privileges granted to PUBLIC on the database (optional, if you granted them)
REVOKE IMPORTED PRIVILEGES ON DATABASE SNOWFLAKE_SAMPLE_DATA FROM ROLE PUBLIC;

Originally published at https://tutorials.demohub.dev.