The Accelerator for Snowflake Cost Monitoring enables Snowflake administrators to analyse the overall compute costs, performance monitoring, user adoption and error tracking of their Snowflake data stores. The Compute Cost Overview dashboard can be used to understand credit burn, plan budget allocation and identify peak outliers to reduce unnecessary spend. Use the User Adoption dashboard to track adoption of your new cloud platform and see where time is being allocated. The Performance Monitoring dashboard and Error tracking dashboard allow you to drill down on specific queries that may be taking longer or timing out.

The Slow Running Queries Dashboard has two versions available for use. The basic version uses persistent information from the ACCOUNT\_USAGE table. The advanced version uses custom SQL to generate Query Profiler analysis on common performance issues, such as exploding joins, inefficient pruning, etc. Analysis is limited to the last 14 days. Performance may be slower due to the custom SQL usage, as Query Profiler information is queried upon selection of each mark. If you do not wish to use the advanced version, close the CUSTOM\_SQL data source and delete the dashboard to avoid errors.

### Answer Key Business Questions

* What is the overall performance of our Snowflake infrastructure? Where is the highest consumption?
* Which warehouses are expending the highest cost?
* How does the compute expense of our database compare with our plan?
* Where are there performance issues with specific users or queries?
* What does user adoption look like? Who uses the database the most, and who is provisioned but doesn’t use it as much as we expected?
* Are there recurring errors to address and optimise?
* Based on current usage, what is the expected contract length?

### Monitor and Improve KPIs

* Number of Queries
* Queries per User
* Cloud Server Utilisation Rate
* Time per Query
* Error Rate
* Minutes Wasted on Errors
* Number of Users
* Avg. spend this month, day, last month
* Avg. daily credit usage
* Avg. monthly storage cost
* Cloud Services Layer Utilisation Rate
* Avg. Query Performance

### Snowflake + Tableau Partners who can help you succeed today

* [Slalom’s expertise with Tableau, Snowflake and Salesforce](https://slalom.turtl.co/story/converge/page/1) integrates platforms and delivers business value. [Click here](https://www.slalom.com/platforms/tableau) to learn more.
* [InterWorks](https://interworks.com) helps customers integrate Snowflake into your data ecosystems – [connect with them directly](https://interworks.com/contact) to find out how they can help you launch cloud-based visual analytics.
* [CoEnterprise](https://www.coenterprise.com/solutions/data-analytics/) provides Snowflake + Tableau Solutions including [Snowtrail](https://info.coenterprise.com/hubfs/Collateral/Analytics/CoEnterprise_SnowTrail.pdf), which helps you ensure the right level of security for your Snowflake infrastructure, as well as [Runaway](https://info.coenterprise.com/hubfs/Collateral/Analytics/RunAway-By-CoEnterprise.pdf), which integrates Slack into performance monitoring to help optimise your Snowflake investment.

### Connect to Your Snowflake Data to Enable This Accelerator

1. Create a new data source in this workbook
2. Select “Snowflake” as the data source, and if you have not previously installed the driver, do so now. Once the driver is installed, proceed to the next step.

Create one new data source for each of these schemas:
Database: SNOWFLAKE Schema: ACCOUNT\_USAGE Table: QUERY\_HISTORY
Database: SNOWFLAKE Schema: ACCOUNT\_USAGE Table: WAREHOUSE\_METERING\_HISTORY
Database: SNOWFLAKE Schema: ACCOUNT\_USAGE Table: STORAGE\_USAGE

If using the Slow Running Queries Advanced Dashboard:

Create another data source by connecting to Snowflake and choosing the ACCOUNT\_USAGE schema. Then, drag a new Custom SQL Table into the canvas. Input the following Custom SQL text (also found in the field “Custom SQL Text” in the data source). 

SELECT 
    QUERY\_ID,
    STEP\_ID,
    OPERATOR\_ID,
    PARENT\_OPERATOR\_ID,
    OPERATOR\_TYPE,
    OPERATOR\_STATISTICS,
    EXECUTION\_TIME\_BREAKDOWN,
    OPERATOR\_ATTRIBUTES,
    EXECUTION\_TIME\_BREAKDOWN:overall\_percentage::float AS OPERATOR\_EXECUTION\_TIME,
    OPERATOR\_STATISTICS:output\_rows output\_rows,
    OPERATOR\_STATISTICS:input\_rows input\_rows,
    CASE WHEN OPERATOR\_STATISTICS:input\_rows>0 THEN OPERATOR\_STATISTICS:output\_rows / OPERATOR\_STATISTICS:input\_rows ELSE 0 END AS row\_multiple,
    CAST(OPERATOR\_STATISTICS:spilling:bytes\_spilled\_local\_storage AS INT) bytes\_spilled\_local,
    CAST(OPERATOR\_STATISTICS:spilling:bytes\_spilled\_remote\_storage AS INT) bytes\_spilled\_remote,
    OPERATOR\_STATISTICS:io:percentage\_scanned\_from\_cache::float percentage\_scanned\_from\_cache,
    OPERATOR\_STATISTICS:table\_name::string tablename,
    CAST(OPERATOR\_STATISTICS:pruning:partitions\_scanned AS INT) partitions\_scanned,
    CAST(OPERATOR\_STATISTICS:pruning:partitions\_total AS INT) partitions\_total,
    OPERATOR\_STATISTICS:pruning:partitions\_scanned/OPERATOR\_STATISTICS:pruning:partitions\_total::float as partition\_scan\_ratio,
    CLUSTERING\_KEY,
    LAG(OPERATOR\_TYPE) OVER (ORDER BY OPERATOR\_ID) LAG\_OPERATOR\_TYPE
FROM TABLE(get\_query\_operator\_stats(\<Parameters.QueryID>))
    LEFT JOIN SNOWFLAKE\_SAMPLE\_DATA.INFORMATION\_SCHEMA.TABLES t
    on TABLENAME = t\.TABLE\_CATALOG \|\| “\.” \|\| t\.TABLE\_SCHEMA \|\| “\.” \|\| t\.TABLE\_NAME
ORDER BY STEP\_ID,OPERATOR\_ID

You may receive an error message, but this is because the parameter to select a valid Query ID has not been updated. You can ignore those error messages until you have replaced the data source.

<br>
Please note: As of May 2019 these tables do not contain any cost information pertaining to Materialised Views, Automatic Clustering or Snowpipe. That information is contained in separate tables.

3. Once you have connected to all four tables, right-click on the respective sample tables, and replace each with the appropriate table from your own Snowflake schema. For example, right-click on QUERY\_HISTORY\_SAMPLE and replace it with the QUERY\_HISTORY table from your own database.
4. When your own data is populating the dashboards, right-click on each of the sample data sources individually, and close them.

Snowflake Account Usage

Track your usage, costs and performance of your Snowflake account