OpsCenter from Sundeck is a free Snowflake Native App which provides a suite of tools to better understand, manage, and alert on Snowflake activity and spend. Key features of OpsCenter include:
ACCOUNTADMINprivileges to perform the initial setup
First, log in to Snowsight in your browser, as a user with
Next, we'll locate the listing for the Sundeck OpsCenter Native App, by:
The search will return a result for "OpsCenter" from Sundeck. Click the "Get" button on the listing.
You'll be taken to the OpsCenter Native App listing page. Here, you can learn more about the App and its features:
To install the App, click the "Get" button.
Once you click "Get" on the listing page, you will be prompted to confirm the installation. Note that the confirmation dialog has an "Options" section, which will allow you to specify the name of the App, as well as the Warehouse which will be used for installation tasks:
Modify any optional settings, and click "Get" to begin the installation.
Once installation is complete, you will see a confirmation dialog, which will offer to take you directly into OpsCenter:
Click "Configure" to complete the local setup of OpsCenter.
Once the Native App is launched, you will be taken to the OpsCenter Home page. Initially, a list of "Getting Started" tasks will be displayed. These will disappear once you have completed the setup steps.
To complete setup, we'll grant a handful of permissions to the OpsCenter application by running the provided SQL script in the "Grant Snowflake Privileges" section. Just click the "copy to clipboard" button in the upper-right corner of the SQL code block:
Then paste the script into a new Snowsight Worksheet. Be sure that you are using the ACCOUNTADMIN role, and click "Run":
Once the grants have been applied, you can return to the OpsCenter Home page, and click the "Refresh Status" button to confirm that the grants have been applied successfully:
Continue to the next step to start using OpsCenter!
Let's start by exploring one of the built-in reports in OpsCenter. Click on the "Warehouses" link in the left-hand navigation menu:
This will bring up the main Warehouse Utilization report:
This report shows a view of combined activity for all warehouses, indicating daily total cost as well as estimated utilization (percentage of the time the warehouse was used to full capacity).
The time period covered by the chart, as well as the resolution (days, weeks, months) of each bar can be adjusted by using the date filter controls at the top of the page. In addition, the warehouses included in the chart data can be filtered as well:
Below the cost and utilization chart are two additional analyses of warehouse activity:
The combination of these metrics can be quite useful for understanding the ratio of quiesced to active time for all warehouses (or particular warehouses as selected in the Filters), and can help inform correct warehouse sizing and auto-suspend settings.
In addition, an alternate Heatmap view of warehouse utilization is available, and can be accessed by selecting "Warehouse Heatmap" from the "Select Report" dropdown at the top of the page:
The Heatmap view shows warehouse utilization over time, broken out by day of the week. Darker shading indicates heavier warehouse utilization, and lighter indicates a higher fraction of idle time. This information can give immediate insight into whether your warehouses are sized appropriately, and may indicate where sizing needs to be adjusted throughout the week for maximum cost efficiency.
Next, let's look at the query analysis reports. Click on the "Queries" link in the left-hand navigation menu:
This will load the Query Activity Report by default, as well as a set of available filters for date range, warehouse, and OpsCenter labels (more on these later). Note that any filter criteria set at the top of the page will apply to other report types available on this Query Activity page. Filters can be accessed by clicking on the "Filters" dropdown:
We'll cover advanced aspects of filtering ("Include All", "Include Any", "Exclude Any") in a later section.
Let's quickly step through each of the available reports.
This report provides views of query cost (left graph) and query counts (right graph), along a selected dimension (User, Warehouse, Role, Query Type, and Execution Status). Each value of the selected dimension is displayed as a different color in the stacked bar chart, and can be included or excluded from the chart by clicking on its value in the legend.
This report can be accessed by selecting it from the "Select Report" dropdown at the top of the page:
This will load the Top Spenders report:
Here we can see the top users by total query cost, as well as the total number of queries for each of these users. Query cost is an estimate (based on query runtime, warehouse size used by the query, and an estimation of resource utilization based on concurrency), and is intended for relative comparison purposes only.
OpsCenter Probes allow you to configure once-per-minute queries against recent and currently-running query history (using Snowflake's
QUERY_HISTORY( ) table function), and take action based on defined conditions.
There are 2 elements to an OpsCenter Probe:
TRUE, the Probe can send an alert (email), cancel the query, or both.
Next, let's set up a Probe to monitor queries based on certain parameters. We'll start by building a new Probe to check for queries which run longer than 5 minutes, and cancel them if they are found.
Click on the "Probes" link in the left-hand navigation menu:
To create a new Probe, we'll click "New":
We simply choose a name for our new Probe, and then specify the conditions under which we want to be alerted. Note that the Condition is any valid
WHERE clause that can be applied to the
INFORMATION_SCHEMA.QUERY_HISTORY() table function.
In this case, we want to monitor by runtime, so let's use the
EXECUTION_TIME column to start. Note this column contains execution times in milliseconds, so in order to find queries with durations longer than 5 minutes, we need to use 300,000 (5 * 60 * 1000) milliseconds. We do this by entering
EXECUTION_TIME > 300000 in the "Condition" field.
We also need to add one more check in our condition, to ensure that our Probe does not cancel the OpsCenter app itself (which runs as a query in Snowflake). We'll do this for now by excluding any Streamlit queries, using this clause
AND NOT (query_text ilike 'execute streamlit%').
Finally, we need to select what we want to happen when queries are found that meet our condition. In this case, we will select "cancel".
Cick "Create" to create the Probe. After a few seconds, you'll be taken back to the list of Probes, and you'll see your new Probe listed:
From here, you can create further Probes, or modify/delete existing Probes.
OpsCenter Labels allow you to tag historical queries based on conditions you specify, and then filter Query Activity reports based on these Labels. This allows you to better understand your workload, and to drill down into specific types of queries.
A Label consists of:
SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORYtable. In addition, QTag functions can be used in Label Conditions to provide more advanced matching.
Let's first navigate to the Labels page by clicking on the "Labels" link in the left-hand navigation menu:
You'll see that initially, there are no Labels configured in the system. Let's create one!
There are two buttons below the labels listing: "New", which creates a new Ungrouped label, and "New (in group)" which creates a new Grouped label. We'll start with the simplest case, an Ungrouped label. Click "New":
All we need to provide is the label name, and the label condition. In this example, let's create a label which matches all queries which Snowflake had to spill to disk during processing. We'll call the label "Spilled", and provide the condition
BYTES_SPILLED_TO_LOCAL_STORAGE > 0 OR BYTES_SPILLED_TO_REMOTE_STORAGE (where clause on QUERY_HISTORY):
Click "Create" to save the label definition. You'll be taken back to the Labels listing page, and you'll see your new label listed:
Once you have created one or more OpsCenter Labels, they can be used in the advanced Filter Conditions on the Query Activity reports:
The dropdowns are fairly straightforward; each can contain muliple selections. Multiple selections in "Include All" are
ANDed together, meaning only queries which match all selected labels will be shown. Multiple selections in "Include Any" are
ORed together, such that queries matching any of the selections are shown. Selections in "Exclude Any" will be excluded from the results.
Grouped Labels are similar to Ungrouped Labels, but with one key difference: Only one Label within a given group can be applied to a single query. This allows you to "bin" queries across the labels in a group, then filter on that group to see a breakout of the data across all labels in the group.
For example, you could create a Grouped Label called "Long Running Queries", and then create a number of Labels within that group, each with a different runtime threshold. This would allow you to filter on "Long Running Queries" and see all queries which ran longer than a certain threshold, with a breakout by duration "bins".
To create Grouped labels, click on the "New (in group)" button, and you will notice that there are two additional fields to fill in ("Group Name" and "Rank"):
Since Group labels are effectively "binning" all queries into one of the labels within the group, the label conditions need to be tested in a specific order to provide deterministic results. To establish the order, set the Rank value relative to the other labels within the same group. Lower Rank numbers will be evaluated first.
To enable OpsCenter Probes to send notifications by email, we'll create a free Sundeck account.
From the OpsCenter Home Page, scroll down to the "Enable Notifications" section:
Next, we'll create a free Sundeck account, by right_clicking the link in Step 1 "Create a Free Sundeck Account".
This will open the Sundeck Signup page in a new window. We'll just need a couple of pieces of information to create the account:
First, let's sign in. In this example, we'll use email and password:
This will send a verification email to the email address provided. We'll need to open that email to retrieve the 6-digit verification code, and paste that when requested.
Once signed in, we'll be presented with a single page to enter the remaining information:
That's it! We now have a new Sundeck account!
To create a token for use in OpsCenter, navigate to the "Account Settings" menu by clicking your username in the upper-right corner of the Sundeck Web UI:
Then scroll down to the bottom of the page to the "Tokens" section, and click "Create Token"to create a new token. In the New Token dialog that appears, give this new token a name (such as "OpsCenter"), then click "Generate Token":
The token will be displayed, along with a "Copy to clipboard" button. Copy the token to the clipboard.
Return to OpsCenter, and paste the token into the Sundeck Token field, and click the "Enable Notifications" button:
Snowflake will ask you to confirm that it's OK to create an API integration. Click "Allow".
You've now connected OpsCenter to Sundeck. Continue to the next step to learn about the advanced OpsCenter features you've just enabled!
If you have chosen to connect OpsCenter to a free Sundeck account, you can now take advantage of a number of advanced features within OpsCenter, such as Notifications and advanced query pattern matching in Labels and Probes.
OpsCenter Probes can send emails via the Sundeck SaaS platform.
These extended alerting and messaging features are provided to your Snowflake account (and OpsCenter) via Snowflake External Functions, which when called from within Snowflake will send messages through Sundeck.
Once connected to Sundeck, you will be able to use the QLike( ) function within Label and Probe condition expressions. QLike provides very powerful query generalization and matching capabilities.
You can learn more about QLike concepts and see QLike examples in the Sundeck documentation.
Now that you've installed and configured the Sundeck OpsCenter Native App, you can use the built-in reports to better understand your Snowflake usage and spend, and drill down further into usage by building your own Labels. You can also set up Probes to regularly check for certain conditions, and alert (or cancel) queries if they exceed certain cost or time limits.
These tools run entirely within your Snowflake account, and do not require any external services or infrastructure. If you want to act on queries in real time before they are submitted to Snowflake, we recommend you check out Sundeck's SaaS offering!