Auto-suspend and auto-resume in Snowflake - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ service_behavior

intermediate

What happens when a Snowflake warehouse with auto-suspend enabled is idle?

A Snowflake warehouse has auto-suspend set to 5 minutes. What will happen if no queries run for 6 minutes?

A The warehouse suspends immediately when the last query finishes.

B The warehouse automatically suspends after 5 minutes of inactivity.

C The warehouse continues running indefinitely until manually suspended.

D The warehouse suspends only if manually triggered by the user.

💡 Hint

Think about what auto-suspend means for idle warehouses.

Auto-suspend automatically stops the warehouse after the specified idle time to save credits.

❓ Configuration

intermediate

Which SQL command enables auto-resume for a Snowflake warehouse?

Choose the correct SQL command to enable auto-resume on a warehouse named MY_WH.

A ALTER WAREHOUSE MY_WH SET AUTO_RESUME = TRUE;

B ALTER WAREHOUSE MY_WH SET AUTO_RESUME = ON;

C ALTER WAREHOUSE MY_WH SET AUTO_RESUME = ENABLED;

D ALTER WAREHOUSE MY_WH SET AUTO_RESUME = 1;

💡 Hint

Check the exact syntax for setting boolean options in Snowflake.

Snowflake uses TRUE or FALSE for boolean settings in ALTER WAREHOUSE commands.

❓ Architecture

advanced

How does auto-resume affect query latency in Snowflake?

Consider a warehouse with auto-suspend enabled after 10 minutes and auto-resume enabled. What is the impact on query latency when a new query arrives after suspension?

A The first query experiences a delay while the warehouse resumes, increasing latency.

B Queries run instantly with no delay because auto-resume is automatic.

C All queries are queued until the warehouse is manually resumed.

D Queries fail if the warehouse is suspended, regardless of auto-resume.

💡 Hint

Think about what happens when a warehouse is suspended and a query arrives.

Auto-resume starts the warehouse when a query arrives, but this startup takes a few seconds, causing initial delay.

❓ security

advanced

What security risk can arise if auto-resume is enabled on a Snowflake warehouse?

Identify a potential security concern related to enabling auto-resume on a warehouse.

A Auto-resume encrypts data less securely during warehouse startup.

B Auto-resume disables user authentication, allowing anonymous access.

C Auto-resume causes warehouses to run with elevated privileges automatically.

D Unauthorized queries can trigger warehouse startup, increasing exposure to data.

💡 Hint

Consider who can run queries and what happens when the warehouse starts automatically.

If unauthorized users can run queries, auto-resume may start the warehouse unexpectedly, potentially increasing data exposure.

✅ Best Practice

expert

Which combination of auto-suspend and auto-resume settings optimizes cost and performance for unpredictable workloads?

You manage a Snowflake warehouse with unpredictable query patterns. Which setting combination best balances cost savings and query responsiveness?

A Auto-suspend set to 60 minutes; auto-resume disabled.

B Auto-suspend disabled; auto-resume disabled.

C Auto-suspend set to 5 minutes; auto-resume enabled.

D Auto-suspend disabled; auto-resume enabled.

💡 Hint

Think about how quickly the warehouse suspends and restarts for cost and speed.

Short auto-suspend with auto-resume saves cost by suspending quickly and resumes automatically to reduce query wait time.