Snow Everything About Your Warehouse
As many companies move to a subscription model, one of the most important tools to provide for customers is a way to justify that their subscription is still valuable. To do so, many SaaS companies offer access to usage data to allow users to easily answer questions about the value they are getting from a product. In a world where there are so many vendor options, giving users the ability to explore the relevant data and justify their investment is even more powerful (if you’re a data geek like me 🤓).
A recent blog post by our customer GitLab inspired us to open source analytics about our Snowflake usage. We built a few different dashboards to investigate use cases for the data that Snowflake offers in its ACCOUNT_USAGE schema, which holds all of the usage data they collect. All of the queries and dashboards are available in the GitLab repo here.
Step 0: Granting access to the tables
Before getting started, you’ll need to grant access to the SNOWFLAKE metadata schema where the ACCOUNT_USAGE tables are. To do so, you can run the following commands as a query in the Snowflake GUI:
use role accountadmin;
grant imported privileges on database snowflake to role sysadmin;
grant imported privileges on database snowflake to role [YOUR_ROLE_NAME];
use role [YOUR_ROLE_NAME];
What’s in my warehouse?
Our first use case answers one of the most common questions that people have for their warehouse: what’s in here and how much space is it taking up? Snowflake provides this information in the STORAGE_USAGE tables. Some of the most critical questions, such as how storage is growing over time, the quantity of objects, and other key ways to slice this data are available in the Snowflake Warehouse Contents dashboard.
From our work with Periscope Data by Sisense customers, one of the key aspects to keeping a data tool useful to end users is to avoid getting bloat within the warehouse, dashboards, or any other data related object. Organizing the tables will allow users to easily discover them when they are needed. Periscope Data by Sisense also offers our schema browser in the editor to allow for faster searching, but it’s still good to not have stale schema or duplicate tables that confuse users.
How is my user experience?
A top priority for the Periscope Data by Sisense customer success team is to monitor query performance. Many data analysts and scientists understand that complex logic may take a while to execute against a database, but a business user expects data to be returned extremely quickly. Monitor trends in query performance to optimize user experience, including ad hoc analytics and business intelligence created on Snowflake, the data warehouse built for the cloud.
In the Snowflake usage tables, we can use QUERY_HISTORY to understand the total amount of time users are waiting for data. Further, we can narrow the type of queries to the actual experience by limiting the search to commands for data retrieval from the data warehouse. If you’ve ever typed SQL, you’ll be familiar with our favorite way to access data, SELECT!
query_type = 'SELECT'
How much am I paying?
At the end of the day, the value that Snowflake brings to a company needs to be weighed against the cost. Snowflake makes this analysis easy using its WAREHOUSE_METERING_HISTORY tables. The main ways that Periscope Data by Sisense and its customers attempts to perform cost control is by identifying the databases, users, or other actionable units where a high volume of credits are being used.
You can find this analysis in the Snowflake Metering Analysis. To get the analytics up and running, the GitLab repo has a placeholder SQL Snippet for the cost per credit.
If you want access to these dashboards (and the fastest time to insight on your Snowflake instance), you can do one of the following:
Existing Customers: As mentioned, we are excited to be open sourcing analytics with our customer GitLab. If you have a git repo connected to your Periscope Data by Sisense site, you can copy the files from this repo into your git repo and then merge changes to the master branch. If you do not have git access, you can contact your customer success manager, who will help you access to these dashboards!
New Customers: For more information about getting started with Periscope Data by Sisense and Snowflake, visit our partners page for more details. See Snowflake walk through the simple setup process for tools in Partner Connect in the video below.