Stop Guessing Snowflake Warehouse Sizes: A Free Advisor That Reads Your Own Usage History
Stop Guessing Snowflake Warehouse Sizes
A free advisor that reads your own usage history and tells you what to change
Almost every Snowflake team sizes a warehouse the same way: start at Medium, see if anything breaks, and never think about it again. Six months later nobody remembers why ANALYTICS_WH is a Large, whether it needs to be, or what it would cost to find out.
The information to answer that question is already sitting in your account. Snowflake records spill, queue time, bytes scanned, and concurrency for every query in ACCOUNT_USAGE. The problem is that turning those columns into a defensible sizing decision takes a fair amount of SQL and a mental model of how the pieces interact.
So I built SnowLens Warehouse Sizing Advisor -- a free Streamlit app that installs into your own Snowflake account, reads those signals, and produces a per-warehouse recommendation with the reasoning attached. It's the second app in SnowLens, alongside the existing 9-detector anomaly dashboard.
In this post
Why warehouse sizing is genuinely hard
Each step up the Snowflake size ladder doubles both compute and cost. X-Small is 1 credit/hour, Small is 2, Medium 4, Large 8, and so on up to 6X-Large at 512.
The intuitive assumption is that bigger means faster. It often doesn't. A query scanning 500 MB will not run meaningfully faster on a Large than on a Small, because it was never constrained by memory or parallelism in the first place. You'd simply pay four times as much for the same result.
Conversely, a query that is memory-constrained doesn't degrade gracefully. Once it exhausts the warehouse's memory it spills to local SSD, and once that fills, to remote storage -- at which point performance falls off a cliff. Here the larger warehouse can be genuinely cheaper in total, because the query finishes in a fraction of the time.
The six signals the advisor reads
Every recommendation is derived from six measurements per warehouse, over a window you choose (7, 30, or 90 days).
Remote spill rate
The share of queries writing to remote storage. This is the strongest undersizing signal that exists. Remote spill means the query exhausted memory, then exhausted local SSD, and fell back to blob storage -- orders of magnitude slower than RAM. If a meaningful share of your queries are doing this, the warehouse is too small, full stop.
Local spill rate
The share spilling to local SSD but not remote. A softer signal -- local spill is survivable -- but sustained local spill still costs wall-clock time on every affected query, and it means you're one workload increase away from remote spill.
p90 bytes scanned
The 90th-percentile scan per query. This drives the downsizing decision, and the choice of percentile over average matters enormously -- explained in its own section below.
Cumulative queue time
QUEUED_OVERLOAD_TIME summed per warehouse. This measures concurrency pressure -- queries waiting because the warehouse was already busy. Critically, this is a different problem from being undersized, and the advisor treats it separately.
Peak hourly concurrency
The busiest hour's query count, which determines how wide the cluster auto-scale range should be. A warehouse queuing at 30 queries/hour needs a different cluster ceiling than one queuing at 600.
Idle credit hours
Hours that consumed credits while running zero queries. This isn't a sizing signal at all -- it points straight at an AUTO_SUSPEND that's set too high, which is usually the cheapest fix available.
The decision logic, in full
No black box. Here is every rule, with its threshold:
| Action | Triggered when | Reasoning |
|---|---|---|
| Size up | Remote spill > 2% of queries or local spill > 15% |
Memory pressure is real and measurable. The larger warehouse often costs less in total because queries stop thrashing. |
| Size down | p90 scan < 100 MB and currently above Small and spill under 1% |
The workload is small relative to the warehouse. All three conditions must hold -- spill anywhere means don't touch it. |
| Add clusters | Cumulative queue time > 60s | Concurrency pressure. Evaluated independently of size, because these are unrelated problems. |
| Keep | None of the above | The evidence doesn't justify a change. Churn for its own sake costs more than it saves. |
Two guard rails sit on top of this. The ladder floors at Small for downsizing rather than X-Small, because dropping to the smallest size on the strength of a scan-volume signal alone is too aggressive. And at the top, a warehouse already at 4X-Large that's still spilling gets told the honest answer: more compute isn't the fix, reducing data scanned is.
Why p90 instead of the average
This is the design decision I'd most defend, so it's worth spelling out.
Consider a warehouse running 1,000 queries a day. 990 of them scan about 20 MB. Ten of them are monthly reconciliation jobs scanning 50 GB each.
Judged on the average, this warehouse looks like it handles half-gigabyte queries and any downsizing recommendation gets suppressed. Judged on p90, the truth is visible: 99% of the time this warehouse is doing very little work. It is a downsizing candidate, and the ten heavy jobs are better served by a separate warehouse sized for them.
The average is dragged around by exactly the outliers you don't want driving a steady-state sizing decision. The 90th percentile reflects what the warehouse routinely does.
Why queuing is not a sizing problem
This is the most common and most expensive sizing mistake I see.
Queries are queuing. Dashboards feel slow. The instinct is to bump the warehouse from Medium to Large. Costs double, and the queuing barely improves.
The reason is that these are two distinct constraints:
| Symptom | Actual constraint | Correct fix |
|---|---|---|
| Individual queries are slow, spilling to disk | Not enough memory per query | Scale up -- bigger size |
| Queries wait in a queue before starting | Not enough slots for concurrent work | Scale out -- more clusters |
A bigger warehouse gives each query more memory. It does very little for how many can run at once. What you want is MAX_CLUSTER_COUNT above 1, so Snowflake spins up additional clusters under load and suspends them when it passes -- you pay for the extra capacity only while it's actually in use.
The advisor evaluates these two axes separately and will happily tell you to size down while adding clusters, which is frequently the correct and cheaper answer.
What the output looks like
Each warehouse gets a row showing current size, recommended size, action, confidence, cluster range, and the underlying numbers. Confidence is driven by how many queries backed the recommendation -- a warehouse with 30 queries in the window is flagged as a weak signal rather than presented with false authority.
Every row carries a plain-English explanation. Not "size up," but why:
Then it generates the SQL:
It generates these statements. It does not run them.
What it deliberately does not do
Being clear about the boundaries matters more than overselling the tool.
It does not execute changes. Every recommendation is a suggestion with SQL attached. You read it, you decide, you run it.
It only sees the past. Recommendations are based on the window you select. A warehouse provisioned last week for a workload launching next month will look oversized, because by the evidence available, it is.
It doesn't know your SLAs. A warehouse might be technically oversized and still correctly sized, because a finance team needs a report in under 30 seconds at month end. The advisor can't see that constraint -- you can.
It's a starting point, not a verdict. Change one warehouse at a time, then re-measure. Sizing is empirical.
ACCOUNT_USAGE metadata, never your business tables. No outbound network calls. Runs on your own XSMALL warehouse with 60s auto-suspend. VizCanvaz receives nothing.
Installing it
If you already run SnowLens, this is a two-minute upgrade: upload sizing_advisor.py to the existing stage and re-run 02_create_app.sql, which creates both apps.
From scratch, it's the same three steps as SnowLens itself:
Both apps share one stage, one warehouse, and one role. Uninstall is still a single script that drops everything.
Try it on your own account
Free, no signup, installs in five minutes.
Download SnowLens View on GitHubIf you try it, I'd like to know whether the recommendations match what you'd have concluded yourself -- particularly where they don't. The thresholds are reasonable defaults drawn from patterns I've seen across enterprise accounts, but they're defaults, and your workload may well justify different ones.
Related: All About SnowLens - the 9 detection rules
Comments
Post a Comment