Cost optimization strategies in Snowflake - Practice Problems & Coding Challenges

Challenge - 5 Problems

🧠 Conceptual

intermediate

Understanding Snowflake Warehouse Sizing for Cost Efficiency

Which Snowflake warehouse size option will generally cost the least while still allowing for moderate query performance?

A X-Small warehouse

B 2X-Large warehouse

C Large warehouse

D Medium warehouse

💡 Hint

Think about how warehouse size affects compute credits used per hour.

Smaller warehouses use fewer compute credits per hour, so an X-Small warehouse costs less than larger sizes. It is suitable for moderate workloads that do not require high concurrency or very fast processing.

❓ service_behavior

intermediate

Effect of Auto-Suspend on Snowflake Costs

What happens to your Snowflake compute costs if you enable auto-suspend on your warehouse with a 5-minute timeout?

A Compute costs continue until manually suspended

B Compute costs stop accruing 5 minutes after inactivity

C Compute costs are charged only during query execution, ignoring suspend settings

D Compute costs increase due to frequent suspends and resumes

💡 Hint

Consider what auto-suspend does to warehouse activity.

Auto-suspend automatically stops the warehouse after the specified idle time, so compute costs stop accruing after 5 minutes of no activity.

❓ Architecture

advanced

Designing a Cost-Effective Snowflake Data Sharing Architecture

You want to share data with multiple external partners without duplicating data and while minimizing costs. Which Snowflake feature should you use?

A Create separate databases for each partner and copy data

B Export data to external cloud storage for each partner

C Create multiple warehouses dedicated to each partner

D Use Snowflake Secure Data Sharing to share live data directly

💡 Hint

Think about sharing data without duplication and extra storage costs.

Snowflake Secure Data Sharing allows sharing live data without copying or moving it, reducing storage and maintenance costs.

❓ security

advanced

Cost Impact of Over-Provisioned Snowflake Warehouses

What is a likely cost consequence of using a warehouse size much larger than needed for your workload?

A Higher compute costs due to unused capacity

B Lower costs because larger warehouses are more efficient

C No cost difference because Snowflake charges per query

D Costs decrease due to faster query completion

💡 Hint

Consider how warehouse size relates to billing and usage.

Snowflake charges based on warehouse size and active time. Using a larger warehouse than needed wastes compute credits on unused capacity, increasing costs.

✅ Best Practice

expert

Optimizing Snowflake Storage Costs with Data Retention Policies

You want to reduce Snowflake storage costs by automatically removing old data that is no longer needed. Which approach is best?

A Manually delete old data tables weekly

B Use Time Travel retention with maximum duration to keep all data

C Set a data retention policy with a short Time Travel period and use automatic data purging

D Archive data externally and keep all data indefinitely in Snowflake

💡 Hint

Think about balancing data availability and storage cost.

Setting a short Time Travel retention period and enabling automatic purging removes old data efficiently, reducing storage costs while keeping recent data accessible.