Cost Savings on Snowflake: How to Optimize Your Cloud Data Warehousing Expenses

In today’s technology-driven environment, many organizations are shifting away from on-premises systems in favor of cloud-based platforms like Snowflake. Snowflake, a cloud-based data warehouse, operates on Amazon Web Services (AWS), Azure, and Google Cloud Platform (GCP), providing flexibility and scalability. While the benefits are clear, controlling costs can be a real challenge.

Understanding Snowflake Pricing

Snowflake’s pay-as-you-go pricing model may seem appealing, but if not monitored closely, expenses can escalate rapidly. Here’s a breakdown of how Snowflake’s costs are determined:

Compute Costs: These typically make up the largest portion of your bill and depend on how long your warehouses run and the amount of compute power used. Warehouses range from x-small to 6X-large, with costs doubling between each tier. Snowflake credits are a distinctive entity used to measure compute costs, and they’re translated into dollars in the monthly bill. The value of a credit varies depending on factors such as:

  • Snowflake Edition: Snowflake offers several editions, each tailored to different business needs:
    • Standard
    • Enterprise
    • Business Critical
    • Virtual Private Snowflake (VPS)
  • Cloud Provider: Snowflake operates on various cloud platforms, including:
    • Amazon Web Services (AWS)
    • Microsoft Azure
    • Google Cloud Platform (GCP)
  • Region: The cost of credits also depends on the region where your Snowflake account is provisioned. Different regions may have variations in pricing due to local regulations, currency exchange rates, and other factors.

Storage Costs: These are calculated based on your average monthly storage consumption, charged in dollars. Often lower than compute costs, storage fees are based on the amount of data stored across tables, clones, and regions.

Data Transfer Costs: These are incurred when transferring data between Snowflake regions or other cloud providers.

Knowing these factors will enable you to take decisive steps in optimizing resources and cutting spending.

 Effective Ways to Reduce Snowflake Costs

    1. Choose the Right Warehouse Size: Match the size of your Snowflake warehouse with the query load. Use large warehouses for heavy queries and small ones for lighter tasks. Start with a smaller virtual warehouse, like x-small, and monitor its performance before scaling up. This approach prevents unnecessary spending on larger-than-needed warehouses.
    2. Suspend Idle Warehouses: Auto-suspend idle warehouses to avoid unnecessary compute charges. You can set a time limit for auto-suspension and even enable auto-resumption when a warehouse gets queried. This can be done by setting an auto-suspend time, even as low as 1 minute, using an SQL statement.
    3. Update the Query Timeout Default Value: Alter the default timeout value to avoid being charged for potentially erroneous long-running queries. Adjust the default timeout value to something more reasonable, like 2 hours, to avoid charges for unnecessarily long-running queries.
    4. Use Resource Monitors: Tracking credit usage through resource monitors helps you stay within budget. Set credit thresholds at different levels to receive timely alerts. Implement alerts and actions when monthly credit consumption thresholds are reached. This can prevent unexpected overages and provide better budget control.
    5. Split Large Files: Minimize processing overhead by exporting large files in smaller chunks. This enables parallel processing, reducing compute time.
    6. Create Alerts for Reader Accounts: Keeping tabs on reader accounts helps in controlling unexpected spikes in costs. Setting resource monitors for reader accounts can help prevent unpleasant surprises.
    7. Utilize Zero-Copy Cloning: Save on storage costs by creating clones that use pointers to live data instead of needing additional storage. Just remember to delete both the original and cloned tables you’re not using.
    8. Educate Users on Best Practices: Regularly review users’ queries and educate them on more efficient practices. For example, they can use SELECT TOP 10 * instead of SELECT * for quick data previews.
    9. Store Data Files in the Same Cloud Provider and Region: This alignment minimizes data transfer costs, a critical consideration when setting up your Snowflake account.
    10. Analyze Account Usage: Regularly review your account usage, including query history and warehouse metering. Look for patterns and inefficiencies to optimize and eliminate.

The following sections delve into various aspects of data management, from loading to storing, and offer insights and tips:

Loading

Although storing data in Snowflake is very different from traditional disk storage, there are many strategic benefits to loading data into Snowflake.

    1. Sort on Ingestion: Data is automatically partitioned in Snowflake on natural ingestion order. Sorting an S3 bucket (e.g., using syncsort) before a bulk load via copy can be faster than inserting with an ORDER BY clause.
    2. Preferred Format: CSV (Gzipped) is the best format for loading to Snowflake, being 2–3x faster than Parquet or ORC.
    3. Use COPY INTO: Use COPY INTO instead of INSERT because it utilizes more efficient bulk loading processes.

Sizing

Take advantage of the native cloud ability to scale, create, and optimize your compute resources.

    1. Scaling Strategy:
      • Scale up or out as needed.
      • Note: If >160 GB for AWS or >400 GB for Azure, it will spill over to remote IO.
      • Large query increases Data Warehouse size, whereas many small queries require an increase in the number of Data Warehouses or clusters.
    2. Virtual Warehouses: Turn your Virtual Warehouse on and off for certain workloads.
    3. Query Control: Control query processing and concurrency with parameters like max_concurrency_level, statement queued timeout, and statement timeout in seconds.
    4. Monitoring: Use warehouse monitoring to size and limit cost per workload, not per database.

Data Modeling

Organizing your information into a mature data model allows for high-performance SQL scripting and better caching potential.

    1. Create Data Models: Use Star Schema, 3NF, or Data Vault for analytics.
    2. Constraints: Build queries to check for violations, as Snowflake doesn’t enforce constraints.
    3. Error Alerts: Build a process to alert you of loading issues.

Tracking Usage

Snowflake stores extensive usage data for analysis, allowing you to track expenses.

    1. Account Views: Use Account Usage Views for tracking history, performance, and cost.
    2. Role Management: Create roles by business functions to track spending.
    3. Resource Monitors: Use Resource Monitors to control Data Warehouses based on predefined credit limits.

Performance Tuning

Use the history profiler to improve queries and monitor performance.

    1. Query Optimization: Utilize the history profiler to optimize queries.
    2. Bytes Analysis: Analyze Bytes Scanned: Remote vs. Cache.
    3. Partitioning: Make the ratio of partitions scanned to partitions used as small as possible.

SQL Coding

Poorly written code can drive up costs in Snowflake. Focus on improving SQL scripts.

    1. Temporary Tables: Drop temporary and transient tables when done.
    2. Table Creation: Use “CREATE OR REPLACE” instead of “CREATE TABLE AS”.
    3. Joins: Use ANSI Joins, not the “WHERE a.id=b.id” format.
    4. Windowing: Use “WITH” clauses instead of temp tables or sub-selects.
    5. Sorting: Avoid ORDER BY; sorting is expensive.
    6. Duplicates: Don’t handle duplicate data using DISTINCT or GROUP BY.

Storing

Set up the Snowflake deployment for seamless integration with your entire data ecosystem.

    1. S3 Buckets: Locate them in the same geographic region.
    2. File Organization: Set up buckets to match incoming files (e.g., by date or application).
    3. File Size: Keep files between 60–100 MB for parallelism.
    4. Materialized Views: Use them only in specific use cases.

Moving to the Cloud with Confidence

Switching from on-premises systems to cloud platforms like Snowflake offers enormous potential. However, understanding the cost structure is essential. With the tips and tricks above, you can significantly optimize your Snowflake expenses without compromising performance.

Whether you’re a small business trying to keep costs low or a large enterprise looking for ways to streamline operations, these strategies provide easy-to-implement and reversible solutions to help you take full advantage of what Snowflake has to offer.

Snowflake is a key player in the cloud data warehousing market, and with the right approach, you can make it a cost-effective solution for your organization. At Anyon Consulting, we understand the complexities of managing and optimizing Snowflake deployments. Our experienced team of professionals can help you take advantage of all the powerful features that Snowflake offers, tailoring solutions to meet your specific needs. From loading strategies and data modeling to performance tuning and secure storing, we’re here to guide you every step of the way. Don’t hesitate to reach out to us to discuss how we can assist your organization in achieving optimal performance and efficiency with Snowflake.

Scroll to top