Snowflake Cost Optimisation – Cut Compute, Storage & Service Costs

Completely rewritten: 11th August 2025

Introduction

Snowflake is one of the most powerful data platforms available — but its flexibility can also make it dangerously easy to overspend. In nearly every Snowflake deployment I’ve worked on, compute waste accounts for the majority of the bill.

This guide combines years of real-world experience (including the strategies from my original cost optimization article) with additional best practices from SELECT’s cost reduction framework and Snowflake’s own Cost Insights documentation.

We’ll start with identifying where your spend is going, then move into the highest-impact optimizations — from warehouse sizing to workload tuning, table design, and automated controls.

Strategy and Benefits Summary

1. How do I identify the major source of Snowflake costs?

1.1: Problem

In traditional on-premises or fixed-capacity cloud data warehouses, hardware is a sunk cost — once the cluster is purchased or provisioned, running queries doesn’t increase the bill.

In Snowflake, every active compute-second, every gigabyte stored, and every serverless operation directly translates into charges.

The problem is that many Snowflake customers jump straight into tuning queries or reducing storage without first understanding where the spend is actually going. Optimising the wrong component may yield negligible savings while the real cost drivers remain untouched.

For example, I had one customer that spent weeks of Data Engineering time focussed on tuning deep-diving into query plans, tuning queries and rewriting SQL only to find the main problem was they were running on warehouses too small for the workload.

---

1.2: Snowflake Insights

The diagram below illustrates the Snowflake hardware architecture which is made up of three distinct layers including Cloud Services, Compute (Virtual Warehouses) and Storage).

Snowflake costs closely mirror this architecture and consist of:

1. Cloud Services

   • Normally tiny, this is used to compile queries and implement features not executed on a virtual warehouse including cloning.

   • Build per-second, however free if the total cloud services cost is less than 10% of the daily compute charging from virtual warehouses.

2. Compute (Virtual Warehouses)

   • Billed per-second after a 60-second minimum.

   • Usually 80–90% of total cost for most accounts.

   • Includes credits used for query execution, transformations, and batch/ETL processing.

   • Different from on-prem: compute is elastic — you can add/remove capacity at will, but you pay for every second it’s running.

3. Storage

   • Billed per TB/month.

   • Covers active storage, Time Travel history, and Fail-safe copies.

   • Unlike local disk storage on-prem, Snowflake storage scales automatically and you’re charged for retention policies you set.

4. Serverless Features

   • Billed separately from warehouses.

   • Examples: Snowpipe, Automatic Clustering, Search Optimisation Service, Database Replication, serverless Tasks.

   • Charges are often per-second or per-operation and can grow unnoticed if left unmonitored.

5. Data Transfer Costs

   • Charged by the cloud provider for cross-region or cross-cloud traffic.

   • This isn’t unique to Snowflake, but it’s invisible until you start replicating data between accounts or serving multi-region workloads.

Key Snowflake difference:

With on-prem, capacity is fixed, and the cost of an inefficient process is “only” lost time. In Snowflake, inefficiency directly increases cost because it consumes billable compute seconds or additional serverless calls.

---

1.3: Solution & Best Practices

1. Use Snowflake Cost Insights for a high-level breakdown

• In Snowsight:

  Admin → Cost Management → Cost Insights

• Filter by Service Type to see the proportion of spend across Compute, Storage, and Serverless.

• Use the Group By option to break down spend by Warehouse, Database, or Tag.

2. Attribute compute spend to workloads

• Run the following to find top-spending warehouses in the past 30 days

• In terms of reducing cost - start with the most expensive warehouses - you may find it too follows the 80/20 rule and 80% of the spend is from 20% of the warehouses.

SELECT warehouse_name,
       SUM(credits_used_cloud_services + credits_used_compute) AS total_credits
FROM snowflake.account_usage.query_history
WHERE start_time >= DATEADD('day', -30, CURRENT_TIMESTAMP)
GROUP BY warehouse_name
ORDER BY total_credits DESC;

• Use the following SQL at the beginning of your longest running transformation jobs to tag

• Use this to track the cost of your biggest jobs.

alter session set query_tag = 'XXXXX';
-- Job steps
alter session unset query_tag;

• Add QUERY_TAG in your ETL/BI tools to label jobs. This allows grouping by workload type:

SELECT query_tag,
       SUM(credits_used_cloud_services + credits_used_compute) AS total_credits
FROM snowflake.account_usage.query_history
WHERE start_time >= DATEADD('day', -30, CURRENT_TIMESTAMP)
GROUP BY query_tag
ORDER BY total_credits DESC;

3. Identify under-utilised warehouses

• Use SNOWFLAKE.ACCOUNT_USAGE.WAREHOUSE_LOAD_HISTORY:

SELECT warehouse_name,
       AVG(avg_running) AS avg_running_clusters,
       AVG(avg_queued_load) AS avg_queued
FROM snowflake.account_usage.warehouse_load_history
WHERE start_time >= DATEADD('day', -30, CURRENT_TIMESTAMP)
GROUP BY warehouse_name;

• Low avg_running with low queue time indicates over-provisioning.

---

How to Verify the Fix

• Before making changes, snapshot baseline spend from Cost Insights.

• After implementing optimizations (e.g., warehouse resizing, auto-suspend changes), re-run the same queries 2–4 weeks later.

• Look for percentage reduction in compute credits for the same workloads.

---

How to Monitor Ongoing Spend

• Review Cost Insights monthly at minimum; weekly if costs are growing rapidly.

• Set up an alert dashboard (either in Snowsight or BI tool) showing:

  • Top 5 spending warehouses

  • Monthly cost trend

  • Compute vs Storage vs Serverless percentages

• Use Resource Monitors to prevent runaway costs (covered in Section 6).

---

2. What’s the Best Practices for Virtual Warehouse Configuration?

2. 1: Right-Size Your Virtual Warehouses

Although this needs some work to deploy, it has proven to have the single biggest impact upon compute cost. It’s based upon the fact that often jobs are executing on a virtual warehouse that’s often far larger than needed. However, this is hidden by the fact that some jobs actually need the bigger warehouse.

Insights

Avoid overpaying by ensuring your warehouse matches your workload.

• Insight: If you execute your jobs on a larger warehouse, provided the queries run twice as fast, it cost nothing extra. As soon as scaling up runs less than twice as fast - you’re paying extra.

• Tip: Only scale up if elapsed time reduces by 50% or more.

Right Sizing the Warehouse: How to find the right warehouse size for a batch job?

1. Set up your JOB (ie. a script with a sequence of statements) on a virtual warehouse with nothing else running.

2. Run the JOB on an XSMALL (or bigger if that’s clearly a silly idea)

3. Run the JOB on the next size up (eg. SMALL or MEDIUM)

4. If it runs twice as fast repeat step (3). If it runs less than twice as fast continue to next step.

5. At this point decide which is the higher priority: (a) Cost (b) Performance. If cost is the priority, execute the job on the previous warehouse size, if performance is the priority use the current warehouse size.

Demonstration

The chart below shows the improvement in elapsed time of a query copying 1.3TBs of data. Each time we increased warehouse size, the elapsed time was halved - until it wasn’t. Once you’re not getting results twice as fast, you’re paying more.

Further Reading

Snowflake Virtual Warehouses - What you need to know

Snowflake Warehouses - Bigger is not necessarily faster

🔗 Warehouse Sizing – Snowflake Docs

---

2.2: Carefully deploy new Virtual Warehouses

Virtual Warehouse deployment is one of the least understood (and expensive when it’s incorrect). Here’s how to do it. Designate virtual warehouses based upon the workload size and required performance - how fast do you NEED the queries to complete.

• COPY Data Loading - Strictly an XSMALL (each file will only use a single CPU - no benefit in scaling up). Consider using MAX_CLUSTER_COUNT = 3 or more if you have more than 8 parallel loads. Each XSMALL will load 8 COPY statements in parallel.

• Batch processing - Typically MEDIUM or LARGE size (depending upon expected workload). This is for transformation processing where the priority is throughput not performance. Use SCALING_POLICY=ECONOMY if you see excessive queuing from parallel batch processing and you find jobs get held up. Note: Expect queuing on this warehouse - it’s a good indicator that you’re keeping compute resources fully utilized and you’re saving money on the top 80% of Snowflake compute costs.

• End User queries (short, fast queries). SMALL (or perhaps XSMALL) with a MAX_CLUSTER_COUNT = 3 or more and the default SCALING_POLICY. Expect most queries to finish within 60 seconds - if you find users running huge queries (15 minutes or more), move them to the Large, Complex End-user queries warehouse.

• Large, complex end-user queries - Sized at MEDIUM or larger this is for end-user processing (eg. Data Scientists or power users executing data analytics). These queries should take minutes to an hour to complete. Leave the SCALING_POLICY as default to allow it to scale out without queuing to maximize performance and eliminate queuing.

• Machine Learning - Including complex compute and memory intensive processing - deploy a Snowpark Optimized Warehouse. However, this is often an edge case and only the largest Snowflake deployments are likely to need this.

The diagram below illustrates an ideal virtual warehouse deployment strategy.

The above diagram shows:

1. A single virtual warehouse for 99% of COPY loading. An X-Small for most of the loads which are single files under 100MB in size. Only consider using a larger sized warehouse for large loads with many files in the 100-250MB size.

2. A range of X-Small upwards for batch transformations. The jobs should be executed on the appropriate warehouse size.

3. A range of warehouses purely for end-user querying and consumption, each sized appropriately to the data volume and query complexity.

The mistake made by nearly every Snowflake deployment is to use a set of virtual warehouses for each application on the assumption it’s more important to allocate costs back to the application owner than reduce overall costs. However, this leads to massive inefficiency with many warehouses running at less than 40% of full capacity and sky-rocketing cost.

2.3: Set the Warehouse AUTO_SUSPEND time

By far the easiest and most effective way to minimizing Snowflake cost is to limit the time spent waiting for idle warehouses to suspend. It’s a little known fact that Snowflake defaults to 10 minutes before warehouses are automatically suspended.

Set the AUTO_SUSPEND after 60 seconds of inactivity to minimize wasted compute and be aware the billing minimum is 60s when a warehouse is provisioned.

Execute the following SQL to set the maximum auto-suspend time to 60 seconds. Only in extreme cases (where your queries are getting a huge performance improvement from the virtual warehouse cache will it make sense to set it larger.

ALTER WAREHOUSE my_wh SET AUTO_SUSPEND = 60;

Further Reading:

https://articles.analytics.today/snowflake-virtual-warehouses-what-you-need-to-know

https://articles.analytics.today/snowflake-virtual-warehouses-is-bigger-faster-but-more-expensive

🔗 Snowflake Docs – Manage Virtual Warehouses

🔗 Article – Boost Snowflake Query Performance

More Information

2.4: Use Multi-Cluster Warehouses with ECONOMY Scaling

One of the single biggest mistakes made by Snowflake administrators to assume query queuing must be avoided at all cost. In reality, the only way to guarantee warehouses are being fully used to detect queuing.

Batch transformation jobs prioritize throughput (the ability to process massive data volumes) rather than speed. Although a given batch operation must complete in time, it’s typically not critical to complete each SQL statement as fast as possible.

Setting the SCALING_POLICY = ECONOMY means as the warehouse reaches 100% of capacity, queries are queued for up to six minutes before allocating an additional cluster. This means each node has enough work to keep it fully busy which in turn leads to reduced cost.

create or replace warehouse batch_vwh with
    warehouse_size = XLARGE
    min_cluster_count = 1
    max_cluster_count = 3
    scaling_policy = ECONOMY
    auto_suspend = 60;

---

2.5: Use STANDARD Scaling ONLY for End-User Queries

Unlike batch processing, end-user queries typically have end-users waiting for results and the priority is therefore elapsed time (speed). Setting the SCALING_POLICY = STANDARD eliminates queuing and allocates additional clusters immediately the node reaches 100% of capacity. This leads to higher cost, but improves query performance.

SQL:

create or replace warehouse dashboard_vwh with
    warehouse_size = SMALL
    max_cluster_count = 3
    scaling_policy = STANDARD;

🔗 Scaling Policy – Snowflake Docs

https://docs.snowflake.com/en/user-guide/warehouses-multicluster#setting-the-scaling-policy-for-a-multi-cluster-warehouse

You can view queuing using the following query which shows the average queuing time and the number of queries where the queuing time was more than 5% of the total elapsed time.

SELECT 
    WAREHOUSE_NAME,
    COUNT(*) AS TOTAL_QUERIES,
    COUNT_IF(QUEUED_OVERLOAD_TIME > 0.05 * TOTAL_ELAPSED_TIME) AS QUERIES_QUEUED_OVER_5_PERCENT,
    ROUND(AVG(QUEUED_OVERLOAD_TIME/1000)) AS AVG_QUEUE_SECONDS
FROM 
    SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY
WHERE 
    START_TIME >= DATEADD(DAY, -7, CURRENT_TIMESTAMP)
    AND WAREHOUSE_NAME IS NOT NULL
    AND TOTAL_ELAPSED_TIME > 0
GROUP BY 
    WAREHOUSE_NAME
ORDER BY 
    TOTAL_QUERIES DESC;

---

2.6: Never (EVER!) Set AUTO_SUSPEND = 0

I had one customer that executed the following query for a set of test warehouses. Once a query was executed, these warehouses immediately started and when manually suspended clocked up over $120,000 over a few days.

create or replace warehouse test with
   warehouse_size = 'X3LARGE'
   min_cluster_count = 8
   max_cluster_count = 10
   auto_suspend = 0;

Setting the AUTO_SUSPEND=0 means the warehouse will not automatically suspend until a user executes:

alter test suspend;

The only reason you’d need to set the AUTO_SUSPEND to zero is if you were certain a sequence of jobs were going to run on the warehouse and you needed to keep it running when idle, perhaps to maintain the warehouse cache. You would then need to manually suspend the warehouse once the jobs were complete

This is, however, a very unusual case.

https://docs.snowflake.com/en/user-guide/warehouses-overview#auto-suspension-and-auto-resumption

---

2.7: Be wary of setting the MIN_CLUSTER_COUNT

In one case, a customer complained that their Snowflake costs where too high, and when I checked their warehouse configuration it showed the following profile with the workload varying during the day (the red line), but the MIN_CLUSTER_COUNT fixed at five.

The reason was their virtual warehouse configuration was as follows:

create or replace warehouse transformation_jobs with
   warehouse_size = MEDIUM
   min_cluster_count = 5
   max_cluster_count = 5
   auto_suspend = 60;

Simply changing the MIN_CLUSTER_COUNT to 1, saved over $200,000 per year on this single warehouse and the diagram below illustrates the new scale out profile.

2.8: Set Query Timeouts (STATEMENT_TIMEOUT_IN_SECONDS)

In another case, I had a Snowflake administrator run a cartesian join query that ran for two days on an X4LARGE warehouse, again clocking up a huge bill over the weekend. The reason, was the warehouse had no limit on the spend for an individual warehouse.

alter warehouse batch_vwh set statement_timeout_in_seconds = 14400;  -- 4 Hours - maximum cost $1536

The following table shows the maximum allowed cost of executing a single SQL statement assuming $3 per credit. Simply set the statement timeout accordingly, and adjust the number of seconds based upon the acceptable maximum cost of each query.

Be aware however, if the query execution time exceeds the statement timeout, the query will be automatically terminated. Be careful to monitor the situation, especially for production warehouses.

2.9: Monitor Query Timeout

One customer I worked with set the QUERY_TIMEOUT limit way too small for warehouses and as a result, queries were frequently terminated. However, because the end-users were unaware of why their queries were terminated, they were simply re-executed.

This. lead to significant credit waste as each time the query was terminated but still billed.

Use the following query to monitor query timeout events and report which users experience the problem. These either need to be educated or moved to a larger warehouse.

SELECT 
    TO_DATE(START_TIME) AS QUERY_DATE,
    TO_CHAR(START_TIME, 'HH24:MI:SS') AS QUERY_TIME,
    USER_NAME,
    ERROR_CODE,
    FLOOR(TOTAL_ELAPSED_TIME / 3600) AS HOURS,
    FLOOR(MOD(TOTAL_ELAPSED_TIME, 3600) / 60) AS MINUTES,
    FLOOR(MOD(TOTAL_ELAPSED_TIME, 60)) AS SECONDS
FROM 
    SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY
WHERE 
    START_TIME >= DATEADD(DAY, -7, CURRENT_TIMESTAMP)
    AND ERROR_CODE = '604'  -- STATEMENT_TIMEOUT_IN_SECONDS
ORDER BY 
    USER_NAME, START_TIME DESC;

3. What’s the Best Practices for Snowflake Query Tuning?

Although it may not be the most efficient way to reduce costs on the an existing deployment which may have thousands of queries, these steps should form part of the project best practices.

Execution queries efficiently will by definition reduce costs by executing queries efficiently.

3.1: Avoid Wrapping Columns in the WHERE clause

Consider the following simple query which filters results for a specific date. Can you see why this might impact query performance?

select *
from orders
where to_char(o_orderdate,'YYYY-MM-DD') = '2025-01-24';

The problem is in the use of the TO_CHAR() function which wraps the O_ORDERDATEcolumn. Simply re-writing this query as follows leads to a massive five times improvement in query performance because it allows Snowflake to filter out data more effectively using partition pruning.

select *
from orders
where o_orderdate = to_date('YYYY-MM-DD') = '2025-01-24';

3.2: Avoid using SELECT *

Snowflake stores data in columnar format which means it’s optimized for queries that return a few columns from potentially very wide tables. However, if every query returns every column, it has more work to do.

Consider the following queries:

select o_orderkey
,      o_totalprice
from  ORDERS;

select *
from  ORDERS;

I executed a benchmark test on an XSMALL warehouse and the results were as follows

Selecting only two columns completed in just 3m 21s whereas SELECT * took 15m 31s - a 500% improvement in query performance.

You’ll see that both queries scanned 3,242 micro-partitions (a full table scan), but the SELECT * query scanned over 48GB of data - around four times the volume of the previous query.

Clearly, you should avoid SELECT * unless you really need to return the full data set.

3.3: Maximize Query Performance using Partition Pruning

Every time you execute a query on Snowflake the WHERE clause is examined to limit the number of micro-partitions scanned. This works for every column on the table and is completely automatic. However, you can improve this “Partition Pruning” if you know how it works.

Consider the diagram below which shows how Snowflake actually stores data. It holds metadata in the Cloud Services layer including the minimum and maximum value of every column in every micro-partition.

In the above example, we’ve loaded data each day from January to March and the dat is appended into four micro-partitions.

Now let’s say we execute the following query:

select *
From sales
where sale_date = to_date('14-Feb-2026','DD-MON-YYYY');

Snowflake will automatically apply Partition Pruning against the table. Using the metadata, it knows the data cannot be in any other micro-partition than number 3, and therefore all other micro-partitions are pruned (skipped) which has a dramatic impact upon query performance.

The diagram above illustrates how this works, and best of all it works automatically against every column that appears in the WHERE clause.

The screenshot below illustrates the potential performance benefits of using partition pruning:

The screenshot above illustrates how two queries against the same table can produce dramatically different performance. The one on the left scanned the entire table whereas the one on the right eliminated all but four micro-partitions and was 740 times faster.

3.4: Consider Using Data Clustering

As we can see above, Partition Pruning can have an amazing impact upon Snowflake query performance. Using Data Clustering we can ensure queries against specific keys which frequently appear in the WHERE clause are maximized.

The diagram below illustrates what Data Clustering really means - effectively we “sort” the data by a given key which means data is “clustered” together.

The table on the left shows the data as it was loaded, whereas the table on the right shows the same results when the data is clustered by SALE_DATE. It’s easy to add a cluster key to a table using the following SQL:

alter table SALES
cluster by SALE_DATE;

Be aware however, clustering is NOT the same as an index, and the actual data sort is executed in background (taking hours on a large, terabyte size table). I’ve also seen many projects spend huge effort and Snowflake credit cost and achieved nothing.

When applying clustering to an existing table, it’s normally more efficient to sort the data for the initial clustering and then apply a clustering key. You can estimate the cost of initial and ongoing clustering using the SYSTEM$ESTIMATE_AUTOMATIC_CLUSTERING_COSTS function.

Further Reading:

Best Practices to Maximize Query Performance Using Snowflake Clustering Keys

https://docs.snowflake.com/en/sql-reference/functions/system_estimate_automatic_clustering_costs?utm_source=chatgpt.com

3.5: Cluster Dimensional Tables by Join Key

Consider the following query which joins results from the SALES and PRODUCTS tables. This is a classic query from a Dimensional Model whereby we need to analyze both sales by product categories.

select product type,
       sum(sales)
from   sales s,
       products p
where  s.product_key = p.product_key
and    s.sale_date between '01-JAN-2026' and '31-JAN-2026';

One way to maximize query performance is to include the join key in the cluster key as follows:

alter table SALES
   cluster by SALE_DATE, PRODUCT_KEY;

alter table PRODUCTS
   cluster by PRODUCT_KEY;

This works effectively because Snowflake is able to execute run-time optimizations to return only the PRODUCT entries that match the corresponding SALES.

Of course this can only be achieved with a single join table, but it’s a useful tip to remember for large dimensional tables like PRODUCTS or CUSTOMERS which are frequently joined to large fact tables.

3.6: ORDER or GROUP BY on Clustered Keys

It’s worth understanding not just that data clustering improves query performance, but why. Data clustering effectively sorts the data by the key and stores it together in the micro-partitions. We can use this to maximize performance, not just for partition elimination but to speed up ORDER BY or GROUP BY operations.

Consider the query below which ran against a huge data volume (3,242 micro-partitions), but returned in just 10 seconds:

select o_orderdate
,      sum(o_totalprice)
from orders
group by 1
order by 1;

The Query Profile above illustrates that despite sorting the entire table, the query spent just 9.8% waiting for CPU time (percentage waiting for processing), and the query completed within ten seconds. The underlying reason was the data was clustered by O_ORDERDATE which meant there was almost nothing to do in the sort operation.

3.7: Avoid Row-By-Row Processing

Consider the following simple Snowflake Script which inserts just 10 rows. How fast do you think this will complete? One second, two seconds?

create table row_by_row (x varchar);

execute immediate $$
-- Snowflake Scripting code
begin
  for x in 1 to 10 do
    insert into row_by_row values ('X');
  end for;
end;
$$;

Now compare the above script to the following query which inserts three million entries. Do you think this will run faster or slower than the script above.

insert into orders
select o_orderstatus
from sample_data.tpch_sf1000.orders
limit 3000000;

You may be shocked to find the query which inserted 10 rows took 12 seconds, but inserting three million rows took just 2 seconds.

I’ve had similar experience with the Oracle database. Modern relational databases are tuned not for individual row-by-row processing, but massive bulk processing operations.

3.8: Filter Early and Effectively

The fewer rows you need to process, the faster your queries will complete. This may seem obvious, but it’s surprising to find queries which don’t filter out rows using a WHERE clause (especially in inline views) which potentially cripples query performance.

Consider the query profile above of a query take took over two hours to complete. The query spent 63% of it’s time waiting for processing which indicated a large sort operation, but in reality this was misleading. In reality it performed a full table scan of 15,514 micro-partitions, whereas adjusting the WHERE clause restricted the rows fed into the Window Function and had a massive impact upon query performance.

3.9: Use the LIMIT clause

Let’s assume you’re working on a new project and you need to see the data in a table. You’d most likely execute the following query:

select *
from sales;

However, you could be waiting for minutes or even hours before you get to see the results, even if you only want to see the first few rows.

The diagram above illustrates why this simple query takes so long. Any results (even when executed on a huge virtual warehouse) must first be returned via the results cache and a massive table will often be spilled to remote storage which kills query performance.

If however you use a LIMIT clause you can speed the query as Snowflake knows you only need the first X rows. For example:

select *
from sales
LIMIT 1000;

In a benchark test the query above returned in 50 milliseconds compared to two minutes without the LIMIT caluse. Best of all, this always works - even if you have an ORDER BY clause.

3.10: Avoid using OR in the WHERE clause

Consider the following query which falls into the trap known as the disjunctive OR problem.

select l_orderkey
,      l_partkey
,      l_suppkey
,      l_quantity
from lineitem
,    partsupp
where l_partkey = ps_partkey
      or
      l_suppkey = ps_suppkey;

The difficulty here is the query plan generates a Cartesian Join as illustrated below, and this will kill your query performance as an 8m and 60m join produces nearly 5 billion rows:

The sensible approach is to simply rewrite the query as two separate joins, and UNION the result sets of both as shown below:

select l_orderkey
,      l_partkey
,      l_suppkey
,      l_quantity
from lineitem
,    partsupp
where l_partkey = ps_partkey
union
select l_orderkey
,      l_partkey
,      l_suppkey
,      l_quantity
from lineitem
,    partsupp
where l_suppkey = ps_suppkey;

The new query profile (although it appears larger) combines the results of two independent joins and produces results about 25 times faster than before.

Finally, the query profile overview below shows the difference in performance.

Simply rewriting the query as a UNION produced the results over 200 times faster than using the OR clause in the WHERE.

4. How would I optimize Snowflake storage costs?

Snowflake system administrators correctly focus on the cost of compute rather than storage as this represents over 80% of overall costs. However, there’s often significant savings in storage that are relatively easy to identify.

4.1: Remove Files from Internal Stages when loaded

Internal Stages are primarily intended as a temporary storage area for data files before loading into Snowflake. However, I have frequently identified problems during Snowflake customer visits, such as customers not removing the data files once loaded, which leads to excessive storage costs.

Be aware also that, unlike Snowflake table storage, which uses columnar compression techniques to reduce data from up to 500MB to just 16MB, Stage storage uses simple row-level compression and has a much larger storage footprint.

The diagram below illustrates a situation I found at one multi-national Snowflake customer.

The graph above shows the total monthly storage broken by type. It shows that as of January 2024, the customer was using:

• Active Storage: Around 220TBs of active storage. This represents the data storage for tables, including time travel.

• Stage Storage: Around 250TBs of Stage storage. This is the storage used to stage internal data files.

• Failsafe Storage: 3TBs of storage. This is the storage used to recover data deleted by accident.

Clearly, the customer used a regular load into an internal stage before copying the data into Snowflake but forgot to delete it afterward.

How to Identify a Stage Storage Issue?

The SQL statement below can be used to identify a potential storage issue around Stage Storage quickly.

select to_char(usage_date,'YYYYMM') as sort_month
,      to_char(usage_date,'Mon-YYYY') as month
,      trunc(avg(storage_bytes)) as storage
,      trunc(avg(stage_bytes)) as stage
,      trunc(avg(failsafe_bytes)) as failsafe
,      round(avg(stage_bytes) / avg(storage_bytes) *100,1 )     as pct_stage
,      round(avg(failsafe_bytes) / avg(storage_bytes) *100,1 )  as pct_failsafe
from snowflake.account_usage.storage_usage
group by month, sort_month
order by sort_month;

Effectively, if the stage storage percentage of active storage is above 3-5%, it's worth investigating and removing unnecessary data files.

How to Resolve a Stage Storage Issue?

The simple way to reduce stage storage is to remove the existing data files. However, if the system continuously delivers and loads data files, suspending the load may be sensible.

The following SQL commands will LIST and then REMOVE data files.

list @sales;
remove @sales;

However, you must also amend the existing load routine to automatically remove data files once successfully loaded.

The following SQL shows how this can be achieved:

put file://downloads/sales.csv
    @sales_stage;

copy into sales
from @sales_stage
   purge = true;

The above SQL command allows automatic removal of data files once loaded, which avoids unnecessary storage costs.

4.2: Identify and Remove Old Clones

One customer I worked with saved over $50,000 per year by simply dropping zero copy clones that were no longer needed.

One of the misconceptions about clones is that while they are initially zero additional storage, they will eventually increase storage costs.

How Cloning Works

The diagram below illustrates how when a table is cloned, the clones don’t add any additional storage, and indeed the actual cloning operation is a cloud services action and therefore almost certainly free.

However, over time, as the data in the source table is modified, new versions of micro-partitions are created and the old versions kept for time-travel. This is illustrated in the diagram below whereby an update has modified all rows.

The problem now, is because of the pointers from the clone, the time-travel data cannot be physically deleted, and now the original data storage has doubled.

In one case, I worked with a customer who had cloned entire databases to produce test and development copies, but these were never removed afterwards. As a result the storage costs ballooned over time.

The code below can be used to identify clones which potentially can be removed, although be careful not to drop any legitimate backup copies.

select  m1.table_catalog                as "Source Database"
,       m1.table_schema                 as "Schema"
,       m1.table_name                   as "Table"
,      case
           when m1.active_bytes >= power(2, 40) then to_char(round(m1.active_bytes / power(2, 40), 1)) || 'TB'
           when m1.active_bytes >= power(2, 30) then to_char(round(m1.active_bytes / power(2, 30), 1)) || 'GB'
           when m1.active_bytes >= power(2, 20) then to_char(round(m1.active_bytes / power(2, 20), 1)) || 'MB'
           when m1.active_bytes >= power(2, 10) then to_char(round(m1.active_bytes / power(2, 10), 1)) || 'K'
           else to_char(m1.active_bytes)
        end as "Bytes"
,       m2.table_catalog                as "Target Database"
,       m2.table_schema                 as "Schema"
,       m2.table_name                   as "Table"
,      case
           when m1.retained_for_clone_bytes >= power(2, 40) then to_char(round(m1.retained_for_clone_bytes / power(2, 40), 1)) || 'TB'
           when m1.retained_for_clone_bytes >= power(2, 30) then to_char(round(m1.retained_for_clone_bytes / power(2, 30), 1)) || 'GB'
           when m1.retained_for_clone_bytes >= power(2, 20) then to_char(round(m1.retained_for_clone_bytes / power(2, 20), 1)) || 'MB'
           when m1.retained_for_clone_bytes >= power(2, 10) then to_char(round(m1.retained_for_clone_bytes / power(2, 10), 1)) || 'K'
           else to_char(m1.retained_for_clone_bytes)
        end as "Clone Bytes"      
,       datediff('days',m1.table_created, current_date()) as "Cloned Days Ago"
from snowflake.account_usage.table_storage_metrics m1
,    snowflake.account_usage.table_storage_metrics m2
where m1.id = m2.clone_group_id
and   m1.id <> m2.id
and   m1.retained_for_clone_bytes > 0
and   m1.table_catalog = 'SALES'
and   m1.deleted = FALSE
and   m2.deleted = FALSE;

4.3: Limit the RETENTION_PERIOD_IN_DAYS

Every time a query updates a table, a new version of the micro-partition is created, and the data retained based upon the DATA_RETENTION_TIME_IN_DAYS which should be set to around 7 days for most cases.

However, I worked with one customer who kept the retention to 90 days (the maximum value) for every table in the entire account which meant they were holding on to nearly two petabytes of data where 90% of the data was retained for time_travel.

Simply setting the DATA_RETENTION_TIME_IN_DAYS to a lower figure saved over $550,000 per year.

SELECT 
    t.TABLE_CATALOG AS DATABASE_NAME,
    t.TABLE_SCHEMA AS SCHEMA_NAME,
    t.TABLE_NAME,
    t.RETENTION_TIME,
    ROUND (s.ACTIVE_BYTES/1024/2024) AS ACTIVE_BYTES,
    ROUND (s.TIME_TRAVEL_BYTES/1024/2024) AS TIME_TRAVEL_BYTES,
    ROUND((s.ACTIVE_BYTES + 
           s.TIME_TRAVEL_BYTES + 
           s.FAILSAFE_BYTES + 
           s.RETAINED_FOR_CLONE_BYTES) / 1024 / 1024) AS TOTAL_BILLABLE_MB,
    t.RETENTION_TIME
FROM 
    SNOWFLAKE.ACCOUNT_USAGE.TABLE_STORAGE_METRICS s
JOIN 
    SNOWFLAKE.ACCOUNT_USAGE.TABLES t
    ON s.TABLE_CATALOG = t.TABLE_CATALOG
    AND s.TABLE_SCHEMA = t.TABLE_SCHEMA
    AND s.TABLE_NAME = t.TABLE_NAME
WHERE 
    t.RETENTION_TIME > 7
ORDER BY 
    TOTAL_BILLABLE_MB DESC;

The following query can be used to set the value at the table level.

ALTER DATABASE sales
SET DATA_RETENTION_TIME_IN_DAYS = 7;

In terms of best practices:

• Ideally avoid setting the DATA_RETENTION_TIME_IN_DAYS at table level as there is a risk of having two or more tables with different retention periods in the same schema. This means recovery is limited by the minimum retention period if data is to be recovered consistently.

• Instead set the value at the DATABASE or SCHEMA level which ensures table recover times are consistent

• Finally, set the DATA_RETENTION_TIME_IN_DAYS to 1 day for schemas holding tables which are frequently deleted and reloaded. This tables can explode the data retention, but equally can be quickly recovered by re-processing the data.

4.4: Use TRANSIENT tables

By default, every table holds on to 7 days of versioned micro-partitions when the data is released from TIME TRAVEL. However, in some cases, (for example when landing table into Snowflake), data is purely transient in nature and deleted and re-inserted. This can cause huge storage costs from FAILSAFE data.

The following query can be used to identify PERMANENT tables with multiple DML operations.

WITH dml_activity AS (
    SELECT 
        OBJECT_CATALOG,
        OBJECT_SCHEMA,
        OBJECT_NAME,
        COUNT(*) AS DML_COUNT
    FROM 
        SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY
    WHERE 
        START_TIME >= DATEADD(DAY, -7, CURRENT_TIMESTAMP)
        AND QUERY_TYPE IN ('INSERT', 'UPDATE', 'DELETE', 'MERGE')
        AND OBJECT_NAME IS NOT NULL
    GROUP BY 
        OBJECT_CATALOG, OBJECT_SCHEMA, OBJECT_NAME
),
aggregated_dml AS (
    SELECT 
        OBJECT_CATALOG,
        OBJECT_SCHEMA,
        OBJECT_NAME,
        SUM(DML_COUNT) AS TOTAL_DML_COUNT
    FROM 
        dml_activity
    GROUP BY 
        OBJECT_CATALOG, OBJECT_SCHEMA, OBJECT_NAME
)
SELECT 
    s.TABLE_CATALOG AS DATABASE_NAME,
    s.TABLE_SCHEMA AS SCHEMA_NAME,
    s.TABLE_NAME,
    ROUND(s.ACTIVE_BYTES / 1024 / 1024) AS ACTIVE_MB,
    ROUND(s.TIME_TRAVEL_BYTES / 1024 / 1024) AS TIME_TRAVEL_MB,
    ROUND(s.FAILSAFE_BYTES / 1024 / 1024) AS FAILSAFE_MB,
    ROUND(s.RETAINED_FOR_CLONE_BYTES / 1024 / 1024) AS RETAINED_FOR_CLONE_MB,
    ROUND((s.ACTIVE_BYTES + s.TIME_TRAVEL_BYTES + s.FAILSAFE_BYTES + s.RETAINED_FOR_CLONE_BYTES) / 1024 / 1024) AS TOTAL_BILLABLE_MB,
    d.TOTAL_DML_COUNT
FROM 
    SNOWFLAKE.ACCOUNT_USAGE.TABLE_STORAGE_METRICS s
JOIN 
    aggregated_dml d
    ON s.TABLE_CATALOG = d.OBJECT_CATALOG
    AND s.TABLE_SCHEMA = d.OBJECT_SCHEMA
    AND s.TABLE_NAME = d.OBJECT_NAME
JOIN 
    SNOWFLAKE.ACCOUNT_USAGE.TABLES t
    ON s.TABLE_CATALOG = t.TABLE_CATALOG
    AND s.TABLE_SCHEMA = t.TABLE_SCHEMA
    AND s.TABLE_NAME = t.TABLE_NAME
WHERE 
    t.TABLE_TYPE != 'TRANSIENT'
ORDER BY 
    d.TOTAL_DML_COUNT DESC;

If the tables reported don’t need failsafe recovery, convert them to TRANSIENT tables using:

-- Step 1: Create the transient table with the same structure
CREATE OR REPLACE TRANSIENT TABLE my_schema.my_table_transient LIKE my_schema.my_table;

-- Step 2: Copy the data
INSERT INTO my_schema.my_table_transient
SELECT * FROM my_schema.my_table;

-- Optional Step 3: Drop or rename the original table
-- DROP TABLE my_schema.my_table;
-- or
-- ALTER TABLE my_schema.my_table RENAME TO my_table_backup;
-- ALTER TABLE my_schema.my_table_transient RENAME TO my_table;

Note: You cannot directly convert a PERMANENT to a TRANSIENT table, but the above methods can be used instead.

5. How can I tune Snowflake Data Loading?

When you consider that a single Snowflake account can hold terabytes of data, consider also that nearly every row must first be loaded. This section discusses the problems faced by many customers loading data.

5.1: COPY using a Single XSMALL Virtual Warehouse

The Snowflake COPY operation loads data files into a Snowflake table. However, each file loaded uses only a single CPU, however many Snowflake customers load using the same virtual warehouse for transformation processing (typically a MEDIUM or LARGE warehouse).

The diagram below illustrates the problem:

The above diagram illustrates that loading a single file on a MEDIUM size warehouse uses only one of the 32 CPUs with a potential waste of 97% of capacity. Furthermore since around 80% of data loads are relatively small (under 100MB in size), there is a relatively large overhead per load to find and fetch the data file from remote storage.

The solution is illustrated below:

Using the deployment above, instead of each COPY running on the same virtual warehouse as the transformation operation, every COPY in the system uses a single shared XSMALL virtual warehouse. Using the following configuration ensures the warehouse will automatically scale out to load up to 80 files in parallel, but match the cost to the load rate.

create or replace warehouse load_warehouse with
   warehouse_size = XSMALL
   min_cluster_count = 1
   max_cluster_count = 10
   auto_suspend = 60;

Note: The actual load operation MUST run every COPY statement in a new session to maximize parallel operations. Using this method with one customer we successfully loaded 320TBs of data in just three days.

5.2: Avoid loading tiny files using Snowpipe

One customer I worked with in the mobile phone industry was loading 100,000s of files per day whereby each file was around 80 bytes in size. They complained that the cost of loading was too high and assumed it was because of the credit charge per 1,000 files.

However, the problem was more subtle. Consider the diagram below which illustrates the impact of file size on both the load rate and cost per megabyte.

When loading a tiny file (eg. 80 bytes), although the actual load may be sub-second there’s a tiny overhead to identify and fetch the file from remote storage. This means when loading thousands of tiny files the actual load rate is very slow.

However, increasing the file size towards 100MB increases the load rate and therefore the cost per megabyte drops quickly. Benchmark tests loading CSV files indicate this load rate increases much more slowly after 100MB which explains the Snowflake standard of 100-250MB.

The customer was using KAFKA to receive the files and write them to storage which triggered Snowpipe to load the files. They simply adjusted the Kafka configuration to wait for up to 60 seconds and write the data file when either the file size reached 100MB or 60 seconds - whichever came first.

As a result their data loading cost dropped considerably - for very little effort. While this is a relatively unusual case, it’s worth understanding as it provides useful insights into the internal workings of Snowflake.

They could of course switch from using Snowpipe to Snowpipe Streaming which is both much faster and doesn’t rely upon files at all, but this was not available at the time.

6. What Application Design Changes Reduce Snowflake Costs?

Although these are often the hardest to implement (especially for applications already running in production), they can often deliver the most effective reductions in cost. It’s therefore worth knowing these design techniques before the solution is built as it will reduce costs beforehand.

6.1: Batch MERGE or UPDATE statements

The diagram below illustrates what happens when a single row is updated in a large table.

Because every micro-partition is immutable, Snowflake needs to replicate the entire micro-partition to apply the change saving the old micro partition for time travel and failsafe. Repeating and committing the same process for multiple rows can lead to an explosion of data storage.

Where possible, try to avoid single-row operations and batch operations together. This means there’s more likely to be multiple rows updated in the same micro-partition reducing the storage in addition to reducing the overall processing cost as fewer micro-partitions are duplicated.

6.2: Process Changed Data

One customer I worked with loaded the entire history of every transaction ever recorded and transformed and aggregated the data every four hours because the data was delivered as a sequence of audit trail changes.

While this worked initially, eventually the data volume processed and compute processing required meant the entire operation was incredibly expensive.

The solution was to load a MASTER table with the latest state, and then use change data capture techniques to identify and apply the changes. As a result, the incremental cost was reduced by 98% and performance improved by orders of magnitude.

6.3: Be Aware of Data Distribution

One customer I worked with held the entire history of transactions in a single table which amounted to 100s of terabytes of data with additional inserts and changes every hour. However, because the data was clustered by date, (and 99% of changes were for the past 7 days), their system experienced great performance and low cost.

The diagram below illustrates why.

The above diagram shows that although the table had over 20 years of history, the updates impacted a relatively small number of micro-partitions. Typically a batch of updates took less than nine minute to complete.

However, the same customer faced a massive problem with another huge table where update operations took hours to complete. In fact a single, relatively simple update statement took just under four hours to complete.

The diagram below illustrates why.

In this case, instead of the update operation impacting a few micro-partitions, it affected a few rows, but these were distributed across thousands of micro-partitions. As a result, the entire table was duplicated with old versions held in time-travel and this led to very high compute costs in addition to storage.

The screenshot above shows the impact of the operations whereby an operation took nearly four hours compared to eight minutes.

There’s no simple solution to this kind of problem. If it’s possible to cluster the data to ensure all entries are physically stored together (for example an update against a single customer could be improved by clustering the data by CUSTOMER_ID), however this wasn’t an option in this case.

The only solution was to batch updates to multiple customers at the same time to help amortize the cost lo

Common Snowflake Cost Mistakes — and What to Do Instead

• Skipping cost analysis before tuning

  Don’t : Jump straight into query tweaks without knowing your top cost drivers.

  Do this instead: Use Snowsight, WAREHOUSE_METERING_HISTORY, and STORAGE_USAGE views to find the biggest spend categories first.

• Oversizing warehouses

  Don’t : Run XL/2XL warehouses “just in case.”

  Do this instead: Start small (S or M), monitor performance, and scale up or use multi-cluster auto-scaling only when needed.

• Ignoring Auto-Suspend settings

  Don’t : Leave warehouses running with no activity.

  Do this instead: Set AUTO_SUSPEND to 1–5 minutes and rely on AUTO_RESUME for responsiveness.

• Relying on manual scaling only

  Don’t : Keep a single cluster active during high-concurrency periods.

  Do this instead: Enable multi-cluster auto-scaling to handle spikes and then drop back to minimum clusters.

• Overusing serverless tasks for heavy workloads

  Don’t : Run large joins or frequent ETL entirely on serverless compute.

  Do this instead: Reserve serverless for light, infrequent jobs; run heavy, regular processing on warehouses.

• Neglecting storage housekeeping

  Don’t : Keep old tables, unused stages, and 90-day Time Travel by default.

  Do this instead: Drop obsolete objects, clear staged files, and use transient tables with minimal Time Travel where possible.

• Misusing clustering keys

  Don’t : Add clustering to every large table without cost checks.

  Do this instead: Use SYSTEM$ESTIMATE_AUTOMATIC_CLUSTERING_COSTS to measure benefit vs. maintenance expense.

• Forgetting about data transfer costs

  Don’t : Assume replication is “just storage.”

  Do this instead: Factor in cross-region/cloud data transfer fees when designing replication and sharing strategies.

• Not monitoring Cloud Services usage

• Don’t : Ignore metadata/compilation costs until they appear on the bill.

  Do this instead: Track Cloud Services credits daily and optimise where usage regularly exceeds the 10% threshold.

• Failing to set governance policies

  Don’t : Allow uncontrolled warehouse creation and no spend alerts.

  Do this instead: Enforce role-based provisioning, set budgets, and configure usage notifications in Snowsight or via alerts.

❓ Frequently Asked Questions on Snowflake Cost Optimisation

Q1: What’s the quickest way to cut Snowflake costs?

The fastest win is to right-size your virtual warehouses and set aggressive AUTO_SUSPEND times (1–5 minutes) to avoid idle compute charges. Use Snowsight or WAREHOUSE_METERING_HISTORY to identify oversized warehouses.

---

Q2: How does Snowflake bill compute usage?

Compute is billed per second, with a 60-second minimum for each warehouse resume or serverless job start. This means even very short bursts are rounded up to one minute for billing.

---

Q3: What is the Cloud Services “10% rule”?

Cloud Services credits (metadata, query compilation, governance features) are only billed if daily usage exceeds 10% of that day’s warehouse compute usage. Serverless compute does not contribute to this 10% baseline.

---

Q4: Should I use serverless tasks or warehouses for scheduled jobs?

• Use serverless for light, infrequent jobs where you don’t want to keep a warehouse running.

• Use warehouses for heavy or frequent jobs — they’re usually cheaper for sustained workloads.

---

Q5: How can I reduce Snowflake storage costs?

Delete unused tables and stages, shorten Time Travel retention where possible, and use transient or temporary tables for intermediate data to avoid Fail-safe storage charges.

---

Q6: How do I monitor Snowflake costs in real time?

• Use Snowsight dashboards and ACCOUNT_USAGE views for daily tracking.

• Set Resource Monitors to send alerts when usage hits budget thresholds.

---

Q7: What’s the impact of data replication on cost?

Replication incurs both compute (on the target account) and data transfer fees (especially cross-region or cross-cloud). Always factor these into your cost planning.

---

Q8: Do clustering keys always save money?

No. Clustering can improve query speed but adds ongoing maintenance cost. Always test using SYSTEM$ESTIMATE_AUTOMATIC_CLUSTERING_COSTS before implementing.