Static warehouse sizing leaves you either overpaying or underperforming. This deep-dive lesson teaches you how to implement Snowflake's Query Acceleration Service and multi-cluster scaling policies together, so your compute adapts dynamically to real workloads — without manual intervention or surprise bills.

Picture this: it's 9:15 AM on a Monday morning. Your executive team just kicked off their weekly business review, and twelve analysts simultaneously fire off complex ad-hoc queries against 500 million rows of sales data. Meanwhile, your dbt pipeline is mid-run transforming your fact tables, and three external dashboards are auto-refreshing. Your XL warehouse starts queuing queries. The CFO's report takes four minutes instead of four seconds. Someone sends a Slack message asking "is Snowflake down?" It isn't. You're just undersized for a workload that looked completely reasonable at 8:50 AM.
This is the defining challenge of analytics compute management: your workload is not uniform, predictable, or polite. Queries arrive in bursts. Some scans need 2 seconds, some need 2 hours. Some months you have three times the analysts you had last quarter. Static warehouse sizing — where you pick an XL and hope for the best — is expensive when idle and painful when overwhelmed. What you actually need is a compute layer that dynamically adapts to query complexity and concurrency in real time, without you having to babysit it at 9 AM every Monday.
By the end of this lesson, you'll understand not just how to configure Query Acceleration Service (QAS) and Snowflake's multi-cluster warehouse scaling policies, but why they work the way they do, when to use each tool, how they interact with each other, and how to instrument your environment so you can tune it intelligently over time. This is the difference between provisioning compute by gut instinct and governing it like an engineer.
What you'll learn:
You should be comfortable with:
Before we touch any configuration, we need to establish a mental model. Snowflake warehouses struggle in two distinct ways, and mixing them up leads to the wrong remediation.
Concurrency problems happen when too many queries arrive simultaneously, filling the query queue. Each individual query might be perfectly reasonable — a 10-second dashboard query — but forty of them arriving in the same second overwhelms a single-cluster warehouse. The fix for concurrency is horizontal scaling: more clusters running in parallel, each handling a subset of the queue. This is what multi-cluster warehouses solve.
Complexity problems happen when a single query is just enormous. A full scan of a 2TB fact table with ten joins, three window functions, and a complex GROUP BY will exhaust the memory and CPU of a given warehouse size regardless of how many other queries are running. The fix for complexity is vertical scaling (using a larger warehouse) or offloading the heavy work to additional compute. This is what Query Acceleration Service solves.
Here's the trap: if you throw multi-cluster scaling at a complexity problem, you'll spin up four clusters and they'll all be waiting on the same slow query. And if you throw QAS at a concurrency problem, you'll burn serverless credits on queries that weren't actually bottlenecked on scan size. These tools are complementary, not interchangeable.
Key insight
Think of your warehouse as a restaurant. Multi-cluster scaling adds more tables and waitstaff when the dining room is packed (concurrency). Query Acceleration Service sends the most complex dish orders to a larger commercial kitchen with extra capacity (complexity). You need both strategies running simultaneously for unpredictable mixed workloads.
A solid approach to any warehouse sizing exercise starts with profiling your queries using QUERY_HISTORY. Let's build that baseline now.
-- Profile your workload by execution time and bytes scanned
-- Run this in ACCOUNTADMIN or SYSADMIN role
SELECT
warehouse_name,
COUNT(*) AS total_queries,
AVG(total_elapsed_time) / 1000 AS avg_elapsed_seconds,
PERCENTILE_CONT(0.95) WITHIN GROUP (ORDER BY total_elapsed_time) / 1000 AS p95_elapsed_seconds,
PERCENTILE_CONT(0.99) WITHIN GROUP (ORDER BY total_elapsed_time) / 1000 AS p99_elapsed_seconds,
AVG(bytes_scanned) / POWER(1024, 3) AS avg_gb_scanned,
MAX(bytes_scanned) / POWER(1024, 3) AS max_gb_scanned,
AVG(queued_overload_time) / 1000 AS avg_queue_wait_seconds,
COUNT(CASE WHEN queued_overload_time > 1000 THEN 1 END) AS queries_that_waited
FROM SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY
WHERE start_time >= DATEADD(day, -14, CURRENT_TIMESTAMP())
AND warehouse_name IS NOT NULL
AND execution_status = 'SUCCESS'
GROUP BY 1
ORDER BY avg_elapsed_seconds DESC;
Look at this output carefully. High avg_queue_wait_seconds with low avg_gb_scanned points to a concurrency problem. High max_gb_scanned with minimal queue wait points to a complexity problem. Many real workloads show both — which is exactly why you need both tools.
Query Acceleration Service is one of Snowflake's more architecturally interesting features, and understanding its internals makes the configuration decisions obvious.
When QAS is enabled on a warehouse, Snowflake's query optimizer evaluates each incoming query to determine whether it's a candidate for acceleration. The key heuristic is whether the query has scan-intensive portions that can be parallelized beyond what the warehouse's own nodes can provide. Specifically, QAS looks for:
When a query qualifies, Snowflake spins up serverless compute workers that work alongside your warehouse. Here's the crucial architectural point: QAS doesn't replace your warehouse for the query — it augments it. Your warehouse handles the final aggregation, result assembly, and lighter operations. The elastic workers handle the heavy scan work. This means your warehouse query slot is freed up faster, which indirectly improves concurrency as a secondary effect.
The serverless workers are billed separately from your warehouse credits. They're billed at approximately 1.5x the standard credit rate (Snowflake's serverless rate), but they can often reduce overall cost by dramatically shortening warehouse active time, particularly if your warehouse would otherwise need to be upsized.
QAS is controlled at the warehouse level, with one key parameter: MAX_CONCURRENCY_LEVEL doesn't affect it, but QUERY_ACCELERATION_MAX_SCALE_FACTOR does.
-- Enable QAS on an existing warehouse
ALTER WAREHOUSE analytics_wh
SET ENABLE_QUERY_ACCELERATION = TRUE
QUERY_ACCELERATION_MAX_SCALE_FACTOR = 8;
-- Or configure during warehouse creation
CREATE WAREHOUSE analytics_wh
WAREHOUSE_SIZE = 'LARGE'
AUTO_SUSPEND = 120
AUTO_RESUME = TRUE
ENABLE_QUERY_ACCELERATION = TRUE
QUERY_ACCELERATION_MAX_SCALE_FACTOR = 8;
The QUERY_ACCELERATION_MAX_SCALE_FACTOR is the most important dial you have. It defines the maximum ratio of serverless compute to warehouse compute that can be used for a single query. With a scale factor of 8, a LARGE warehouse (8 nodes) can burst to access up to 64 serverless nodes for a qualifying query. The valid range is 0–100, with 0 meaning acceleration is enabled but uncapped (Snowflake decides how much is needed), and anything 1–100 capping the multiplier.
Warning
Setting QUERY_ACCELERATION_MAX_SCALE_FACTOR = 0 does not disable QAS — it removes the cap. This can be appropriate for one-off analytical workloads where you want maximum performance, but on a shared analytics warehouse with 20 concurrent users, it can produce surprise bills when a runaway query triggers massive serverless scaling. Always set an explicit cap on shared warehouses.
Understanding which of your queries actually qualify for acceleration before enabling it broadly is good practice. Snowflake provides a function for this:
-- Identify which recent queries would qualify for QAS
-- and estimate the compute they'd use
SELECT
query_id,
query_text,
total_elapsed_time / 1000 AS elapsed_seconds,
bytes_scanned / POWER(1024, 3) AS gb_scanned,
SYSTEM$ESTIMATE_QUERY_ACCELERATION(query_id) AS acceleration_estimate
FROM SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY
WHERE start_time >= DATEADD(day, -7, CURRENT_TIMESTAMP())
AND warehouse_name = 'ANALYTICS_WH'
AND total_elapsed_time > 30000 -- queries over 30 seconds
AND bytes_scanned > 1073741824 -- queries scanning more than 1GB
ORDER BY bytes_scanned DESC
LIMIT 50;
The SYSTEM$ESTIMATE_QUERY_ACCELERATION function returns a JSON object with an eligible boolean and an upperLimitScaleFactor estimate. Parse it like this:
SELECT
query_id,
elapsed_seconds,
gb_scanned,
TRY_PARSE_JSON(acceleration_estimate):eligible::BOOLEAN AS is_eligible,
TRY_PARSE_JSON(acceleration_estimate):upperLimitScaleFactor::INTEGER AS recommended_scale_factor,
TRY_PARSE_JSON(acceleration_estimate):estimatedQueryTimes.originalQueryTime::FLOAT AS original_time,
TRY_PARSE_JSON(acceleration_estimate):estimatedQueryTimes.acceleratedQueryTime::FLOAT AS accelerated_time
FROM (
SELECT
query_id,
total_elapsed_time / 1000 AS elapsed_seconds,
bytes_scanned / POWER(1024, 3) AS gb_scanned,
SYSTEM$ESTIMATE_QUERY_ACCELERATION(query_id) AS acceleration_estimate
FROM SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY
WHERE start_time >= DATEADD(day, -7, CURRENT_TIMESTAMP())
AND warehouse_name = 'ANALYTICS_WH'
AND total_elapsed_time > 30000
LIMIT 100
)
WHERE TRY_PARSE_JSON(acceleration_estimate):eligible::BOOLEAN = TRUE
ORDER BY (original_time - accelerated_time) DESC;
This gives you a prioritized view of the queries that would benefit most, ranked by estimated time savings. If you see dozens of queries with 5-10x estimated speedup, QAS is a strong candidate. If the eligible list is thin and the savings modest, the problem is elsewhere.
Once QAS is running, you need to track whether it's actually firing and whether it's cost-effective. The QUERY_ACCELERATION_HISTORY view in ACCOUNT_USAGE is your friend:
-- Weekly QAS usage summary
SELECT
DATE_TRUNC('day', start_time) AS usage_date,
warehouse_name,
COUNT(*) AS accelerated_queries,
SUM(credits_used) AS qas_credits_used,
AVG(upper_limit_scale_factor) AS avg_scale_factor_used,
SUM(num_files_scanned) AS total_files_scanned
FROM SNOWFLAKE.ACCOUNT_USAGE.QUERY_ACCELERATION_HISTORY
WHERE start_time >= DATEADD(day, -30, CURRENT_TIMESTAMP())
GROUP BY 1, 2
ORDER BY 1 DESC, 3 DESC;
Compare QAS credits against time saved. If you're spending 50 QAS credits per day but accelerated queries now take 20% of their previous time — and those queries were blocking your warehouse for a large chunk of the day — the math usually works in your favor. If QAS is running on queries that only improved by 10% and they weren't causing concurrency problems, reconsider.
A multi-cluster warehouse is fundamentally a pool of warehouses that share a name and configuration, but are provisioned and deprovisioned dynamically based on load. From the perspective of your SQL clients, nothing changes — connections go to ANALYTICS_WH regardless of whether it's one cluster or six. Internally, Snowflake's load balancer distributes queries across available clusters.
Each cluster in a multi-cluster warehouse is independently sized (they all share the same WAREHOUSE_SIZE) and independently billed. If you have a LARGE multi-cluster warehouse running 4 clusters, you're paying for 4 × LARGE at every minute all four are running. This is why the scaling policy — the logic that governs when to spin up and spin down clusters — matters enormously for your bill.
Snowflake offers two scaling policies with meaningfully different behavior:
Standard scaling spins up additional clusters immediately when queries begin queuing. It's optimized for performance: if any query has to wait, start another cluster. Cluster scale-down begins when a cluster has been idle for the standard AUTO_SUSPEND period.
Economy scaling waits until it's projected that a new cluster can be kept busy for at least 6 minutes before spinning one up. It's optimized for cost: don't start a cluster unless you're confident you'll use it. The tradeoff is that some queries experience queue wait while the system builds confidence.
-- Configure a multi-cluster warehouse with economy scaling
CREATE WAREHOUSE analytics_reporting_wh
WAREHOUSE_SIZE = 'MEDIUM'
MIN_CLUSTER_COUNT = 1
MAX_CLUSTER_COUNT = 4
SCALING_POLICY = 'ECONOMY'
AUTO_SUSPEND = 300
AUTO_RESUME = TRUE
ENABLE_QUERY_ACCELERATION = TRUE
QUERY_ACCELERATION_MAX_SCALE_FACTOR = 4;
-- For a workload with predictable bursts requiring fast response
CREATE WAREHOUSE analyst_interactive_wh
WAREHOUSE_SIZE = 'LARGE'
MIN_CLUSTER_COUNT = 1
MAX_CLUSTER_COUNT = 6
SCALING_POLICY = 'STANDARD'
AUTO_SUSPEND = 60
AUTO_RESUME = TRUE
ENABLE_QUERY_ACCELERATION = TRUE
QUERY_ACCELERATION_MAX_SCALE_FACTOR = 8;
Tip
Use STANDARD scaling for your interactive analytics warehouse where analysts are waiting in real time for results. Use ECONOMY scaling for scheduled batch workloads where a 60-second queue wait is inconsequential. Many teams make the mistake of applying STANDARD everywhere and then wondering why their multi-cluster costs are 3x what they expected.
The right MIN_CLUSTER_COUNT setting is often underappreciated. Setting MIN_CLUSTER_COUNT = 1 means you'll always have at least one cluster running (or resumable). Setting MIN_CLUSTER_COUNT = 2 means two clusters are always available during business hours but wastes credits overnight. Consider using Snowflake Tasks or an external scheduler (like Airflow — see Data Pipeline Orchestration with Airflow) to ALTER WAREHOUSE and change MIN_CLUSTER_COUNT based on time of day:
-- Scale up for business hours (run this via a Snowflake Task at 8 AM)
ALTER WAREHOUSE analytics_reporting_wh
SET MIN_CLUSTER_COUNT = 2;
-- Scale back down for overnight (run this via a Snowflake Task at 7 PM)
ALTER WAREHOUSE analytics_reporting_wh
SET MIN_CLUSTER_COUNT = 1;
Multi-cluster warehouses have an important binary mode that's separate from the scaling policy: Maximized vs Auto mode.
In Auto mode (the default for multi-cluster), Snowflake dynamically manages the number of active clusters between MIN and MAX based on demand. This is the adaptive behavior you want for unpredictable workloads.
In Maximized mode, Snowflake always runs all MAX_CLUSTER_COUNT clusters simultaneously. This is useful when you have precisely estimated concurrency requirements and want zero spin-up latency — for example, a Black Friday analytics environment where you know you'll need 6 clusters from 10 AM to 2 PM.
-- Switch to maximized mode for a known high-load period
ALTER WAREHOUSE analytics_reporting_wh
SET MIN_CLUSTER_COUNT = 4
MAX_CLUSTER_COUNT = 4;
-- Return to auto mode after the event
ALTER WAREHOUSE analytics_reporting_wh
SET MIN_CLUSTER_COUNT = 1
MAX_CLUSTER_COUNT = 4;
Note
In Maximized mode, AUTO_SUSPEND and AUTO_RESUME apply to the entire multi-cluster group, not individual clusters. If the whole warehouse suspends, all clusters go down simultaneously. When it resumes, all configured clusters spin up together. This can result in a brief but noticeable latency spike for the first query that triggers resume — something to account for in SLA-sensitive environments.
The most sophisticated thing you can do with Snowflake compute governance isn't tuning a single warehouse — it's designing a multi-warehouse architecture that segments workloads by their characteristics, then applying the right tools to each segment.
Here's a real-world architecture pattern for a mid-to-large analytics organization:
Tier 1: Interactive Analytics Warehouse Used by: analysts, BI tools, ad-hoc exploration Characteristics: high concurrency, unpredictable query sizes, low latency tolerance Configuration: LARGE, multi-cluster (1–6), STANDARD policy, QAS enabled, scale factor 8–16, AUTO_SUSPEND 60s
Tier 2: Transformation Warehouse Used by: dbt models, your ELT pipeline. If you're familiar with dbt Fundamentals: Transform Data with SQL in Your Warehouse, you know these queries can be highly varied — some incremental models are trivial, some full refreshes are massive. Characteristics: moderate concurrency, predictable scheduling windows, large scans common Configuration: XL, single-cluster (or multi-cluster 1–2 for parallel dbt thread bursts), ECONOMY policy, QAS enabled, scale factor 4
Tier 3: Reporting/Dashboard Warehouse Used by: auto-refreshing Tableau/Looker/Sigma dashboards Characteristics: very high concurrency, predictable queries (same query patterns repeat), moderate sizes Configuration: MEDIUM, multi-cluster (2–8), STANDARD policy, QAS disabled (dashboard queries are typically already optimized and don't scan enough to benefit)
Tier 4: Data Loading Warehouse Used by: Fivetran, Airbyte, COPY INTO operations. For background on these tools, see Data Ingestion with Fivetran, Airbyte, and Custom Connectors Characteristics: low concurrency, I/O bound, credit efficiency matters Configuration: SMALL or XSMALL, single-cluster, QAS disabled, long AUTO_SUSPEND (600s)
-- Full architecture setup
CREATE WAREHOUSE interactive_analytics_wh
WAREHOUSE_SIZE = 'LARGE'
MIN_CLUSTER_COUNT = 1
MAX_CLUSTER_COUNT = 6
SCALING_POLICY = 'STANDARD'
AUTO_SUSPEND = 60
AUTO_RESUME = TRUE
ENABLE_QUERY_ACCELERATION = TRUE
QUERY_ACCELERATION_MAX_SCALE_FACTOR = 12
COMMENT = 'Tier 1: Interactive analyst queries. High concurrency + QAS for complex scans.';
CREATE WAREHOUSE transformation_wh
WAREHOUSE_SIZE = 'X-LARGE'
MIN_CLUSTER_COUNT = 1
MAX_CLUSTER_COUNT = 2
SCALING_POLICY = 'ECONOMY'
AUTO_SUSPEND = 300
AUTO_RESUME = TRUE
ENABLE_QUERY_ACCELERATION = TRUE
QUERY_ACCELERATION_MAX_SCALE_FACTOR = 4
COMMENT = 'Tier 2: dbt and ELT transforms. Large scans, QAS for outlier queries.';
CREATE WAREHOUSE reporting_wh
WAREHOUSE_SIZE = 'MEDIUM'
MIN_CLUSTER_COUNT = 2
MAX_CLUSTER_COUNT = 8
SCALING_POLICY = 'STANDARD'
AUTO_SUSPEND = 120
AUTO_RESUME = TRUE
ENABLE_QUERY_ACCELERATION = FALSE
COMMENT = 'Tier 3: Dashboard auto-refresh. Pure concurrency scaling.';
CREATE WAREHOUSE ingestion_wh
WAREHOUSE_SIZE = 'SMALL'
MIN_CLUSTER_COUNT = 1
MAX_CLUSTER_COUNT = 1
AUTO_SUSPEND = 600
AUTO_RESUME = TRUE
ENABLE_QUERY_ACCELERATION = FALSE
COMMENT = 'Tier 4: Data loading. Cost-optimized, no scaling needed.';
Defining warehouses is only half the battle. You need mechanisms to ensure queries actually land on the right warehouse. There are three primary approaches:
1. Role-based routing: Create Snowflake roles mapped to each warehouse.
-- Grant analysts the interactive warehouse
GRANT USAGE ON WAREHOUSE interactive_analytics_wh TO ROLE analyst;
-- Grant the transform service account the transformation warehouse
GRANT USAGE ON WAREHOUSE transformation_wh TO ROLE dbt_service_role;
-- Grant BI tools the reporting warehouse
GRANT USAGE ON WAREHOUSE reporting_wh TO ROLE bi_service_role;
2. Connection-string routing: Configure your dbt profiles and BI tool connections to explicitly reference the correct warehouse. In your profiles.yml:
analytics_project:
target: prod
outputs:
prod:
type: snowflake
account: "{{ env_var('SNOWFLAKE_ACCOUNT') }}"
user: "{{ env_var('SNOWFLAKE_USER') }}"
role: dbt_service_role
warehouse: transformation_wh # Explicitly routed
database: analytics_db
schema: dbt_prod
threads: 8
private_key_path: "{{ env_var('SNOWFLAKE_PRIVATE_KEY_PATH') }}"
3. Query tagging with warehouse override: For complex pipelines where different steps need different warehouses, use Snowflake's USE WAREHOUSE within sessions:
-- In an Airflow task running a heavy historical backfill
USE WAREHOUSE transformation_wh;
-- ... backfill queries ...
-- Then for a lightweight metadata update in the same pipeline
USE WAREHOUSE ingestion_wh;
-- ... lightweight DML ...
Key insight
Workload segmentation is the most underused lever in Snowflake cost optimization. Most organizations have one or two warehouses doing everything. When you properly segment workloads, QAS tuning and scaling policy tuning become far more effective because each warehouse has a homogeneous workload to respond to.
No adaptive scaling architecture is complete without Resource Monitors. These are credit budgets enforced at the account or warehouse level that trigger alerts or suspensions when thresholds are breached.
-- Create a resource monitor for the interactive analytics warehouse
-- with a monthly budget and graduated alerts
CREATE RESOURCE MONITOR analytics_wh_monitor
WITH CREDIT_QUOTA = 500 -- 500 credits per month
FREQUENCY = MONTHLY
START_TIMESTAMP = IMMEDIATELY
TRIGGERS
ON 50 PERCENT DO NOTIFY -- Email alert at 50%
ON 75 PERCENT DO NOTIFY -- Email alert at 75%
ON 90 PERCENT DO NOTIFY -- Email alert at 90%
ON 100 PERCENT DO SUSPEND -- Suspend new queries at 100%
ON 110 PERCENT DO SUSPEND_IMMEDIATE; -- Kill running queries at 110%
-- Apply to the warehouse
ALTER WAREHOUSE interactive_analytics_wh
SET RESOURCE_MONITOR = analytics_wh_monitor;
Warning
SUSPEND_IMMEDIATE at the resource monitor level will terminate currently-executing queries mid-flight without warning. For analytical workloads where analysts might have been waiting 10 minutes for a result, this is a terrible user experience. Use SUSPEND_IMMEDIATE only as a last resort at a very high threshold (110-120%). The SUSPEND trigger stops new queries from starting but lets running ones finish — usually the better behavior for all but the most runaway situations.
Resource monitors pair with QAS in an important way: QAS credits are charged against the warehouse's resource monitor. If you've set a tight credit quota and QAS is aggressively scaling up, you could hit your quota sooner than expected. Monitor QUERY_ACCELERATION_HISTORY credits alongside warehouse credits in your budget tracking.
-- Combined credit consumption: warehouse + QAS
SELECT
DATE_TRUNC('week', usage_date) AS week,
'warehouse' AS credit_type,
warehouse_name,
SUM(credits_used_compute) AS credits
FROM SNOWFLAKE.ACCOUNT_USAGE.WAREHOUSE_METERING_HISTORY
WHERE usage_date >= DATEADD(month, -3, CURRENT_DATE())
GROUP BY 1, 2, 3
UNION ALL
SELECT
DATE_TRUNC('week', start_time::DATE) AS week,
'qas' AS credit_type,
warehouse_name,
SUM(credits_used) AS credits
FROM SNOWFLAKE.ACCOUNT_USAGE.QUERY_ACCELERATION_HISTORY
WHERE start_time >= DATEADD(month, -3, CURRENT_TIMESTAMP())
GROUP BY 1, 2, 3
ORDER BY 1 DESC, 2, 3;
Here's a subtle behavior that trips up many teams: QAS will not accelerate a query if the warehouse is at low utilization. Snowflake's eligibility heuristics include an assessment of warehouse load — the serverless workers are intended to supplement an overloaded warehouse, not replace it. If your warehouse is at 20% utilization when a large query arrives, Snowflake may decide that the warehouse has spare capacity and not invoke QAS.
This means QAS is most effective during periods of high concurrency and large query volume combined. If you're seeing eligible queries (per SYSTEM$ESTIMATE_QUERY_ACCELERATION) that aren't actually being accelerated (per QUERY_ACCELERATION_HISTORY), low warehouse utilization at query time is often the explanation.
The implication: if you have a dedicated ETL warehouse that runs three big dbt models sequentially with no concurrency, QAS may rarely fire even on those models individually. Consider whether parallel dbt threads (increasing threads in your profile) help here — more parallelism on the warehouse increases utilization and makes QAS more likely to engage.
A subtle interaction between AUTO_SUSPEND and multi-cluster behavior: when a cluster in a multi-cluster warehouse becomes idle, it's suspended according to the AUTO_SUSPEND timer. But if the whole warehouse suspends (all clusters idle), the next query that comes in must wait for the entire warehouse to resume before any cluster can process it.
For interactive analytics workloads, a very short AUTO_SUSPEND (60 seconds) minimizes idle credit waste but means analysts who return from a coffee break face a resume latency. For reporting warehouses serving dashboards that auto-refresh every 5 minutes, setting AUTO_SUSPEND = 300 (5 minutes) means the warehouse never actually goes cold during business hours, trading some idle credits for zero resume latency.
The math here matters. An M warehouse costs 4 credits/hour. Five minutes of idle time per cycle = 0.33 credits. If your auto-refresh cycle is 5 minutes and analysts are active for 8 hours, you're paying for a continuously warm warehouse essentially anyway — so the 300-second suspend doesn't waste much compared to the alternative of cold-start penalties on every dashboard load.
-- Analyze resume wait times to calibrate AUTO_SUSPEND
SELECT
warehouse_name,
DATE_TRUNC('hour', start_time) AS hour_bucket,
COUNT(*) AS query_count,
AVG(compilation_time) AS avg_compile_ms,
AVG(queued_provisioning_time) AS avg_provisioning_wait_ms,
MAX(queued_provisioning_time) AS max_provisioning_wait_ms
FROM SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY
WHERE start_time >= DATEADD(day, -14, CURRENT_TIMESTAMP())
AND warehouse_name IN ('INTERACTIVE_ANALYTICS_WH', 'REPORTING_WH')
AND execution_status = 'SUCCESS'
GROUP BY 1, 2
ORDER BY 1, 2;
queued_provisioning_time is the query's wait time for the warehouse to resume. If this is regularly high for your interactive warehouse, your AUTO_SUSPEND is too aggressive.
QAS excels at reducing scan cost, but the number of micro-partitions Snowflake has to evaluate is determined by your data's clustering. QAS and automatic clustering (or manual cluster keys) are complementary: good clustering reduces the number of partitions eligible for scanning, and QAS accelerates the scan of those partitions.
For your largest, most frequently queried fact tables — think fct_orders or fct_web_events — clustering on the most common filter key (usually a date column) dramatically reduces how much data QAS has to process even when it does kick in. This interaction is why profiling and optimizing slow dbt models often yields improvements that make QAS faster and cheaper simultaneously.
Tip
When you enable Automatic Clustering on a table in Snowflake, those clustering credits are separate from both warehouse and QAS credits. They appear in AUTOMATIC_CLUSTERING_HISTORY. Factor this into your total compute cost model when designing which tables to cluster.
QAS will not help with:
If your slow queries are single-row lookups or tiny aggregations over well-clustered data that happen to be slow, the problem is likely compilation overhead, network, or client-side rendering. No amount of compute scaling will fix a client library that takes 8 seconds to render a 50,000 row result set.
In this exercise, you'll build a complete adaptive scaling setup for a realistic analytics workload and validate it's behaving as expected. Work in a development environment with appropriate permissions.
Run the query profiling analysis from earlier sections against your own ACCOUNT_USAGE. Generate a report that answers:
-- Comprehensive workload profile
WITH query_stats AS (
SELECT
warehouse_name,
DATE_TRUNC('hour', start_time) AS hour_bucket,
COUNT(*) AS query_count,
COUNT(CASE WHEN total_elapsed_time > 60000 THEN 1 END) AS slow_queries,
SUM(queued_overload_time) AS total_queue_ms,
SUM(total_elapsed_time) AS total_elapsed_ms,
AVG(bytes_scanned) / POWER(1024, 3) AS avg_gb_scanned,
PERCENTILE_CONT(0.95) WITHIN GROUP (ORDER BY bytes_scanned) / POWER(1024, 3) AS p95_gb_scanned
FROM SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY
WHERE start_time >= DATEADD(day, -14, CURRENT_TIMESTAMP())
AND execution_status = 'SUCCESS'
AND warehouse_name IS NOT NULL
GROUP BY 1, 2
)
SELECT
warehouse_name,
SUM(query_count) AS total_queries,
SUM(slow_queries) AS total_slow_queries,
ROUND(100.0 * SUM(slow_queries) / NULLIF(SUM(query_count), 0), 1) AS pct_slow,
ROUND(100.0 * SUM(total_queue_ms) / NULLIF(SUM(total_elapsed_ms), 0), 1) AS pct_time_in_queue,
ROUND(AVG(p95_gb_scanned), 2) AS avg_p95_scan_gb,
MAX(query_count) AS peak_hourly_queries
FROM query_stats
GROUP BY 1
ORDER BY total_queries DESC;
For your highest-volume warehouse, run the QAS eligibility check:
-- Sample 200 queries from the last 7 days for QAS eligibility check
-- (SYSTEM$ESTIMATE_QUERY_ACCELERATION is expensive to run at scale)
SELECT
q.query_id,
q.total_elapsed_time / 1000 AS elapsed_sec,
q.bytes_scanned / POWER(1024, 3) AS gb_scanned,
q.warehouse_name,
TRY_PARSE_JSON(SYSTEM$ESTIMATE_QUERY_ACCELERATION(q.query_id)):eligible::BOOLEAN AS is_eligible,
TRY_PARSE_JSON(SYSTEM$ESTIMATE_QUERY_ACCELERATION(q.query_id)):upperLimitScaleFactor::INT AS scale_factor,
TRY_PARSE_JSON(SYSTEM$ESTIMATE_QUERY_ACCELERATION(q.query_id)):estimatedQueryTimes.originalQueryTime::FLOAT AS original_ms,
TRY_PARSE_JSON(SYSTEM$ESTIMATE_QUERY_ACCELERATION(q.query_id)):estimatedQueryTimes.acceleratedQueryTime::FLOAT AS accelerated_ms
FROM (
SELECT *
FROM SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY
WHERE start_time >= DATEADD(day, -7, CURRENT_TIMESTAMP())
AND total_elapsed_time > 20000
AND bytes_scanned > 500000000
AND execution_status = 'SUCCESS'
ORDER BY bytes_scanned DESC
LIMIT 200
) q;
Document: how many queries are eligible, what's the average estimated speedup, and what scale factor is recommended.
Enable QAS on your test warehouse with a conservative scale factor:
ALTER WAREHOUSE your_test_warehouse
SET ENABLE_QUERY_ACCELERATION = TRUE
QUERY_ACCELERATION_MAX_SCALE_FACTOR = 4;
Re-run a selection of your previously-identified slow queries. Compare actual elapsed time against the QUERY_ACCELERATION_HISTORY to confirm acceleration fired, and calculate actual vs. estimated improvement.
Create a resource monitor scoped to your test warehouse:
CREATE RESOURCE MONITOR exercise_monitor
WITH CREDIT_QUOTA = 50
FREQUENCY = MONTHLY
START_TIMESTAMP = IMMEDIATELY
TRIGGERS
ON 75 PERCENT DO NOTIFY
ON 95 PERCENT DO NOTIFY
ON 100 PERCENT DO SUSPEND;
ALTER WAREHOUSE your_test_warehouse
SET RESOURCE_MONITOR = exercise_monitor;
Verify the monitor appears in Snowsight under Admin → Resource Monitors.
Write up your findings:
QAS is proportional to warehouse size. On an XSMALL warehouse, the absolute compute amplification is minimal — there's not much to multiply. The clearest wins from QAS are on MEDIUM and larger warehouses where the base scan parallelism is already meaningful and the serverless nodes can add materially more. If you enable QAS on an XSMALL and see no improvement, the first step is upsizing the warehouse, not increasing the scale factor.
A MAX_CLUSTER_COUNT of 10 on a LARGE warehouse is 10 × LARGE credits per hour when all clusters are running. Without a resource monitor, a burst event (say, 200 Tableau users hitting refresh simultaneously during an earnings call) could consume a month's budget in an afternoon. Always pair multi-cluster with resource monitors.
If your dbt pipeline runs at midnight and fires 50 models in parallel, STANDARD scaling will immediately spin up multiple clusters. But dbt's parallelism is bounded by your threads setting — you don't have 50 genuinely concurrent sessions in the same way 50 analysts do. The result is cluster spinup overhead and costs with minimal benefit. ECONOMY scaling, or simply increasing threads on a single-cluster XL, is usually better for batch transform workloads.
queued_overload_time in QUERY_HISTORY is concurrency queue wait — the time a query waited because all query slots were occupied. QAS doesn't address this. If this metric is high, you have a concurrency problem and need more clusters, not QAS. Routing these queries to QAS instead will not reduce queue time.
Teams frequently enable QAS and never check whether it's actually firing. If QUERY_ACCELERATION_HISTORY returns zero rows for your warehouse after a week, QAS may not be eligible for your query patterns, your warehouse may be too underloaded to trigger it, or there may be a configuration error. This view is your ground truth — check it.
-- Quick check: Is QAS actually running on your warehouse?
SELECT COUNT(*) AS qas_events, SUM(credits_used) AS qas_credits
FROM SNOWFLAKE.ACCOUNT_USAGE.QUERY_ACCELERATION_HISTORY
WHERE warehouse_name = 'YOUR_WAREHOUSE_NAME'
AND start_time >= DATEADD(day, -7, CURRENT_TIMESTAMP());
This one is subtle. QAS credits are billed separately from warehouse credits. A resource monitor set to trigger at 100 warehouse credits will not account for the QAS credits being consumed. Your actual bill can exceed what the resource monitor suggests. Build QAS credit expectations into your budget separately and monitor both views.
Warning
This billing separation means you can hit a resource monitor limit on your warehouse (which suspends it), but QAS credits from already-running queries can continue to accrue until those queries complete. The warehouse suspension stops new queries but doesn't immediately terminate QAS workers on in-flight queries.
If your multi-cluster warehouse feels slow to respond to load spikes (analysts experience queue wait even with STANDARD scaling), check:
Is the warehouse warm? A suspended warehouse must fully resume before the first cluster is active. The provisioning time for your warehouse size (usually 10-30 seconds for a LARGE) adds to perceived queue wait on cold start.
Is your MIN_CLUSTER_COUNT too low? Setting MIN_CLUSTER_COUNT = 1 means only one cluster is guaranteed available. If your morning burst is consistent, consider bumping to 2 during business hours.
Is your MAX_CLUSTER_COUNT adequate? At peak, check WAREHOUSE_LOAD_HISTORY to see whether your warehouse was regularly at or above max cluster count:
SELECT
start_time,
warehouse_name,
avg_running,
avg_queued_load,
avg_blocked,
avg_server_count
FROM SNOWFLAKE.ACCOUNT_USAGE.WAREHOUSE_LOAD_HISTORY
WHERE warehouse_name = 'INTERACTIVE_ANALYTICS_WH'
AND start_time >= DATEADD(day, -7, CURRENT_TIMESTAMP())
ORDER BY avg_queued_load DESC
LIMIT 50;
If avg_server_count is consistently equal to MAX_CLUSTER_COUNT and avg_queued_load is non-zero, you've hit the ceiling and need to increase MAX or upsize the base warehouse.
You now have a complete framework for thinking about and implementing dynamic compute scaling in Snowflake. Let's consolidate the key ideas.
Query Acceleration Service is your tool for query complexity — it offloads large scan-intensive workloads to elastic serverless compute that augments your warehouse rather than replacing it. The scale factor is the critical dial, and SYSTEM$ESTIMATE_QUERY_ACCELERATION gives you a data-driven basis for setting it. Monitor QUERY_ACCELERATION_HISTORY to confirm it's actually firing.
Multi-cluster warehouses with scaling policies are your tool for query concurrency — they add parallel clusters to handle simultaneous workloads. STANDARD policy optimizes for performance; ECONOMY optimizes for cost. Pick based on latency tolerance. WAREHOUSE_LOAD_HISTORY tells you whether your max cluster count is sufficient.
Workload segmentation multiplies the effectiveness of both tools by ensuring each warehouse has a homogeneous workload to respond to. Interactive analytics, batch transformation, dashboard serving, and data loading have fundamentally different characteristics and deserve different configurations.
Resource monitors are the safety net that prevents billing surprises when aggressive scaling meets unpredictable demand. Always pair dynamic scaling with resource monitors, especially when QAS is enabled.
The best analytics infrastructure is invisible to its users. When QAS and multi-cluster scaling are tuned correctly, analysts fire their most complex Monday morning queries and get results in seconds, not minutes, without you having to manually resize anything. The system adapts while you sleep.
Where to go from here:
Compute management is never a one-time configuration. As your data volumes grow, your team expands, and your query patterns evolve, revisit these configurations quarterly. The teams that treat warehouse tuning as an ongoing practice — not a one-time setup — consistently spend less and deliver faster.
Modern Data Stack