Evaluating Adaptive Warehouses for ETL: Why We Reverted to Standard

DEV Community

docs.snowflake.com

For our Silver layer workload, the answer was no.

Background

WH_ETL_SILVER_01 is our dedicated warehouse for Silver layer data loads — long-running transformation queries at a steady, predictable rate of ~2,000 queries/day. On May 12, 2026 we migrated it from Standard to Adaptive to evaluate the new model under production conditions.

Parameter	Standard Config	Adaptive Config
Type	STANDARD	ADAPTIVE
Size	Small	Max: Small
Concurrency/Burst	MAX_CLUSTER_COUNT = 3	QUERY_THROUGHPUT_MULTIPLIER = 3
Auto-Suspend	60 seconds	Managed by Snowflake

After 16 days of production data, we reverted.

The Problem: Queuing That Wouldn't Stop Growing

Within the first week on adaptive, we started seeing queries queue. By week three, the queue rate had grown to 3.4% — and still climbing.

Period	Config	Queue Rate	Avg Wait (queued)	Max Wait
Apr 1 – May 11	Standard	~0%	N/A	—
May 12–15	Adaptive Week 1	0.1%	~10s	102s
May 16–22	Adaptive Week 2	2.5%	80–98s	490s
May 23–27	Adaptive Week 3	3.4%	65–80s	508s

Under Standard with the same ~2,000 queries/day load, queueing was effectively zero. Two queries queued on the worst day, for 100ms each.

Fair Comparison: Matched Load Windows

To avoid skewing results by different query volumes, we compared periods with equivalent daily throughput.

Standard baseline: May 1–11 (11 days, avg 2,151 queries/day)
Adaptive comparison: May 12–14, 18–22, 25–26 (10 days, avg 2,060 queries/day)

Query Performance

Metric	Standard	Adaptive	Change
Avg Elapsed Time	19.9s	22.8s	+15% slower
Median Elapsed Time	256ms	267ms	+4%
P95 Elapsed Time	14.1s	20.4s	+45% slower
Avg Execution Time	19.6s	21.0s	+7% slower

Cost

Metric	Standard	Adaptive	Change
Avg Daily Credits	15.2	19.1	+26% more expensive
Credits Per Query	0.0071	0.0093	+31% more expensive

Queuing

Metric	Standard	Adaptive	Change
Queue Rate	0.3%	2.2% (growing)	+7x worse
Avg Queue Wait	3.3ms	1,543ms	+46,000% worse

How We Measured

We used the same ACCOUNT_USAGE views from the previous post.

Query Performance (Matched Windows)

SELECT
 CASE
 WHEN start_time::DATE BETWEEN '2026-05-01' AND '2026-05-11' THEN 'Standard'
 ELSE 'Adaptive'
 END AS config,
 COUNT(*) AS total_queries,
 ROUND(AVG(total_elapsed_time) / 1000, 2) AS avg_elapsed_sec,
 ROUND(MEDIAN(total_elapsed_time) / 1000, 2) AS median_elapsed_sec,
 ROUND(PERCENTILE_CONT(0.95) WITHIN GROUP
 (ORDER BY total_elapsed_time) / 1000, 2) AS p95_elapsed_sec,
 ROUND(AVG(queued_overload_time) / 1000, 2) AS avg_queued_sec,
 ROUND(COUNT_IF(queued_overload_time > 0)
 / COUNT(*) * 100, 2) AS queue_rate_pct
FROM SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY
WHERE warehouse_name = 'WH_ETL_SILVER_01'
 AND start_time::DATE >= '2026-05-01'
GROUP BY config
ORDER BY config;

Credit Consumption

SELECT
 CASE
 WHEN start_time::DATE BETWEEN '2026-05-01' AND '2026-05-11' THEN 'Standard'
 ELSE 'Adaptive'
 END AS config,
 ROUND(SUM(credits_used_compute) / COUNT(DISTINCT start_time::DATE), 2) AS avg_daily_credits,
 ROUND(SUM(credits_used_compute), 2) AS total_credits
FROM SNOWFLAKE.ACCOUNT_USAGE.WAREHOUSE_METERING_HISTORY
WHERE warehouse_name = 'WH_ETL_SILVER_01'
 AND start_time::DATE >= '2026-05-01'
GROUP BY config
ORDER BY config;

Why Adaptive Doesn't Fit This Workload

Adaptive warehouses are designed for variable, unpredictable workloads — dashboards, ad-hoc analytics, mixed query sizes. Our Silver pipeline is the opposite:

Steady ~2,000 queries/day
Predictable concurrency (same pipeline, same schedule)
Long-running transformation queries — not short, bursty ones
Consistent data volumes day-to-day

The QUERY_THROUGHPUT_MULTIPLIER = 3 cap was insufficient for peak concurrency windows. The adaptive model's dynamic scaling overhead — which adds value when workloads are unpredictable — just introduced latency without benefit here. The Standard config's fixed multi-cluster model handled this workload profile cleanly.

Recommendation: Revert to Standard

ALTER WAREHOUSE WH_ETL_SILVER_01 SET
 WAREHOUSE_TYPE = 'STANDARD',
 WAREHOUSE_SIZE = 'SMALL',
 MAX_CLUSTER_COUNT = 3,
 SCALING_POLICY = 'STANDARD',
 AUTO_SUSPEND = 60;

Expected impact after reverting:

Eliminate queuing (proven 0% queue rate at this load level)
Save ~26% on daily credits
Reduce P95 latency by 45%
Restore predictable, consistent performance

If You Must Stay on Adaptive

Snowflake's official guidance is clear: increase QUERY_THROUGHPUT_MULTIPLIER to reduce queuing.

ALTER WAREHOUSE WH_ETL_SILVER_01 SET QUERY_THROUGHPUT_MULTIPLIER = 6;

This would likely resolve the queuing issue. However, for a predictable, steady-state ETL workload, this approach adds operational complexity without strategic benefit:

You're now tuning a multiplier instead of configuring a fixed cluster count
Higher throughput capacity may increase peak concurrent spend, requiring new cost monitoring
For a pipeline that runs the same way every day, the adaptive model's flexibility adds overhead without value

Bottom line: Adaptive's tuning knobs are powerful for variable workloads. For predictable ETL, Standard's simplicity wins operationally — and financially.

Key Takeaways

Adaptive is not universally better — it adds overhead that only pays off for variable, unpredictable workloads.
Always compare at matched load — raw averages across different query volumes are misleading.
Queuing is the canary — a worsening queue rate on adaptive signals a workload mismatch, not just an under-tuned multiplier.
Standard multi-cluster remains the better fit for batch/ETL pipelines with consistent concurrency.
Reserve adaptive for variable workloads — ad-hoc analytics or environments where query volume swings 5–10x daily.

Closing Thought

The lesson from post was: in some workloads, bigger can be cheaper. The lesson here is the inverse: newer is not always better. The right warehouse type depends on workload shape — steady pipelines and dynamic workloads have fundamentally different resource patterns, and Snowflake's warehouse models reflect that.

Disclosure and Scope

Results reflect our specific environment and workload profile; outcomes may vary.
Analysis period: May 1–27, 2026 | Warehouse: WH_ETL_SILVER_01 | Workload: Silver layer production loads

This analysis used Snowflake built-in ACCOUNT_USAGE views only. No third-party monitoring stack required.