Set Up Snowflake Monitoring Quickly with Sigma Templates

If you're using Snowflake as your cloud data warehouse (CDW), you know how important it is to monitor usage and how much work is required to build a reliable set of reporting dashboards.  Instead of starting from scratch, Sigma's Snowflake Usage templates give you prebuilt dashboards that reduce your time-to-answers.  These templates have curated the important metrics and visualizations for you, but still let you drill down to the most granular underlying data.  Templates are used to create Sigma workbooks, which means it's easy to make edits, share insights and explore your data in depth without changing the whole workbook to answer a specific question.  And because the templates are built directly on the Snowflake-provided usage views, there is no data engineering required to deploy them.

Using Sigma templates is easy.  First, log into your Sigma organization (or start a Free Trial).  Then, go to the Templates section of Sigma where you’ll simply click on a Snowflake template to launch it, pre-loaded with sample data.  You’ll then be prompted to swap your own data into the template.  Sigma will automatically search your connected cloud data warehouses, looking for the correct tables to swap with.  You can also navigate to the correct table yourself.  Because the Snowflake Usage templates are built on top of the Snowflake-provided views, the swapping feature finds the proper Snowflake tables easily.

Cost Monitoring Template

We'll start with the Snowflake Cost Monitoring template, which answers questions like "How much money is being spent on compute?  Which accounts and warehouses are the most expensive?  How much data is being stored and what does that cost?  Have these costs gone up or down in the last few months?".

Some high-level metrics are shown up top, and the bar charts below detail the top consuming accounts and warehouses.  It's clear that the DEMENG account is responsible for the largest portion of this Snowflake organization's compute cost, so we can filter to include only that account and continue the analysis from there.  The heat map gives us an idea of high-cost time periods during the week, and on the next tab we can see that this consumption has been increasing for the past 6 months.

This template unearths a lot of information about compute cost across a Snowflake organization, and includes similar analysis of storage cost.  Sigma also provides a template that tracks reader account cost for a specific Snowflake account.

Snowflake Performance Monitoring Template

Next up is the Snowflake Performance Monitoring template.  This template will help answer questions about how queries are performing in terms of run time, cache usage and pruning, and help monitor the health of warehouses.

On the first tab, the workbook shows key performance indicators (KPIs) for query performance, a breakdown of the types of queries run, and a histogram of query time.  There is also a table that reports the status of each warehouse, with metrics like cache utilization, average queued time, total GB spilled to storage and more.  In this example, SALES_PROD_WH is the busiest warehouse, running 72% of all queries in this account, but has very low cache utilization.  To get a better understanding of this issue, we'll look at the Caching tab.  

After filtering down to SALES_PROD_WH for the past 30 days, we see that this warehouse has low use of both the result and warehouse caches, which likely means poor performance.  From here, we could launch a deeper analysis to find the root cause of the issue, whether that is too low of an auto-suspend time or a poor choice of warehouse size.  Because this template is just a starting point, we can continue exploring in this same workbook.

User Activity Template

To monitor users, we can use the User Activity template, which provides metrics on the top Snowflake users, which data they interact with most and their behavior over time.

After filtering to include only the SALES_PROD_WH warehouse, the workbook gives some quick information on the numbers of users and queries in the specified time period.  It also lists the most active, most recent, and most regular users with a couple other breakdowns of activity.  On the Periodic tab, another anomaly appears: there were two weeks in February with an extreme increase in query volume for this warehouse.  Interestingly, this time period was not particularly high in credit consumption based on the Cost Monitoring template, warranting a deeper investigation into the data.

In just a few minutes, we have identified a few issues that we can explore in more depth using these same workbooks.  With Sigma's Snowflake Usage templates, we saved weeks of building dashboards and quickly found answers to questions about compute cost, CDW performance and user activity.