When analysts complain about slowness at 10am, the nightly ELT overruns, and ML jobs "need an XL just in case," the problem is usually planning, not performance. Snowflake warehouses are easy to mismatch to their workloads, and the bill reflects it - typically 20% of queries consume 80% of credits. Right-sizing, tuning auto-suspend, and standardizing job shapes for batch, interactive, and ML workloads, with guardrails so cost can't creep, fixes most of it.

Core concepts

Sizing - scale-up vs. scale-out. Scaling up (M → L → XL) helps a single heavy query with large joins or wide aggregations. Scaling out (multi-cluster) helps many simultaneous users and queries; use SCALING_POLICY = ECONOMY for a thriftier spin-up. Bigger isn't automatically faster - scale-out beats scale-up for concurrency, and the reverse holds for one heavy query.

Auto-suspend and auto-resume. Billing is per second with a minimum window, so short suspends save money - but set a threshold that avoids thrash and cold-cache penalties. Sensible defaults: interactive BI 60-120s, ELT batch windows 300-600s (pipelines have small gaps between tasks), ad-hoc sandboxes 30-60s.

Concurrency knobs. Multi-cluster MIN/MAX sets the elasticity band for bursts; SCALING_POLICY = ECONOMY is a cheaper, slower ramp and a good default; statement timeouts kill zombie jobs. You'll control cost far more with a few clearly-defined warehouse shapes - Interactive, Batch, ML - than with a bespoke warehouse per team.

Interactive / BI (spiky human traffic)

Goal: snappy dashboards at peak, minimal cost off-peak.

CREATE OR REPLACE WAREHOUSE WH_BI_MEDIUM
  WAREHOUSE_SIZE = 'M'
  AUTO_SUSPEND = 90
  AUTO_RESUME = TRUE
  MIN_CLUSTER_COUNT = 1
  MAX_CLUSTER_COUNT = 3
  SCALING_POLICY = ECONOMY
  STATEMENT_TIMEOUT_IN_SECONDS = 300;

An M is enough for typical BI queries, scaling out to three clusters for the morning spike. A 90-second suspend avoids constant spin cycles during "click, think, click" sessions. Restrict it to BI roles (block ELT), and require a QUERY_TAG like BI_DASHBOARD:{product} to trace runaway queries.

Batch ELT / backfills (predictable windows)

Goal: finish on time, then shut down - no drift.

CREATE OR REPLACE WAREHOUSE WH_ELT_LARGE
  WAREHOUSE_SIZE = 'L'
  AUTO_SUSPEND = 300
  AUTO_RESUME = TRUE
  MIN_CLUSTER_COUNT = 1
  MAX_CLUSTER_COUNT = 2
  SCALING_POLICY = ECONOMY
  STATEMENT_TIMEOUT_IN_SECONDS = 7200;

Scale up to L for heavy joins and sorts; two clusters cover overlapping tasks; the 300-second suspend avoids thrash between task chains. Orchestrate dependent loads with tasks:

CREATE OR REPLACE TASK T_LOAD_DIM
  WAREHOUSE = WH_ELT_LARGE
  SCHEDULE = 'USING CRON 15 1 * * * America/New_York'
AS MERGE INTO DIM_CUSTOMER ...;

CREATE OR REPLACE TASK T_LOAD_FACT
  WAREHOUSE = WH_ELT_LARGE
  AFTER T_LOAD_DIM
AS INSERT INTO FACT_ORDERS ...;

ALTER TASK T_LOAD_DIM RESUME;
ALTER TASK T_LOAD_FACT RESUME;

Attach a resource monitor that suspends the warehouse when a credit budget is hit, and keep hard statement timeouts (2h here) to kill runaway backfills.

ML / Snowpark (memory-hungry, bursty)

Goal: enough memory and cores for feature builds and training bursts, isolated from BI.

CREATE OR REPLACE WAREHOUSE WH_ML_XL
  WAREHOUSE_SIZE = 'XL'
  AUTO_SUSPEND = 120
  AUTO_RESUME = TRUE
  MIN_CLUSTER_COUNT = 1
  MAX_CLUSTER_COUNT = 1
  STATEMENT_TIMEOUT_IN_SECONDS = 14400;

ML usually wants scale-up (wide shuffles, big frames); keep it to a single cluster to avoid surprise costs, with a short suspend to switch the big engine off when idle. Set the tag and timeout from Snowpark:

import snowflake.connector
ctx = snowflake.connector.connect(...
    session_parameters={
        "QUERY_TAG": "ML_PIPELINE:feature_build",
        "STATEMENT_TIMEOUT_IN_SECONDS": 14400
    })

Give ML a dedicated warehouse - never shared with BI - and enforce a max runtime and per-project monitors.

The three shapes at a glance

Pattern	Size	Clusters	Auto-suspend	Timeout	Scaling	Notes
Interactive	M	1-3	60-120s	300s	ECONOMY	Spiky traffic; pay only when humans click
ELT / batch	L	1-2	300-600s	2h	ECONOMY	Predictable windows; avoid thrash
ML / Snowpark	XL	1	120s	4h	STANDARD	Memory/CPU hungry; isolate costs

Adjust sizes one notch up or down after watching queue time, spills, and job SLAs.

Guardrails to enforce

Resource monitors give budgets teeth - warn early, suspend at the cap:

CREATE OR REPLACE RESOURCE MONITOR RM_BI_MONTHLY
  CREDIT_QUOTA = 1000
  FREQUENCY = MONTHLY
  TRIGGERS = ON 80 PERCENT DO NOTIFY, ON 100 PERCENT DO SUSPEND;
ALTER WAREHOUSE WH_BI_MEDIUM SET RESOURCE_MONITOR = RM_BI_MONTHLY;

Query tagging makes cost attributable - make it policy that every job sets a QUERY_TAG of {domain}:{job}:{version}:

ALTER SESSION SET QUERY_TAG = 'ELT_PIPELINE:orders_daily:v3';

Timeouts and queue control: set STATEMENT_TIMEOUT_IN_SECONDS at the warehouse or session level, and STATEMENT_QUEUED_TIMEOUT_IN_SECONDS to fail fast rather than wait forever. Access boundaries: one warehouse per persona (BI, ELT, ML), least-privilege roles, no cross-persona usage. Observability: track daily credits by warehouse, tag, and role; watch QUEUED_OVERLOAD_TIME and COMPILATION_TIME; and watch spill metrics (large disk or remote spills mean an under-sized warehouse or poor SQL). The data lives in ACCOUNT_USAGE.WAREHOUSE_METERING_HISTORY and QUERY_HISTORY.

A backfill without bill shock

A one-off 90-day orders backfill that locks the BI warehouse every afternoon has a clean fix: create a dedicated WH_ELT_BACKFILL at L with AUTO_SUSPEND=300 and a single cluster; enforce a monitor with a 200-credit daily cap that suspends at 100%; run it tagged BACKFILL:orders_v2 with a 7200-second timeout; schedule it off-peak (2-5am ET); and drop the warehouse when it's done. BI stays unaffected, cost is capped, and the tag trail is auditable.

Best practices and pitfalls

Start small and scale one notch at a time based on queue time (over 30s?) and spill indicators. Prefer scale-out for concurrency and scale-up only for proven single-query heaviness. Keep auto-suspend short - a 1-3 second cold start goes unnoticed. Separate warehouses by persona and SLA (gold BI vs. exploratory), and bake the query tag, timeouts, and monitors into your orchestration templates (Airflow, Dagster). The recurring mistakes: XL warehouses left idling "just in case"; auto-suspend set too short on ELT chains, causing thrash between sequential tasks; sharing BI and ELT so one starves the other; ignoring spills (you pay more and stay slow); and running with no credit budget, so finance finds the overspend before you do. Review configs monthly and decommission one-off warehouses.