Snowflake Warehouse Sizing for ETL (Without Idle Compute)
Right-size Snowflake warehouses for batch pipelines: XS vs S vs M, auto-suspend, one warehouse per workload, and checks so retries do not scale you up by accident.
- snowflake
- warehouse
- sizing
- etl
Snowflake warehouse sizes look simple until the ETL bill arrives. Teams scale from XS to L because a DAG “felt slow,” leave warehouses running between hourly jobs, and then scale up again when a silent failure triggers retries.
Warehouse sizing is a pipeline contract problem: which job, which warehouse, which SLA, who owns the cost.
Start with workload, not a default Medium
| Workload | Typical size | Why |
|---|---|---|
| Incremental MERGE on a few million rows | XS–S | Most batch loads are latency-tolerant |
| Large COPY INTO / first backfill | M–L, then scale down | Burst, then return to S |
| dbt tests + freshness SQL | XS | Metadata queries should not share an L warehouse with transforms |
| Concurrent hourly marts | Separate S warehouses, not one L | Isolation beats a bigger shared pool |
If every pipeline uses TRANSFORM_WH_L, you cannot tell which DAG caused the spike. Put the warehouse name on the pipeline spec and in the catalog.
Auto-suspend is not optional
For scheduled ETL:
- Auto-suspend: 60 seconds for bursty jobs; 5 minutes only if the next task in the DAG is guaranteed within that window.
- Auto-resume: on.
- Minimum cluster count: 1 unless you have proven query queuing during the batch window.
Idle warehouses are the most common “we sized up” mistake. An S that suspends beats an M that sits warm for 50 minutes after orders_daily_sync finishes.
Do not scale up to hide stale data
A classic incident:
mart.executive_kpisis 38 hours stale.- Airflow still reports SUCCESS (the COPY finished; the source was empty).
- Someone resizes the warehouse and re-runs the DAG.
- Snowflake credits spike; the table is still stale.
Size does not fix a missing increment. Add a freshness check on the target and revoke the Pipeline Passport when it fails. Then debug the source — silent failures are cheaper than a larger warehouse.
One warehouse per SLA class
Group pipelines by when they must finish, not by team Slack channel:
ETL_BATCH_S— nightly finance loads, 2 a.m. windowETL_HOURLY_XS— product analytics micro-batchADHOC_XS— analysts; never the same warehouse as SLA pipelines
When a check fails, lineage should answer: warehouse, pipeline, downstream dashboards. That is what a Pipeline Passport is for.
Snowflake connector example (catalog)
In DataXPipe, register the connection once, then reference it from specs so checks run against the same warehouse the transform uses. See the Snowflake connector example for auth, role, and check SQL.
Practical next steps
- List warehouses used by production DAGs and kill any without auto-suspend.
- Move dbt tests off the transform warehouse.
- Attach freshness checks to the three marts leadership actually opens.
- Import the DAGs into DataXPipe so warehouse, owner, and checks sit on one catalog entry.