One warehouse, two workloads: spilling ELT and a 08:30 dashboard spike
ETL_WH is an XS warehouse running a 14-task nightly pipeline that starts at 01:00 and finishes around 01:55. Three of those tasks account for 40 minutes of the runtime and spill 30–60 GB per run; the largest is a window-function join that repartitions an 800M-row staging table. From 06:00 the same warehouse serves about 40 concurrent BI dashboards; at 08:30 queue times reach 3–8 minutes and users escalate. The month-end resource monitor, set to suspend at 100%, has fired twice. The team's proposal is to resize ETL_WH to 4XL and be done with it. Query history shows 92% of BI queries complete in under 4 seconds once they start.
1. Evaluate the 4XL proposal against the evidence in the setup.
Read the worked answer
It solves one of the two problems and overspends on the other.
For the ELT side it is directionally right. Spilling to remote storage means the working set exceeded local disk; a larger warehouse gives more local SSD and memory per node, and scale-up is the correct axis for a single huge join. But 4XL is a guess: an XS to 4XL jump is 16× the credit rate, and the three spilling tasks may become memory-resident well before that. Resize in steps, measure bytes spilled per query, and stop where spilling is local-only or gone.
For the BI side it is the wrong axis. 92% of queries finish in under 4 seconds once they start; the pain is queue time, which is a concurrency problem, not a per-query compute problem. A bigger single cluster does help queuing somewhat, but it is the expensive way to buy it and it couples BI cost to ETL size.
Also note the resize discards the warehouse cache, so the first BI queries after each resize pay full compilation and remote-read costs.
2. Design the target warehouse topology and settings, and say what you would still not be able to fix.
Read the worked answer
Separate the workloads. An ETL warehouse sized to eliminate the join's spilling, and a BI warehouse sized for the observed query profile. Separation protects each from the other's cache churn and lets cost be attributed.
BI concurrency: start single-cluster and add multi-cluster with a sensible MAX_CLUSTER_COUNT if 08:30 still queues. Choose the scaling policy deliberately: STANDARD favours keeping queries fast by starting clusters early; ECONOMY favours cost by waiting. With a hard 08:30 spike and 3–8 minute queues, STANDARD is the defensible default, with auto-suspend kept short so the clusters do not idle through the day.
Cost guardrails: warehouse-level resource monitors with notify and suspend thresholds per warehouse (a shared 100%-of-account monitor is why month-end fired with no attribution), plus auto-suspend tuned to query arrival patterns.
What this does not fix: if the spilling join is caused by a skewed key or a missing filter, a big warehouse just spills expensively. Check bytes spilled and partition pruning on that one query before committing to 4XL, and consider the Query Acceleration Service for the outlier scan pattern rather than sizing the whole warehouse for it.