How to Estimate Snowflake Cost?

In the last couple of years, Snowflake has gained a lot of popularity due to its pay-as-you-go pricing structure. It is indeed a pocket-friendly model BUT only if you know what you are doing. Otherwise, your bill can skyrocket in no time. This is why it’s critical to understand how Snowflake cost is calculated before setting up your warehouse. In this article, we will discuss everything you should know to use Snowflake efficiently and cost-effectively. 

Want a quick estimate before getting on a call? Run the numbers yourself first.

Your total bill of using Snowflake comprises of 3 types of costs:

• compute costs
• storage costs
• data transfer costs

Which of these costs will I incur? Well, it depends solely on the type of tasks you perform. The architecture of Snowflake is designed to divide the cost based on the resources used to complete it. Let’s dive a little deeper to understand these costs.

Snowflake Compute Cost

The cost of compute is based on the consumption of Snowflake credits, the fundamental unit of compute cost measurement. The number of credits consumed depends on the size of the resource and the duration of its operation. There are three types of compute resources within Snowflake (virtual warehouse, serverless, and cloud services) and each one of them consume a specific number of credits per second.

Compute Cost = Number of Credits Used ∗ Price of Each Credit

Virtual Warehouses

Standard virtual warehouses are most common compute resource. They come in several sizes, starting with x-small (1 credit per hour) and going all the way up to 6XL (512 credits per hour).

Snowflake also offers Snowpark optimized warehouses for memory-intensive processing with a 16x memory per node in comparison to standard warehouses. The sizes of these warehouses range from medium (6 credits per hour) to 6 XL (768 credits per hour).

The number of credits charged per hour that the warehouse runs doubles as you progress from one size to the next. The good thing about the pricing model is that the cost is calculated only when the warehouses are running. So, if a warehouse runs only for a few minutes, it will be charged only for those minutes rather than the whole hour.

Serverless

Snowflake offers a range of serverless computing features, like automatic clustering, materialized views, and Snowpipe. The cost of these services is based on the total usage of Snowflake-managed compute resources and is calculated in compute hours (per second model). The number of credits consumed per compute hour depend upon the feature you are using. The following table covers all the details to calculate the share of serverless features in your Snowflake cost.

Cloud Services

These services handle a range of tasks within Snowflake, including user authentication, query optimization, and metadata management. It operates on compute instances supplied by the cloud provider and uses Snowflake credits for calculation of cost. However, there is a catch in its pricing model!

Snowflake Storage Cost

The cost of storage is much simpler to estimate as it is charged a flat rate per terabyte of data stored. It is calculated based on the average number of on-disk bytes stored each day and depend on the frequency of data ingestion and retention policies.

For a high-level idea, you can assume that snowflake will charge you around $25/TB for storage used. The actual rate can vary based on your snowflake plan and data policies.

Data Transfer Cost

Snowflake only charges for data egress. This means that you can load data into it for FREE but will have to pay if you transfer the data from Snowflake to an external source.

NOTE: The cloud provider might charge some fees for transferring data to Snowflake.

The Snowflake cost is calculated on a per-byte model and the pricing depends on the region where your account is hosted. If the data is transferred within the same region, it is FREE. However, if you want to move your data into a different region or a different cloud provider, it will come at a cost.

Now, that we have understood why compute expenses are the largest contributor to our Snowflake cost, let’s discuss how we can estimate them. You can use our calculator for that purposes as well.

Query Execution Time Data

The simplest and most obvious method to calculate Snowflake compute cost is to use the QUERY_HISTORY view. It gives you the detailed information about all the queries that ran in your Snowflake account. You can then multiply a query's execution time with the billing rate for the warehouse it ran on to get the cost. Repeat it for all the queries and you will have the total compute cost for your Snowflake infrastructure.

For example, you run a query for 15 minutes on a small size warehouse. A small warehouse costs 2 credits per hour and the price of a credit is $2. The total cost of this query would be $1.

Compute Cost = Execution 
Time per Hour ∗ Credits Rate∗ Price of Credit Compute Cost = 15/60 ∗ 2 ∗ 2 = 1

Based on this calculation, you can use the following SQL query to find the total compute cost of all the queries in the last 30 days by leveraging QUERY_HISTORY view of Snowflake.

WITH warehouse_credits AS (

SELECT

'X-Small' AS warehouse_size, 1 AS credit_multiplier UNION ALL

SELECT 'Small', 2 UNION ALL

SELECT 'Medium', 4 UNION ALL

SELECT 'Large', 8 UNION ALL

SELECT 'X-Large', 16 UNION ALL

SELECT '2X-Large', 32

),

cost_per_credit AS (

SELECT 2.0 AS credit_cost -- Define the cost per credit

)

SELECT

qh.query_id,

qh.warehouse_name,

wc.credit_multiplier * (qh.total_elapsed_time / 3600000) AS estimated_credits_used,

wc.credit_multiplier * (qh.total_elapsed_time / 3600000) * cpc.credit_cost AS estimated_cost

FROM SNOWFLAKE.ACCOUNT_USAG
E.QUERY_HISTORY AS qh

JOIN warehouse_credits AS wc ON qh.warehouse_size = wc.warehouse_size

JOIN cost_per_credit AS cpc ON 1=1 -- Cross join to apply cost multiplier

WHERE qh.start_time >= CURRENT_DATE - INTERVAL '30 days'

ORDER BY qh.start_time DESC;

SAMPLE OUTPUT:

Although it is easy to understand, this approach has a massive pitfall which can affect your calculation of Snowflake cost. WHY? Because Snowflake doesn’t charge for the time a query ran. Instead, it charges for the time a warehouse was running (even if it is idle).

This means if 2 queries run simultaneously for 15 minutes, the cost of compute will be collected for 15 minutes (and not 30). On the other hand, if a query runs for 15 minutes and the warehouse stays idle for 30 seconds before being suspended, the Snowflake cost will be accounted for 15.5 minutes.

To counter this problem and analyze things at a more granular level, it’s best to use the WAREHOUSE_METERING_ HISTORY view. This is the ultimate source of truth for calculating compute cost in Snowflake and here’s how it works.

Warehouse Usage Data

This is a direct and more accurate method to estimate Snowflake cost because it calculates the usage of every virtual warehouse in your account. So, it handles not only the idle time variation but also accounts for concurrency (when multiple tasks are running at the same time). In most cases, concurrency lowers the cost per query.

Snowflake offers all the details about credits consumption of each warehouse through its built-in logs. Using this information, we can easily calculate the compute cost of Snowflake by using this formula.    

Compute Cost = Credits Used * Price of Credit

Based on this, you can use the following SQL query to find the total compute cost of all the warehouses in the last 30 days by leveraging the WAREHOUSE_METERING_ HISTORY view of Snowflake.

SELECT
 warehouse_name,
 SUM(credits_used) AS total_credits,
 SUM(credits_used) * <CREDIT_COST_PER_UNIT> AS estimated_cost
FROM SNOWFLAKE.ACCOUNT_USAG
E.WAREHOUSE_METERING_HIS
TORY
WHERE start_time >= CURRENT_DATE - INTERVAL '30 days'
GROUP BY warehouse_name;

SAMPLE OUTPUT:

It’s true that the usage-based pricing model is convenient but it can be a hassle to track and calculate Snowflake cost. However, you can make predictable estimations by using built-in logs (of warehouse activity) and implementing effective cost monitoring solutions. Hence, you get to enjoy efficient resource utilization while keeping the costs to a minimum.

If you want to estimate the cost of running your monthly load on snowflake, you can probably use this technique for one full round of load (one day’s compute as it would normally be scheduled). Then, you can multiple it by the number of days you expect to run that load in a month.