Founded in 2014 by three leading cloud engineers, phData focuses on solving real-world data engineering, operations, and advanced analytics problems with the best cloud platforms and products.
Over the years, one of our primary focuses became Snowflake and migrating customers to this leading cloud data platform. While setting up accounts, we developed and shared best practices for working within Snowflake to optimize efficiency and performance. This desire to share our knowledge of Snowflake led us to create the Advisor Tool, which is part of the phData Toolkit.
In this blog, we’ll explore the phData Toolkit, why the Advisor Tool is an integral part of it, and the top 5 use cases for the Advisor Tool.
What is the phData Toolkit?
While working on many data engineering projects, phData found patterns of issues that would come up regularly when migrating clients’ data. Instead of reinventing the wheel with each new project, we built reusable tools to help speed up our engagements and provide a better service.
This evolved into the phData Toolkit, a collection of high-quality data applications to help you migrate, validate, optimize, and secure your data. The Toolkit offers tools that range in capabilities. The Toolkit is an automation powerhouse; from translating SQL from one dialect to another to identifying opportunities to make your Snowflake Data Cloud account more efficient and secure, there’s a lot of automation to unpack.
What is the Advisor Tool?
Our Snowflake Advisor is a software tool designed to help optimize your use of the Snowflake Data Platform. With the Advisor tool, you can quickly and easily identify opportunities to improve the configuration, security, performance, and efficiency of your Snowflake environment.
We have worked closely with our Snowflake data and operations experts to ensure that our Snowflake Advisor delivers actionable recommendations based on best practices and phData’s vast real-world experience on the Snowflake Platform.
The Advisor tool comes with a pre-configured set of checks to identify opportunities and provide recommendations that ensure your Snowflake environment is following best practices and industry standards across a variety of categories, including:
Configuration Best Practices: Recommendations for your Snowflake environment configuration based on best practices and industry standards.
Security Enhancement: Identify and fix potential security risks in your account and maintain a compliant security posture going forward.
Operational Risks: Uncover operational risks such as data loss or failures in the event of an unforeseen outage or disaster.
Performance Optimization: Locate and fix bottlenecks in your data pipelines so that you can get the most out of your Snowflake investment.
Resource Utilization: Optimize the use of resources, such as computing and storage, to ensure that you are not overpaying for resources you don’t need.
Environment Scale and Capacity: View critical metrics about the scale and features used in your account to help you understand your current state and plan for growth.
Top Use Cases for the Advisor Tool
Account Configuration
As a modern cloud-based data platform, Snowflake contains many features and continues adding account configuration features. Whether you’re new to Snowflake or have had Snowflake for years, you may not know what some of these features are or that they even exist. Furthermore, determining the best way to set these configurations for your account can be difficult without working on them.
This is where the Advisor Tool comes into play.
The Advisor tool will run a list of checks against Snowflake account parameters that will compare each value with a best practice. Each check will either pass or fail. The Advisor tool displays whether or not the checks passed or failed and provides a detailed summary of each parameter for users to understand better what it does.
For example, this output from the Advisor tool shows this account failed in several categories, including the ABORT_DETACHED_QUERY check, but passed in others:
If a user doesn’t understand what a parameter does, clicking the check allows them to drill down into the configuration and see details along with what needs to be done to allow it to pass.
For example, if a user doesn’t know what ABORT_DETACHED_QUERY means, they can drill down to see the description and a link to Snowflake documentation for more information:
Operational Risks
Within your Snowflake account, there are many things that can break on a daily basis. Pipes, streams, materialized views, and tasks are among these processes that need to be monitored to ensure they are working properly.
The Advisor tool can perform checks on all of these to quickly display their status. For example, here we see this account hasn’t had any pipe errors in the last 30 days but does have some stale streams:
As with the account configurations, if a user doesn’t understand what a stale stream is, drilling down will reveal a description and the best course of action to correct the issue:
With the Advisor tool, you know where all your Snowflake processes stand and can take action to fix them quickly.
Performance Optimization and Resource Allocation
Another good use case of the Advisor tool is checking for performance and resource optimization opportunities.
The tool can provide information on your warehouses, such as:
Queueing stats
Spillage to local and/or remote disk
Idle warehouses
Oversized warehouses
In regards to storage objects, the Advisor tool provides the following:
Storage used by database
Duplicate tables
Unused tables
Storage trends
Here’s an example of the graph the Advisor tool presents to display the oversized warehouses:
With these stats at your fingertips, Snowflake users can determine warehouses that need resizing or deletion and storage objects that can be reworked to increase performance and save costs.
Security
Security is paramount to every data warehouse, but it is even more important for warehouses based on the cloud. Luckily, Snowflake prides itself on the security features it provides to all customers. However, even with these features, customers can leave themselves vulnerable if they’re not careful.
The Advisor tool has many security checks. From minor ones, like users using only simple password authentication, to major ones, such as attempted logins blocked by a network policy.
Using the Tags function of the Advisor tool here, we can see all the checks being performed:
Other checks the Advisor tool performs around security:
Grants made to roles that shouldn’t receive extra permissions (i.e.
ACCOUNTADMIN,PUBLIC)Periodic re-keying/client-side encryption key strength
Schemas without managed access
Queries performed by
ACCOUNTADMINNumber of users with
ACCOUNTADMINroleNetwork Policy enablement
Costs
Managing costs is a priority for all businesses using any cloud-based tool such as Snowflake. With so many moving parts, this can be difficult. However, the Advisor tool can identify where the credit usage is coming from in your account.
The tool outputs many checks related to cost, including:
Queries using the most credits
Snowpipe credit usage
Warehouse credit usage
Oversized warehouses
Warehouse spillage
Compute credits used by external tables
Automatic clustering
Query optimization credit usage
Much of this information has been available over the last 30 days, which can give me even more insight into what’s using up credits.
For example, here’s the Advisor tool’s graphic output of warehouse credit usage by day:
Closing
phData is committed to using our vast knowledge of Snowflake best practices to improve the Advisor Tool and all the tools in the Toolkit to increase the efficiency of our engineers and our clients’ engineers. With the Advisor tool, anyone can configure their Snowflake account to be efficient, performant, and cost-effective.
If you have any additional questions regarding phData’s Advisor Tool, reach out to our team of experts today!
