Author: Twinkle Viswanathan
Ever wondered what contributed a huge sum to your Snowflake bill? Monitoring costs in Snowflake could be quite a hassle. In this blog, we'll discuss the cost-incurring factors in Snowflake and how we can monitor and control costs in our Snowflake environment.
Costs In Snowflake
On a high level, costs in Snowflake can be categorized into compute cost and storage cost. To read in detail about storage costs and how we can monitor them, we suggest you read this blog on storage monitors. Now, let's look into how costs are calculated in Snowflake.
All Snowflake costs are based on the usage of data storage, virtual warehouses (compute) resources, cloud services, serverless features, and data transfer.
Usage for virtual warehouses (compute) resources is calculated based on the number of Snowflake credits consumed by virtual warehouses for executing queries, loading/unloading data, and performing other DML operations. The warehouse's size and duration it runs affect the number of credits utilized. Choosing the right warehouse size depending on the load and setting auto-suspend and auto-resume at appropriate intervals helps us to optimize warehouse credit usage. Setting up resource monitors for warehouses is a good practice, as it helps us monitor our warehouse usage and set thresholds to get notifications to prevent overuse. To know more about resource monitors, read our blog on resource monitors.
Cloud services usage is charged only if the everyday use of cloud services for the account exceeds 10% of the everyday usage of the compute resources. The charge is calculated every day (UTC time zone). This ensures that the 10% adjustment is accurately applied each day at the credit price for that day.
The serverless features of Snowflake include AUTOMATIC_CLUSTERING, SEARCH_OPTIMIZATION_SERVICE, MATERIALIZED_VIEW_MAINTENANCE, REPLICATION, and SNOWPIPE. Charges for these features are calculated based on the total usage of the resources.
A per-byte fee is charged when users transfer data from one Snowflake account (hosted on AWS, Google Cloud Platform, or Microsoft Azure) into cloud storage in another region on the same cloud platform or into cloud storage in another cloud platform.
Factors That Influence Cost
Now that we've seen what factors contribute to Snowflake cost let's discuss the factors that influence Snowflake cost.
Region
Cloud Provider
Edition of Snowflake account (Influences compute cost)
Type of storage (On-demand or capacity)
So choosing the right region, cloud provider and edition of the Snowflake account that fulfills our requirements and fits our budget could be crucial.
Snowflake Provided Resource Usage Information
Snowflake gives insights into the number of credits used or the amount of storage used, but it doesn't provide direct data on the total cost incurred from these resources. Moreover, there's no one-stop solution to get the combined costs together. Now let's look at where we can find all the resource utilization information.
WAREHOUSE_METERING_HISTORY (information schema and account usage schema). In WAREHOUSE_METERING_HISTORY we'd get information about the credits used by warehouses and cloud services. Even though we get information about credits used in a warehouse, it doesn't provide credit usage information on a role or user level. To overcome this, we can get query running time and user and role information from QUERY_HISTORY and calculate the costs on our own.
select user_name, count(*),
sum(total_elapsed_time/1000 *
case warehouse_size
when 'X-Small' then 1/60/60
when 'Small' then 2/60/60
when 'Medium' then 4/60/60
when 'Large' then 8/60/60
when 'X-Large' then 16/60/60
when '2X-Large' then 32/60/60
when '3X-Large' then 64/60/60
when '4X-Large' then 128/60/60
else 0
end) as estimated_credits
from snowflake.account_usage.query_history
group by user_name
order by 3 desc
limit 10;
Try this sample query to get user-wise credit usage, and we can multiply it with the cost per credit to get your user-wise credit usage cost.
Thus we can get information on all cost-incurring factors from different system views in the Account Usage schema or table functions in Information_schema.
Way Forward
From all this information, we can build dashboards with custom filters for users, roles, warehouses, or time periods either in Snow Sight for free or in any other reporting tool to monitor all Snowflake costs in one place.
Now that we have built dashboards to monitor costs in Snowflake, we can build alerts and notifications whenever a particular threshold has been reached. We can send a notification through email/Slack to different groups of people.
You might also want to look at our other blogs on Snowflake monitoring
To know more about implementing cost monitoring for your Snowflake account and kipi.bi's all-in-one Snowflake monitoring solution, Mastiff; contact us at mastiff@kipi.bi.
References:
https://www.analytics.today/blog/monitoring-snowflake-usage-and-costs
https://www.linkedin.com/pulse/reporting-snowflake-part-2-sample-cost-usage-report-krzysztofek/