Trying Out Snowflake's Adaptive Warehouse — Auto-Scaling Compute Without Manual Sizing

This is an English translation of the original Japanese article:
https://dev.classmethod.jp/articles/snowflake-try-adaptive-warehouse/

Until now, Snowflake warehouses required you to manually select and manage a size (XS, S, M, L, etc.). With Adaptive Warehouse, compute resources automatically scale based on query characteristics. This eliminates the need for manual warehouse sizing and scale-up/scale-out configuration.

I learned about this feature from @tshowis's post:

https://x.com/tshowis/status/2044446136556282293

While release notes haven't been published yet, the official documentation has full details. The Public Preview supported regions include AWS Tokyo Region (AP Northeast 1), so those using the Tokyo region can try it right away. I tested how it works using TPC-H sample data and have summarized the steps and results below.

https://docs.snowflake.com/en/user-guide/warehouses-adaptive

Feature Overview

Adaptive Warehouse is a compute service that recognizes workloads and automatically allocates resources. It has a dedicated shared compute pool per account and automatically determines the optimal resources based on query characteristics.

Key features include:

• No warehouse sizing required: No need to manually select or change sizes
• Automatic per-query scaling: Small queries use fewer resources, large queries automatically use more
• Online conversion: Converting existing standard warehouses to Adaptive Warehouses requires no downtime
• Behavior controlled by 2 parameters

The two controllable parameters are:

Setting QUERY_THROUGHPUT_MULTIPLIER to N ensures capacity for N queries to run concurrently at the MAX_QUERY_PERFORMANCE_LEVEL resource level.

Limitations

• As of April 16, 2026, this is a Public Preview feature. Specifications may change before GA
• Available regions (Public Preview): US West 2 (Oregon), EU West 1 (Ireland), AP Northeast 1 (Tokyo)
• Required edition: Enterprise Edition or higher
• Conversion from X5Large/X6Large warehouses to Adaptive Warehouse is not supported
• Mutual conversion with Snowpark-optimized warehouses and Interactive warehouses is also not supported

Cost

Adaptive Warehouse uses a query-based billing model. Each query's cost is calculated based on the compute resources and software resources consumed. The total warehouse cost is the sum of all executed query costs.

Creating a warehouse itself incurs no cost — billing begins when queries are executed. For cost management, you can use Budgets, Resource Monitors, and views in SNOWFLAKE.ACCOUNT_USAGE for monitoring.

Prerequisites

• Snowflake: AWS US Oregon region, Enterprise Edition
  • I happened to have a trial account, so I tested on the US Oregon region

Preparation

Verify TPC-H Sample Data

For this test, I used the TPC-H sample data provided by default in Snowflake. TPC-H is a decision support benchmark dataset well-suited for evaluating large-scale data processing with complex joins and aggregations.

https://docs.snowflake.com/en/user-guide/sample-data-tpch

Multiple schemas with different scale factors (SF) are available in the SNOWFLAKE_SAMPLE_DATA database:

To clearly observe the automatic scaling behavior, I used TPCH_SF1000 (billions of records).

First, verify that you can access the sample data:

-- Verify sample data access
USE SCHEMA SNOWFLAKE_SAMPLE_DATA.TPCH_SF1000;

-- Check table list
SHOW TABLES;

You should see 8 tables (LINEITEM, ORDERS, CUSTOMER, SUPPLIER, PART, PARTSUPP, NATION, REGION).

Disable Account-Level Cache

To accurately compare execution times before and after parameter tuning, disable the query result cache. With caching enabled, subsequent identical queries would be served from cache, making execution time comparison impossible.

https://docs.snowflake.com/en/sql-reference/parameters#use-cached-result

Disable caching at the account level:

USE ROLE ACCOUNTADMIN;

-- Disable query result cache at account level
ALTER ACCOUNT SET USE_CACHED_RESULT = false;

Verify the parameter is set to false:

-- Confirm the setting
SHOW PARAMETERS LIKE '%USE_CACHED_RESULT%';

⚠️ If you're using a production account, don't forget to re-enable caching after testing:

USE ROLE ACCOUNTADMIN;
ALTER ACCOUNT SET USE_CACHED_RESULT = true;

Hands-On Testing

1. Create an Adaptive Warehouse (XLARGE Setting)

First, create a new Adaptive Warehouse with MAX_QUERY_PERFORMANCE_LEVEL = XLARGE:

USE ROLE SYSADMIN;

-- Create Adaptive Warehouse with XLARGE setting
CREATE ADAPTIVE WAREHOUSE adaptive_wh_xlarge
  WITH MAX_QUERY_PERFORMANCE_LEVEL = XLARGE
       QUERY_THROUGHPUT_MULTIPLIER = 2;

Verify the configuration:

-- Check warehouse settings
SHOW WAREHOUSES LIKE 'adaptive_wh_xlarge';

Confirm that the type column shows ADAPTIVE.

2. Run TPC-H Queries with XLARGE Setting

Run representative TPC-H queries using adaptive_wh_xlarge.

Set QUERY_TAG so we can compare execution times later:

-- Set QUERY_TAG for later comparison
ALTER SESSION SET QUERY_TAG = 'adaptive_xlarge_m2';

USE WAREHOUSE adaptive_wh_xlarge;
USE SCHEMA SNOWFLAKE_SAMPLE_DATA.TPCH_SF1000;

TPC-H Q1: Aggregation Query (Full LINEITEM Table Scan)

First, run TPC-H Q1 (Pricing Summary Report) — one of the simplest TPC-H queries. It performs a full table scan and aggregation on the LINEITEM table (approximately 6 billion records at SF1000).

-- TPC-H Q1: Pricing Summary Report
-- Aggregates the LINEITEM table (~6 billion records at 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
WHERE
    L_SHIPDATE <= DATEADD(DAY, -90, TO_DATE('1998-12-01'))
GROUP BY
    L_RETURNFLAG,
    L_LINESTATUS
ORDER BY
    L_RETURNFLAG,
    L_LINESTATUS;

Results after 5 runs:

TPC-H Q5: Multi-Table Join Query

Next, run Q5 (Local Supplier Volume), which joins multiple tables:

-- TPC-H Q5: Local Supplier Volume
-- Complex query joining 6 tables
SELECT
    N.N_NAME,
    SUM(L.L_EXTENDEDPRICE * (1 - L.L_DISCOUNT)) AS REVENUE
FROM
    CUSTOMER   C,
    ORDERS     O,
    LINEITEM   L,
    SUPPLIER   S,
    NATION     N,
    REGION     R
WHERE
    C.C_CUSTKEY    = O.O_CUSTKEY
    AND L.L_ORDERKEY  = O.O_ORDERKEY
    AND L.L_SUPPKEY   = S.S_SUPPKEY
    AND C.C_NATIONKEY = S.S_NATIONKEY
    AND S.S_NATIONKEY = N.N_NATIONKEY
    AND N.N_REGIONKEY = R.R_REGIONKEY
    AND R.R_NAME      = 'ASIA'
    AND O.O_ORDERDATE >= TO_DATE('1994-01-01')
    AND O.O_ORDERDATE <  DATEADD(YEAR, 1, TO_DATE('1994-01-01'))
GROUP BY
    N.N_NAME
ORDER BY
    REVENUE DESC;

Results after 5 runs:

3. Create a Comparison Adaptive Warehouse (SMALL Setting)

Create a separate Adaptive Warehouse with MAX_QUERY_PERFORMANCE_LEVEL = SMALL:

USE ROLE SYSADMIN;

-- Create Adaptive Warehouse with SMALL setting
CREATE ADAPTIVE WAREHOUSE adaptive_wh_small
  WITH MAX_QUERY_PERFORMANCE_LEVEL = SMALL
       QUERY_THROUGHPUT_MULTIPLIER = 2;

-- Check warehouse settings
SHOW WAREHOUSES LIKE 'adaptive_wh_small';

Confirm that the type column shows ADAPTIVE.

4. Run TPC-H Queries with SMALL Setting

Run the same Q1 and Q5 queries using adaptive_wh_small:

-- Set QUERY_TAG for SMALL setting runs
ALTER SESSION SET QUERY_TAG = 'adaptive_small_m2';

USE WAREHOUSE adaptive_wh_small;
USE SCHEMA SNOWFLAKE_SAMPLE_DATA.TPCH_SF1000;

TPC-H Q1: Aggregation Query

-- Q1 execution (MAX_QUERY_PERFORMANCE_LEVEL = SMALL)
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
WHERE
    L_SHIPDATE <= DATEADD(DAY, -90, TO_DATE('1998-12-01'))
GROUP BY
    L_RETURNFLAG,
    L_LINESTATUS
ORDER BY
    L_RETURNFLAG,
    L_LINESTATUS;

Results after 5 runs:

TPC-H Q5: Multi-Table Join Query

-- Q5 execution (MAX_QUERY_PERFORMANCE_LEVEL = SMALL)
SELECT
    N.N_NAME,
    SUM(L.L_EXTENDEDPRICE * (1 - L.L_DISCOUNT)) AS REVENUE
FROM
    CUSTOMER   C,
    ORDERS     O,
    LINEITEM   L,
    SUPPLIER   S,
    NATION     N,
    REGION     R
WHERE
    C.C_CUSTKEY    = O.O_CUSTKEY
    AND L.L_ORDERKEY  = O.O_ORDERKEY
    AND L.L_SUPPKEY   = S.S_SUPPKEY
    AND C.C_NATIONKEY = S.S_NATIONKEY
    AND S.S_NATIONKEY = N.N_NATIONKEY
    AND N.N_REGIONKEY = R.R_REGIONKEY
    AND R.R_NAME      = 'ASIA'
    AND O.O_ORDERDATE >= TO_DATE('1994-01-01')
    AND O.O_ORDERDATE <  DATEADD(YEAR, 1, TO_DATE('1994-01-01'))
GROUP BY
    N.N_NAME
ORDER BY
    REVENUE DESC;

Results after 5 runs:

5. Compare Execution Times Using INFORMATION_SCHEMA.QUERY_HISTORY

After completing runs on both warehouses, compare the two QUERY_TAG values using INFORMATION_SCHEMA.QUERY_HISTORY(). Unlike ACCOUNT_USAGE views, this can be queried without delay.

View All 20 Individual Run Times

First, check all 20 individual execution records (Q1 and Q5 × 5 runs each × 2 warehouses). Queries containing L_RETURNFLAG in QUERY_TEXT are identified as Q1, and those containing N_NAME as Q5.

-- All 20 execution records (check execution times)
SELECT
    QUERY_TAG,
    WAREHOUSE_NAME,
    CASE
        WHEN QUERY_TEXT ILIKE '%L_RETURNFLAG%' THEN 'Q1'
        WHEN QUERY_TEXT ILIKE '%N_NAME%'       THEN 'Q5'
        ELSE 'OTHER'
    END                                       AS query_name,
    START_TIME,
    ROUND(EXECUTION_TIME / 1000, 2)           AS exec_sec
FROM TABLE(
    INFORMATION_SCHEMA.QUERY_HISTORY(
        END_TIME_RANGE_START => DATEADD(HOUR, -2, CURRENT_TIMESTAMP()),
        END_TIME_RANGE_END   => CURRENT_TIMESTAMP(),
        RESULT_LIMIT         => 1000
    )
)
WHERE WAREHOUSE_NAME   IN ('ADAPTIVE_WH_XLARGE', 'ADAPTIVE_WH_SMALL')
  AND QUERY_TAG        IN ('adaptive_xlarge_m2', 'adaptive_small_m2')
  AND EXECUTION_STATUS = 'SUCCESS'
  AND QUERY_TYPE       = 'SELECT'
ORDER BY QUERY_TAG, query_name, START_TIME;

Aggregated Comparison

Check whether lowering MAX_QUERY_PERFORMANCE_LEVEL is reflected in execution_time. I also included queued_overload_time to observe any queuing trends.

-- Aggregate execution times by QUERY_TAG × query type for comparison
-- Only target the most recent 5 SELECT statements per QUERY_TAG × query_name
WITH ranked AS (
    SELECT
        QUERY_TAG,
        WAREHOUSE_NAME,
        CASE
            WHEN QUERY_TEXT ILIKE '%L_RETURNFLAG%' THEN 'Q1'
            WHEN QUERY_TEXT ILIKE '%N_NAME%'       THEN 'Q5'
            ELSE 'OTHER'
        END                  AS query_name,
        EXECUTION_TIME,
        TOTAL_ELAPSED_TIME,
        QUEUED_OVERLOAD_TIME,
        ROW_NUMBER() OVER (
            PARTITION BY QUERY_TAG,
                         CASE
                             WHEN QUERY_TEXT ILIKE '%L_RETURNFLAG%' THEN 'Q1'
                             WHEN QUERY_TEXT ILIKE '%N_NAME%'       THEN 'Q5'
                             ELSE 'OTHER'
                         END
            ORDER BY START_TIME DESC
        ) AS rn
    FROM TABLE(
        INFORMATION_SCHEMA.QUERY_HISTORY(
            END_TIME_RANGE_START => DATEADD(HOUR, -2, CURRENT_TIMESTAMP()),
            END_TIME_RANGE_END   => CURRENT_TIMESTAMP(),
            RESULT_LIMIT         => 1000
        )
    )
    WHERE WAREHOUSE_NAME   IN ('ADAPTIVE_WH_XLARGE', 'ADAPTIVE_WH_SMALL')
      AND QUERY_TAG        IN ('adaptive_xlarge_m2', 'adaptive_small_m2')
      AND EXECUTION_STATUS = 'SUCCESS'
      AND QUERY_TYPE       = 'SELECT'
)
SELECT
    QUERY_TAG,
    WAREHOUSE_NAME,
    query_name,
    COUNT(*)                                           AS run_count,
    ROUND(AVG(EXECUTION_TIME)       / 1000, 2)        AS avg_exec_sec,
    ROUND(MIN(EXECUTION_TIME)       / 1000, 2)        AS min_exec_sec,
    ROUND(MAX(EXECUTION_TIME)       / 1000, 2)        AS max_exec_sec,
    ROUND(AVG(TOTAL_ELAPSED_TIME)   / 1000, 2)        AS avg_elapsed_sec,
    ROUND(AVG(QUEUED_OVERLOAD_TIME) / 1000, 2)        AS avg_queued_overload_sec
FROM ranked
WHERE rn <= 5
GROUP BY QUERY_TAG, WAREHOUSE_NAME, query_name
ORDER BY QUERY_TAG, query_name;

Here are the summarized results in table format.

You can see that adaptive_small_m2 has longer avg_exec_sec than adaptive_xlarge_m2 (execution time increases as the performance cap is lowered).

Analysis

Both Q1 and Q5 showed significantly shorter execution times on the XLARGE warehouse compared to the SMALL warehouse.

Q5 showed a larger ratio than Q1. Since Q5 involves JOINs across 6 tables, it's a more complex query where the difference in available resources becomes more pronounced.

Testing Note: Warehouse Data Cache

There's an important point to keep in mind about this test. Snowflake warehouses have a data cache (local disk cache) separate from the query result cache. This mechanism retains micro-partitions read from remote storage on the warehouse's local SSD.

In this test, I disabled the query result cache at the account level with USE_CACHED_RESULT = false, but I did not disable the data cache. Therefore, when running the same query consecutively on the same warehouse, the 2nd run onward reads data from local SSD, making it faster than the first run.

Looking at the actual data, the XLARGE warehouse's Q5 first run took 5.74 seconds, while runs 2–5 dropped to 2.83–3.17 seconds, confirming the data cache effect.

Checking the query profile, the 2nd run shows a certain percentage for Percentage scanned from cache, confirming that the warehouse data cache was in effect.

SMALL Q5: 1st Run Query Profile

SMALL Q5: 2nd Run Query Profile

Since the purpose of this test was to confirm the "relative difference between SMALL and XLARGE," both warehouses were compared under the same conditions (same cache state), making the relative comparison valid. However, if you want to measure pure cold-start performance, note that you need to suspend and restart the warehouse to clear the data cache before measuring.

Conclusion

I tested Snowflake's new Adaptive Warehouse feature using TPC-H sample data.

Comparing MAX_QUERY_PERFORMANCE_LEVEL at XLARGE vs. SMALL, I observed approximately a 6.8x difference for Q1 (aggregation query) and approximately a 10.9x difference for Q5 (6-table JOIN). More complex JOIN queries tend to show a more pronounced difference based on resource allocation, confirming that the MAX_QUERY_PERFORMANCE_LEVEL setting directly impacts performance.

The most impressive aspect was being able to control the "cost vs. performance balance" with just two parameters (MAX_QUERY_PERFORMANCE_LEVEL and QUERY_THROUGHPUT_MULTIPLIER) without thinking about warehouse sizes at all. Previously, you had to make specific size choices like "XL or XXL," but Adaptive Warehouse automates that while letting you simply specify an upper limit — a much simpler design.

This feature is available in Public Preview for Enterprise Edition and above. In addition to the US Oregon region (US West 2) used in this test, it's also available in EU West 1 (Ireland) and AP Northeast 1 (Tokyo). If you're using the Tokyo region, you can try it immediately with the same steps described here — give it a try!