Skip to main content

🌟 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.
  • ✅ 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 = TRUE to 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_HISTORY
    • SNOWFLAKE.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 DROP or TRUNCATE for old/unneeded data.

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.