Warehouse selection strategies in Snowflake - Practice Problems & Coding Challenges

Challenge - 5 Problems

🧠 Conceptual

intermediate

Understanding warehouse sizing impact on query performance

Which Snowflake warehouse size will generally provide the fastest query execution for a large, complex data transformation workload?

A X-Small warehouse, because it uses fewer credits and is more cost-efficient

B Small warehouse, because it balances cost and performance for all workloads

C Medium warehouse, because it automatically scales to the workload size

D Large warehouse, because it has more compute resources to process queries faster

💡 Hint

Think about how more compute resources affect processing speed.

Larger warehouses have more compute clusters and resources, which generally allow faster query execution for complex workloads. Smaller warehouses save cost but run slower.

❓ service_behavior

intermediate

Effect of auto-suspend on warehouse cost and availability

What happens when a Snowflake warehouse is configured with auto-suspend set to 5 minutes?

A The warehouse runs continuously for at least 5 minutes before suspending, regardless of query activity

B The warehouse suspends automatically after 5 minutes of inactivity, reducing cost but causing a delay when resuming queries

C The warehouse suspends immediately after each query completes, saving maximum credits

D The warehouse never suspends automatically and must be manually stopped

💡 Hint

Consider how auto-suspend balances cost and query latency.

Auto-suspend pauses the warehouse after a set idle time to save credits. Resuming takes a few seconds, causing a slight delay for the next query.

❓ Architecture

advanced

Choosing warehouse strategy for concurrent user workloads

Your company has many users running queries simultaneously. Which warehouse strategy best supports high concurrency without queuing delays?

A Use multiple small warehouses manually assigned to different user groups

B Use a single X-Small warehouse and rely on query queuing

C Use a single large warehouse with multi-cluster enabled to scale out automatically

D Use a Medium warehouse with auto-suspend disabled

💡 Hint

Think about how Snowflake handles many simultaneous queries.

Multi-cluster warehouses automatically add clusters to handle concurrent queries, reducing queuing and improving user experience.

❓ security

advanced

Securing warehouse access with role-based policies

Which approach best restricts warehouse usage to authorized users in Snowflake?

A Grant warehouse usage privileges only to specific roles and assign users to those roles

B Allow all users to use the warehouse but monitor usage logs for unauthorized queries

C Disable warehouse auto-resume to prevent unauthorized query execution

D Use network policies to block access to the warehouse endpoint

💡 Hint

Consider Snowflake's role-based access control features.

Granting warehouse usage privileges to roles and assigning users accordingly enforces strict access control at the warehouse level.

✅ Best Practice

expert

Optimizing cost and performance with warehouse auto-scaling policies

You want to optimize both cost and performance for a variable workload using Snowflake warehouses. Which configuration achieves this best?

A Enable multi-cluster warehouse with minimum clusters set to 1 and maximum clusters set to 5, with auto-suspend after 10 minutes

B Use a single large warehouse with auto-suspend disabled to avoid delays

C Use multiple X-Small warehouses manually started and stopped by users

D Set auto-suspend to 1 minute and disable auto-resume to control costs strictly

💡 Hint

Think about how auto-scaling and auto-suspend work together.

Multi-cluster warehouses with auto-scaling adjust resources to workload demand, while auto-suspend saves cost during idle times, balancing performance and cost.