Snowflake with his advanced storage features, has the ability to store your data in the most efficent way. It makes these changes automatically and set-up your work environment ready to go with the least possibile cost. Even if your data storage is not the only parameter of your costs; it is still really highly important.
Snowflake offers you several options to take your Snowflake experience to another level. Some of these features happen automatically, without user input. Some of them can be as detailed as you want. In this blog we are going to look at some examples and functionalities that Snowflake provides us for the best pricing control features.
Snowflake offers two types of pricing: on-demand and pre-purchased capacity. With on-demand pricing, customers pay only for the storage and compute resources they use, while pre-purchased capacity provides customers with a fixed amount of resources for a specified period of time. This allows customers to have more control over their costs and avoid unexpected fees.
To ensure that customers don’t exceed their capacity limits, Snowflake offers a number of pricing controls. One of the main ways Snowflake controls pricing is through the use of warehouses. A warehouse is a cluster of compute resources that can be scaled up, down or out based on demand. Choosing the right warehouse size you need is one of the most important thing for your Snowflake Pricing Control Features. The best way to choose the right size is to have a try by starting from the smallest one and going up one by one.
Snowflake Auto-Clustering
Auto-Clustering is a feature that Snowflake executes automatically on data. It becomes auto-clustered based on usage pattern and then stored in micro partitions to be able to use easily afterwards. You can suspend and then reactivate the auto-clustering feature for any table you want. Auto-Clustering is a feature that uses Snowflake credits but when you compare it to its pros it will have a good price/performance ratio. We should remind that when we suspend and recativate clustering feature on Snowflake it could take costs and time a bit more than a normal clustering feature since it will have been a lot of time without clustering.
We can set an auto-clustering value in the creation of table but Snowflake does this automatically for us. In general, it uses values like Dates that could have multiple similar values inside the table.
Snowflake doesn’t execute clustering in every possible instance. It will make an operation/value balance before the clustering, and after that result it decides to make auto-clustering or not.
You can always check the auto-clustering state of your table executing the following code:
SHOW TABLES LIKE '<your_table_name>';
as you scroll towards the right on the result, you will see the automatic_clustering column that has the value ON or OFF depending on your table statement.
Auto-Suspend and Auto-Resume
One of the other components that Snowflake could execute in automatic are the auto-suspend and auto-resume functions. These features automatically pause our warehouses when they are not being used and resume them when needed. By default Snowflake has the time before auto-suspend as 10 minutes; in Nimbus Intelligence we set this value as 1 minute. This can help reduce costs by minimizing idle time and ensuring that resources are only being used when necessary.
ALTER WAREHOUSE <wh_name> SET AUTO_SUSPEND = <seconds>
AUTO_RESUME = TRUE | FALSE
as you can see AUTO_SUSPEND takes input as seconds; so if you want to set auto suspend feature to 1 minute, you should enter 60 as a value.
Time-Travel
Time-Travel is one of the features that have additional costs and the ability to use that depends on your Snowflake Edition (you can check it from here). It depends on your usage of them but might really be a friend of you; especially if you are working with big dimensional datas. It takes the period that you want to see the data and make a historical journey. If you have the Snowflake Standart Version you can set this value as 0 or 1 days. In Snowflake Enterprise Version and the versions above, this value can be set up to 90 days for permanent objects.
There are different ways to utilize this function, one of them is to use with specific date:
SELECT * FROM <your_table> AT(TIMESTAMP => 'Tue, 20 Mar 2023 12:00:00 -0700'::timestamp_tz);
the other way is to call with the certain period of time that we want to see our data (the offset is calculated by seconds so this function is going to go show 3 minutes before):
SELECT * FROM <your_table> AT(OFFSET => -60*3);
last but not least you can call it with one of our query id’s that we made, and specify that we want to see the data before that:
SELECT * FROM <your_table> BEFORE(STATEMENT => '01ab1437-0000-8a87-0000-e4d50003c756');
to have a return value from all these queries our data should exist at the time that we want to travel.
Resource Monitors
Resource Monitors are unique features that help us to notice our warehouse overuse. We can set some usage limitations based on specific parameters that we want. For instance; let’s create a Resource Monitor that suspends immediately when we have used half of the credit quota we gave it in a day. To do so we can run this query:
CREATE RESOURCE MONITOR example_rm WITH
CREDIT_QUOTA = 100
FREQUENCY = DAILY
START_TIMESTAMP = IMMEDIATELY
NOTIFY_USERS = ('CANHOBEK', 'CANHOBEK2')
TRIGGERS ON 50 PERCENT DO SUSPEND_IMMEDIATE
We have three trigger actions; DO NOTIFY, DO SUSPEND, DO SUSPEND_IMMEDIATE as we show in our example. The difference between suspend and suspend_immediate is while we are using suspend_immediate the system is going to suspend the warehouse without waiting for that query to end; but in suspend the last query process will be waited to finish and then the warehouse will be suspended. While notify will only send a notify to users without any further action.
Conclusion
As a conclusion; Snowflake offers us several options to detect where we are overusing our credits and tries to make some improvements to share the best working experience with us. Using these options, we can set features such as resource monitors and auto-suspend function which will take our experience to a next level.
If you ever wonder how you can check your Snowflake usage in detail, you can check this link, and decide which type of pricing control features your account requires.
