Optimizing Snowflake (6) — Cost Optimization using 3rd party services
When you want to analyse the Snowflake credit usage using readymade apps, there are several apps, either native or streamlit, published by 3rd parties in the Dataproducts/Marketplace section. When doing a search on cost optimization, several services are displayed.
These are the ones available, seems that many news ones are showing up regularly: NTT, KPI Partners, Snoptimizer, Watchkeeper, Dataradar, Infostrux, InstaQuery, Select.
In this article, we will explore the possibilities of the free NTT service.
The setup is super easy, ACCOUNTADMIN privileges required. After installation, we go through some configuration options. We need to give the app access to the Snowflake.Account_usage schema (using the IMPORTED PRIVILEGES), plus the app will execute task to refresh the data from the Snowflake schema into the NTT specific schema’s.
It will start initializing:
After that it will present statistics like top n expensive queries in the above displayed categories:
Also several charts are displayed:
Under the warehouse tab some recommendations are given:
- Review the workload and Auto-suspend time to decrease idle time. Following are the potential savings if idle time is reduced to 20%
- Scale-up warehouses with higher spillage to local storage to improve query performance
- Re-group similar workloads on same warehouse and review Auto-suspend time to improve query performance
- No Bytes spilled to remote storage! You have a room to scale-down your warehouses to reduce cost
When leaving the computer, after a while this shows up:
This is the typical Streamlit wake-up visual. So, let’s check which grants are given to the Snowflake database:
This is the command from the query history which is executed:
execute streamlit "COST_OPTIMIZER_FOR_SNOWFLAKE"."APP_SCHEMA"."HOME"()The assigned privileges are also visible in the query history. What we can see here is that the commands are executed under the currently logged in user. Therefore, it is highly recommended to create a dedicated account for the installation and usage of this app.
Let’s have a look at what happens when we disable/remove the app:
This is the query that is executed:
drop APPLICATION IDENTIFIER('"COST_OPTIMIZER_FOR_SNOWFLAKE"')In the Snowflake documentation, extensive instructions are published on how to uninstall apps:
To conclude:
- very easy install
- some recommendations but not very extensive
- seems to be slightly similar to the Snowflake out-of-the-box features
- also easy uninstall
A recommendation when you want to use this in your company environment: install it on a separate Snowflake account first, with a dedicated Snowflake account for optimal tracing.
If you do not have multiple accounts within your company, set it up within a trial. Also, prepare a proper log on the grants that are given and the grants that are cleaned.