Skip to main content

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_SUSPEND time

    • 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:

  • MONITOR privilege 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.