Virtual Warehouses: Sizing, Scaling, and Cost

A virtual warehouse is a cluster of compute you rent by the second. Two independent decisions govern it — how big each cluster is, and how many clusters there are — and mixing them up is the most common way to spend money without getting faster.

Sizes

Each step doubles the compute and doubles the credit rate:

alter warehouse bookshop_wh set warehouse_size = large;

+----------------------------------+
| status                           |
|----------------------------------|
| Statement executed successfully. |
+----------------------------------+
1 Row(s) produced. Time Elapsed: 0.204s

Resizing takes effect on the next query and needs no restart. Running queries finish on the old size.

Bigger is often the same price

The instinct that a larger warehouse costs more is only half right. Take a scan-heavy aggregation over a large table:

select
    date_trunc('month', ordered_at) as month,
    country_code,
    sum(amount) as revenue
from orders_large            -- 4.2 billion rows
join customers using (customer_id)
group by 1, 2;

-- warehouse_size = SMALL
+---------+--------------+------------+
| MONTH   | COUNTRY_CODE | REVENUE    |
|---------+--------------+------------|
| ...     | ...          | ...        |
+---------+--------------+------------+
36 Row(s) produced. Time Elapsed: 244.108s

-- warehouse_size = LARGE
36 Row(s) produced. Time Elapsed: 62.755s

Small is 2 credits/hour for 244 seconds → 0.136 credits. Large is 8 credits/hour for 63 seconds → 0.140 credits. Nearly identical cost, and the answer arrived four minutes sooner.

That near-linear scaling holds only while the query can use the extra nodes. Check by looking at whether it spilled:

select
    query_text,
    execution_time / 1000 as seconds,
    bytes_spilled_to_local_storage,
    bytes_spilled_to_remote_storage
from table(information_schema.query_history())
order by start_time desc limit 2;

+------------------------+---------+--------------------------------+---------------------------------+
| QUERY_TEXT             | SECONDS | BYTES_SPILLED_TO_LOCAL_STORAGE | BYTES_SPILLED_TO_REMOTE_STORAGE |
|------------------------+---------+--------------------------------+---------------------------------|
| select date_trunc('... |  62.755 |                              0 |                               0 |
| select date_trunc('... | 244.108 |                    18253611008 |                      2147483648 |
+------------------------+---------+--------------------------------+---------------------------------+

The small warehouse spilled 18 GB to local disk and 2 GB to remote storage. Remote spilling is the single worst thing that can happen to a query’s runtime — it means the working set did not fit in memory and Snowflake fell back to object storage. Any non-zero value in that last column is a signal to size up.

A query that returns one row from a lookup, though, gains nothing:

-- X-Small:  Time Elapsed: 0.412s
-- 2X-Large: Time Elapsed: 0.388s

64× the credit rate for 6% less time. Size to the workload, not to the importance of the dashboard.

Scaling out for concurrency

A bigger warehouse does nothing for twenty analysts running twenty small queries — they queue. That needs more clusters:

alter warehouse reporting_wh set
    warehouse_size = small
    min_cluster_count = 1
    max_cluster_count = 4
    scaling_policy = 'STANDARD';

+----------------------------------+
| status                           |
|----------------------------------|
| Statement executed successfully. |
+----------------------------------+

Snowflake starts extra clusters when queries begin queueing and shuts them down when demand falls. Watch it happen:

select
    warehouse_name,
    avg(avg_running)      as avg_running,
    avg(avg_queued_load)  as avg_queued
from table(information_schema.warehouse_load_history(
    date_range_start => dateadd('hour', -2, current_timestamp())))
group by 1;

+----------------+-------------+------------+
| WAREHOUSE_NAME | AVG_RUNNING | AVG_QUEUED |
|----------------+-------------+------------|
| REPORTING_WH   |        6.42 |       0.03 |
| BOOKSHOP_WH    |        1.10 |       2.87 |
+----------------+-------------+------------+
2 Row(s) produced. Time Elapsed: 0.688s

Read that as a diagnosis. REPORTING_WH runs 6.4 queries concurrently with almost nothing queued — healthy. BOOKSHOP_WH runs 1.1 and queues 2.9 — it needs more clusters, not a bigger size.

scaling_policy = 'ECONOMY' waits longer before starting a cluster, trading some queueing for fewer credits. Use STANDARD for anything a person is waiting on.

Auto-suspend and the cache trade-off

alter warehouse bookshop_wh set auto_suspend = 60;

Suspending stops the bill, and it also throws away the warehouse’s local disk cache — the copy of recently read micro-partitions held on the cluster’s SSDs. The next query re-reads from object storage:

-- warm cache
Time Elapsed: 1.204s
-- after suspend/resume
Time Elapsed: 8.771s

So the setting is a real trade-off, not a free win:

Never set auto_suspend = 0 or never on a warehouse anyone can forget about. An X-Small left running is 24 credits a day; a 4X-Large is 6,144.

Guarding the bill

create resource monitor bookshop_monthly with
    credit_quota = 500
    frequency = monthly
    start_timestamp = immediately
    triggers
        on 75 percent do notify
        on 90 percent do notify
        on 100 percent do suspend
        on 110 percent do suspend_immediate;

alter warehouse bookshop_wh set resource_monitor = bookshop_monthly;

+-------------------------------------------------+
| status                                          |
|-------------------------------------------------|
| Resource monitor BOOKSHOP_MONTHLY successfully created. |
+-------------------------------------------------+
1 Row(s) produced. Time Elapsed: 0.318s

suspend lets running queries finish; suspend_immediate kills them. Having both, a few percent apart, gives you a grace window before anything is lost.

Add a statement timeout so one bad query cannot run for a day:

alter warehouse bookshop_wh set statement_timeout_in_seconds = 3600;

+----------------------------------+
| status                           |
|----------------------------------|
| Statement executed successfully. |
+----------------------------------+

Where the credits went

select
    warehouse_name,
    sum(credits_used)      as credits,
    round(sum(credits_used) * 3.00, 2) as approx_usd
from snowflake.account_usage.warehouse_metering_history
where start_time >= dateadd('day', -7, current_timestamp())
group by 1
order by credits desc;

+----------------+---------+------------+
| WAREHOUSE_NAME | CREDITS | APPROX_USD |
|----------------+---------+------------|
| ETL_WH         |  412.60 |    1237.80 |
| REPORTING_WH   |   88.14 |     264.42 |
| BOOKSHOP_WH    |   12.03 |      36.09 |
+----------------+---------+------------+
3 Row(s) produced. Time Elapsed: 1.204s

account_usage views lag by up to 45 minutes and keep a year of history; information_schema is current but only keeps 7-14 days. Use the first for billing reviews and the second for debugging what just happened.

Practice

-- X-Small: Time Elapsed: 488.204s  → 1 credit/hr × 488s  = 0.136 credits
-- Large:   Time Elapsed:  62.755s  → 8 credits/hr × 63s  = 0.140 credits

select query_id, execution_time/1000 as seconds, bytes_spilled_to_remote_storage
from table(information_schema.query_history())
where bytes_spilled_to_remote_storage > 0
order by bytes_spilled_to_remote_storage desc;

+--------------------------------------+---------+---------------------------------+
| QUERY_ID                             | SECONDS | BYTES_SPILLED_TO_REMOTE_STORAGE |
|--------------------------------------+---------+---------------------------------|
| 01b2c3d4-0000-a1b2-0000-c3d400001a2b | 244.108 |                      2147483648 |
+--------------------------------------+---------+---------------------------------+
1 Row(s) produced. Time Elapsed: 0.612s

+----------------+-------------+------------+
| WAREHOUSE_NAME | AVG_RUNNING | AVG_QUEUED |
|----------------+-------------+------------|
| BOOKSHOP_WH    |        4.88 |       0.11 |
+----------------+-------------+------------+

show resource monitors;

+-------------------+--------------+---------------+-----------+-----------+
| name              | credit_quota | used_credits  | remaining | frequency |
|-------------------+--------------+---------------+-----------+-----------|
| BOOKSHOP_MONTHLY  |          500 |         12.03 |    487.97 | MONTHLY   |
+-------------------+--------------+---------------+-----------+-----------+
1 Row(s) produced. Time Elapsed: 0.194s

Next: loading data — stages, COPY INTO, and getting files into tables.