Auto-Suspend & Auto-Resume in Snowflake
What are they?
-
✅ Auto-Suspend When a virtual warehouse in Snowflake is not in use (no queries running), it automatically pauses itself to save costs. Snowflake bills compute by the second—so suspending idle warehouses prevents unnecessary charges.
-
✅ Auto-Resume When a new query arrives for a suspended warehouse, Snowflake instantly resumes the warehouse. This ensures there’s no manual intervention to “start it back up.”
Why is it required?
✔️ Cost Optimization: Prevents paying for idle compute resources. ✔️ No Performance Loss: Warehouses resume instantly when needed—no query lag. ✔️ Operational Simplicity: Reduces manual tasks for warehouse start/stop.
How to configure it? (SQL Example)
You can set these properties during warehouse creation or modify them later.
-- Create a warehouse with auto-suspend (5 min) and auto-resume
CREATE WAREHOUSE my_wh
WAREHOUSE_SIZE = 'XSMALL'
AUTO_SUSPEND = 300 -- suspend after 300 seconds (5 minutes)
AUTO_RESUME = TRUE;
-- Alter an existing warehouse to enable auto-suspend and auto-resume
ALTER WAREHOUSE my_wh
SET AUTO_SUSPEND = 120 -- suspend after 2 minutes of inactivity
SET AUTO_RESUME = TRUE;
Use Case
Imagine a team of data analysts using Snowflake for ad-hoc queries throughout the day.
- ✅ Queries spike at 10 AM for daily reporting, then no queries until 3 PM.
- ✅ Without auto-suspend, the warehouse would stay running (costs $$$!).
- ✅ Auto-suspend ensures the warehouse pauses itself after the burst.
- ✅ Auto-resume ensures no one notices a delay when they query again at 3 PM.
Best Practices
-
✅ Set an appropriate
AUTO_SUSPENDtime- Shorter (30–60 seconds) for BI dashboards or sporadic queries.
- Longer (5–10 minutes) for ETL/ELT jobs with gaps between steps.
-
✅ Enable
AUTO_RESUME- Always enable it to avoid user frustration if queries start while the warehouse is suspended.
-
✅ Use separate warehouses for ETL vs. Ad-Hoc
- ETL jobs can have longer auto-suspend to reduce startup lag between pipeline steps.
-
✅ Monitor usage
- Use Query History and WAREHOUSE_METERING_HISTORY views to see warehouse activity.
Limitations
❌ Startup lag: Although resuming is fast, there can be a 1–3 second delay the first time a warehouse resumes. ❌ Doesn’t help long-running queries: If a warehouse is constantly busy, auto-suspend won’t kick in—so you’ll still pay full usage.
Advantages
- ✅ Saves money by only billing for active compute time.
- ✅ Zero manual effort to start/stop compute resources.
- ✅ Ensures performance for ad-hoc queries because warehouses resume instantly.
Disadvantages
❌ May disrupt ETL steps if suspend times are too aggressive—e.g., if a pipeline has multiple steps separated by short gaps, auto-suspend might pause between them and add tiny resume lags. ❌ Not real-time control—Snowflake decides precisely when to suspend based on no running queries.
Roles Required
🔑 Creating/Modifying Warehouse:
- ACCOUNTADMIN or SYSADMIN roles.
🔑 Querying warehouse activity:
MONITORprivilege on the warehouse for monitoring.
Impact on Performance Tuning
-
✅ Improves Cost-Performance Balance: Auto-suspend reduces idle credit spend—your budget goes to actual work, not wasted compute.
-
✅ No performance impact on queries: Snowflake instantly resumes warehouses, so user queries remain responsive.
-
✅ Warehouse sizing + suspend tuning: Pairing auto-suspend with right-sized warehouses ensures you’re not overpaying for oversized, always-on warehouses.
🚀 Summary
✔️ Auto-suspend = “Pause when idle.” ✔️ Auto-resume = “Start instantly on demand.” ✔️ Together, they reduce costs and maintain performance for interactive and sporadic workloads.