SnowPro Advanced Architect

By Snowflake · Question Mode

QuestionQ3

Performance, Cost, and Resource Optimization

An Architect is designing a Snowflake architecture that must execute Data Analyst reports very quickly. To optimize costs, the virtual warehouse is configured to auto-suspend after 2 minutes of idle time. The Architect runs the queries once in the morning after the data refresh, hoping that all subsequent queries will use the warehouse cache. However, later in the day, subsequent queries run slowly.

Why is this happening?

Explanation

A virtual warehouse’s local data cache is dropped when the warehouse is suspended. An auto-suspend setting of 2 minutes can therefore clear the cache before later reports run, requiring the warehouse to rebuild the cache and causing slower performance. Snowflake recommends at least 10 minutes of auto-suspend for BI and SELECT query warehouses when preserving cache is important.

Learn more

Community Discussion

No comments yet. Be the first to start the discussion!