Warehouse sizes and scaling in Snowflake - Practice Problems & Coding Challenges

Challenge - 5 Problems

🧠 Conceptual

intermediate

Understanding Snowflake Warehouse Size Impact

Which of the following statements best describes the impact of increasing the size of a Snowflake warehouse?

A Increasing warehouse size decreases compute resources, which slows down query processing but reduces cost.

B Increasing warehouse size increases compute resources, which speeds up query processing but also increases cost proportionally.

C Increasing warehouse size has no effect on query speed but increases storage capacity.

D Increasing warehouse size automatically reduces the number of concurrent queries allowed.

💡 Hint

Think about how compute power and cost relate in Snowflake warehouses.

In Snowflake, larger warehouses have more compute resources, which speeds up queries but costs more because billing is based on compute usage.

❓ service_behavior

intermediate

Effect of Auto-suspend on Warehouse Billing

What happens to billing when a Snowflake warehouse is set to auto-suspend after 5 minutes of inactivity?

A Billing pauses only when the warehouse is manually suspended, not auto-suspended.

B Billing continues for 30 minutes after inactivity regardless of auto-suspend settings.

C Billing stops immediately after 5 minutes of inactivity because the warehouse suspends.

D Billing is unaffected by auto-suspend and continues as long as the account is active.

💡 Hint

Consider how auto-suspend controls warehouse state and billing.

Auto-suspend stops the warehouse after the set inactivity period, which stops compute billing since the warehouse is not running.

❓ Architecture

advanced

Choosing Warehouse Size for Concurrent Heavy Queries

You have a workload with many concurrent heavy queries that must complete quickly. Which Snowflake warehouse configuration is best?

A Use a single X-Large warehouse with multi-cluster enabled to scale out automatically.

B Use a Small warehouse and rely on query queuing to manage concurrency.

C Use multiple Small warehouses manually started and stopped as needed.

D Use a Medium warehouse with auto-suspend set to 1 minute.

💡 Hint

Think about how multi-cluster warehouses handle concurrency and heavy workloads.

Multi-cluster warehouses automatically add clusters to handle many concurrent heavy queries, improving performance without manual intervention.

❓ security

advanced

Security Implications of Warehouse Scaling

What is a key security consideration when enabling multi-cluster warehouses in Snowflake?

A Each cluster in a multi-cluster warehouse shares the same security context and access controls.

B Multi-cluster warehouses require separate user credentials for each cluster.

C Scaling warehouses automatically disables encryption for faster performance.

D Multi-cluster warehouses expose data to external networks by default.

💡 Hint

Consider how Snowflake manages security across clusters within the same warehouse.

All clusters in a multi-cluster warehouse operate under the same security policies and access controls, ensuring consistent security.

✅ Best Practice

expert

Optimizing Cost and Performance with Warehouse Scaling

You want to optimize both cost and performance for a variable workload with unpredictable spikes. Which Snowflake warehouse strategy is best?

A Set a Small warehouse with auto-resume disabled to control costs strictly.

B Use a Large warehouse always running to avoid startup delays during spikes.

C Manually start and stop multiple Small warehouses based on predicted workload.

D Configure a Medium warehouse with multi-cluster auto-scaling enabled and auto-suspend set to 5 minutes.

💡 Hint

Think about balancing automatic scaling and cost control features.

Auto-scaling with auto-suspend allows the warehouse to grow during spikes and pause when idle, optimizing cost and performance automatically.