Cost Optimizer for Snowflake

Introduction
There are several risk factors involved with determining cost optimization in Snowflake, and as a result, clients may have concerns about whether those risks are worth the reward. The common concerns we hear include the high initial cost and the necessary experimentation with Snowflake’s platform for performance and recommendations on design decisions.

Here are a few of the most frequently asked questions that our team receives from new, Snowflake clients in their first-year journey:

"We followed the best practices recommended by Snowflake in multiple webinars, blogs, and documentation. They do work well, but they come with a huge credit cost.
- What really went wrong?
- How can optimal performance be achieved at a justified cost?
- What clustering keys should be used?
- What’s the right size of the warehouse to be used?"

There are strong query performance optimization methods available in Snowflake including materialized view, clustering, and search optimization. Each optimization method is designed for different scenarios, and if it is used in the wrong scenario, this can lead to an unexpected result. Additionally, selecting the wrong choice of optimization method can lead to higher costs.

NTT DATA understands these questions and concerns, and as a result, we were motivated to build our “Cost Optimizer for Snowflake”.

The "Cost Optimizer for Snowflake" App Analyses, scrutinizes and optimizes following Snowflake cost areas across the platform:
- Managed compute warehouse cost
- Storage cost
- Server less compute including:
- Auto-clustering
- Materialized Views
- Pipes

Following sections briefly describes each section in the app.

Overview
The overview section highlights summary of credit usage at organization level as well as at account level which includes:
- Weekly cost in various snowflake accounts within the organization and how it is different from previous week
- Segregation of cost by service types at organization and account level
- Most frequent users and Databases used
- Top costly queries and their cost and frequency

This section is helpful to prioritize the cost optimization efforts.

Warehouse Utilization
The warehouse utilization section takes deep dive into the warehouse utilization. This includes:
- Unutilized but billed warehouse time and possible saving
- Bytes spilled to local/remote disks and possibility of upscaling warehouses
- Utilization of data cache
- Queue load on warehouses and possibility of use of multi-cluster warehouses

Auto Clustering
This section investigates the clustered tables and tables which possibly benefit from clustering along with the recommendation on clustering key. This section inspects the query log to identify the best cluster key. This section includes capabilities including:
- Analysis on how frequently table is re-clustered and how frequently it is queried
- Potential savings if auto-reclustering is removed from less frequently queried but frequently updated table
- Analysis of if columns used in joins and where clause are other than cluster key
- Recommendation on cluster key

Materialized Views
This section investigates materialized views, their cost, frequency of use and frequency of refresh. This section also gives recommendations about following:
- Potential saving if less frequently used but frequently refreshed materialized view are replaced by standard views/static tables
- Potential candidate views for materialized view

Snowpipe
This section analyses the snowpipe usage to identify average file size, number of files etc. This section also recommends following:
- File sizes
- Potential savings

Storage
This section investigates storage uses and identifies unused storage in the account. This section also recommends following:
- Candidate tables for drop
- Potential saving on storage if table is dropped

Settings
This section provides flexibility to change the frequency of execution of cost optimizer in the background. All cost optimizers will be executed on the selected schedule to minimize the manual efforts.