Bird
Raised Fist0
SnowflakeHow-ToBeginner · 4 min read

How to Reduce Snowflake Cost: Practical Tips and Best Practices

To reduce Snowflake costs, optimize your virtual warehouses by resizing and suspending them when idle, and use clustering keys to improve query efficiency. Also, manage data storage by removing unused data and using data retention policies wisely.
📐

Syntax

Here are key Snowflake commands to control costs:

  • ALTER WAREHOUSE <name> SET WAREHOUSE_SIZE = <size>: Change warehouse size to control compute cost.
  • ALTER WAREHOUSE <name> SUSPEND: Pause warehouse to stop billing when not in use.
  • ALTER WAREHOUSE <name> RESUME: Restart warehouse when needed.
  • ALTER TABLE <table> CLUSTER BY (<columns>): Improve query performance by organizing data.
  • DROP TABLE <table>: Remove unused data to reduce storage cost.
sql
ALTER WAREHOUSE my_warehouse SET WAREHOUSE_SIZE = 'XSMALL';
ALTER WAREHOUSE my_warehouse SUSPEND;
ALTER WAREHOUSE my_warehouse RESUME;
ALTER TABLE sales CLUSTER BY (region, date);
DROP TABLE old_data;
💻

Example

This example shows how to reduce costs by resizing a warehouse, suspending it when idle, and creating a clustering key to speed up queries.

sql
ALTER WAREHOUSE analytics_warehouse SET WAREHOUSE_SIZE = 'SMALL';
ALTER WAREHOUSE analytics_warehouse SUSPEND;
-- Later when needed
ALTER WAREHOUSE analytics_warehouse RESUME;

ALTER TABLE orders CLUSTER BY (customer_id, order_date);
Output
Warehouse ALTERED Warehouse SUSPENDED Warehouse RESUMED Clustering key CREATED
⚠️

Common Pitfalls

Common mistakes that increase Snowflake costs include:

  • Leaving warehouses running when not in use, causing unnecessary compute charges.
  • Using warehouses that are too large for the workload, wasting resources.
  • Not using clustering keys on large tables, leading to slow queries and higher compute time.
  • Keeping old or unused data without archiving or deleting it, increasing storage costs.

Always monitor warehouse usage and automate suspension to avoid these issues.

sql
/* Wrong: Warehouse always running */
ALTER WAREHOUSE my_warehouse RESUME;
-- No suspend command used

/* Right: Suspend warehouse when idle */
ALTER WAREHOUSE my_warehouse SUSPEND;
📊

Quick Reference

  • Resize warehouses to fit workload: smaller size = lower cost.
  • Suspend warehouses when idle to stop billing.
  • Use clustering keys to speed queries and reduce compute time.
  • Delete or archive unused data to save storage costs.
  • Monitor usage regularly with Snowflake's Resource Monitors.
✅

Key Takeaways

Suspend virtual warehouses when not in use to avoid unnecessary compute charges.
Resize warehouses to the smallest size that meets your performance needs.
Use clustering keys on large tables to improve query efficiency and reduce compute time.
Remove or archive unused data to lower storage costs.
Regularly monitor usage and costs with Snowflake's built-in tools.