🌟 Best Practices for Cost Control in Snowflake
Snowflake’s usage-based pricing model makes it powerful but can quickly become expensive if not managed carefully. Here’s how to keep costs in check!
1️⃣ Optimize Warehouse Sizing
-
✅ Match warehouse size to workload
- Use smaller warehouses (
XSMALL,SMALL) for light queries or ad-hoc work. - Use larger warehouses (
MEDIUM,LARGE) for complex transformations or big ETL loads.
- Use smaller warehouses (
-
✅ Scale-out instead of scaling up
- Instead of a huge warehouse, use multi-cluster to handle concurrency and minimize queueing (pay only when needed).
-
✅ Avoid always-on warehouses
- Use auto-suspend to pause them after inactivity.
- Set
AUTO_RESUME = TRUEto restart automatically when needed.
2️⃣ Use Auto-Suspend & Auto-Resume
- ✅ Set short auto-suspend intervals (30–300 seconds) for ad-hoc/BI workloads.
- ✅ Enable auto-resume to avoid user frustration when new queries come in.
💡 Tip: Avoid using 0 (no suspend), unless for critical workloads that must be always available.
3️⃣ Leverage Query Result Caching
- ✅ Snowflake automatically caches results for 24 hours by default.
- ✅ Encourage BI tools to reuse cached results (instead of re-running expensive queries).
4️⃣ Monitor Usage & Costs
-
✅ Use Account Usage Views:
SNOWFLAKE.ACCOUNT_USAGE.WAREHOUSE_METERING_HISTORYSNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY
-
✅ Use Cost Views:
SNOWFLAKE.ACCOUNT_USAGE.METERING_DAILY_HISTORY
-
✅ Set up Resource Monitors:
- Define credit limits for each warehouse/project.
- Automatically suspend warehouses when limits are reached.
-- Example: Create a resource monitor for 500 credits per month
CREATE RESOURCE MONITOR my_rm
WITH CREDIT_QUOTA = 500
TRIGGERS ON 90 PERCENT DO NOTIFY
ON 100 PERCENT DO SUSPEND;
5️⃣ Right-Size Data Storage
-
✅ Minimize data duplication:
- Use cloning instead of making copies of tables (zero-copy clones save costs).
-
✅ Leverage Time Travel wisely:
- Default 1-day Time Travel is often enough.
- Reduce retention for staging tables to save storage.
-
✅ Use data retention policies:
- Use
DROPorTRUNCATEfor old/unneeded data.
- Use
6️⃣ Optimize Data Ingestion
- ✅ Use streaming ingestion (Snowpipe, Kafka connectors) for real-time workloads.
- ✅ Batch load large files (e.g., Parquet, ORC) rather than many small files.
7️⃣ Control Access
- ✅ Avoid unnecessary usage by limiting user/role access to high-cost resources.
- ✅ Use role-based access control (RBAC) to isolate and manage access.
8️⃣ Educate Users
-
✅ Make sure analysts & data engineers know about:
- Query cost impact.
- Query best practices (filter early, avoid SELECT *).
- How to identify expensive queries using Query Profile.
9️⃣ Use Task & Pipeline Scheduling Efficiently
- ✅ Don’t over-schedule tasks; run them only when needed.
- ✅ Avoid running tasks during peak hours to balance load and avoid unnecessary warehouse scaling.
10️⃣ Evaluate Data Sharing Costs
- ✅ If sharing data with external partners, monitor shared object usage to avoid unexpected costs.
🚀 Impact on Performance
- ✅ Proper warehouse sizing + auto-suspend avoids wasted credits.
- ✅ Efficient tasks/pipelines avoid warehouse queueing, keeping performance snappy.
- ✅ Caching and smart ingestion reduce compute load, ensuring quick query response times.
⚠️ Common Pitfalls
❌ Forgetting to enable auto-suspend. ❌ Oversized warehouses left running overnight. ❌ Ignoring “hidden” costs (e.g., Time Travel & Fail-safe). ❌ Over-cloning or over-sharing data.
📌 Summary
By following these practices, Snowflake costs remain predictable, aligned to real usage, and avoid unnecessary waste—without compromising on performance.