Health Check Native App

Health Check Native App

Introduction

SELECT's Health Check native app provides high level account usage insights, along with an estimate of the automated savings that the full SELECT app can achieve.

Installation

Install the app from the native app marketplace listing. The app is available for free to all Snowflake accounts running in AWS. Native apps are not yet in public availability in other cloud providers.

Once installed, the app requires some permissions to run metadata queries in your account. From an accountadmin role, the permissions can be granted by running the below commands.

grant manage warehouses on account to application health_check;
grant imported privileges on database snowflake to application health_check;

Available Procedures

Before running the procedures, follow the setup instructions above.

health_check.core.healthcheck()

This stored procedure provides a high level account health report. The stored procedure highlights:

  1. Unused warehouses
  2. The top 5 warehouses by credit usage from the last 24 hours
  3. The top 5 query patterns from the last 24 hours

A query pattern is a grouping of queries by similar structure, ignoring literals and other typically changeable elements.

To run this procedure:

call health_check.core.healthcheck();

Example response:

SELECT Healthcheck Results
There are 20 warehouses in the account.
The following warehouses have not been used in 60 days and can likely be dropped:
DBT_CLOUD
LOOKER
BI
TRANSFORM_XSMALL
The top 5 most expensive warehouses in the last 24 hours were:
- APP_BACKEND_SMALL with 36.005 credits used (77.00% of total).
- APP_BACKEND_DBT_MEDIUM with 6.664 credits used (14.00% of total).
- APP_BACKEND with 2.653 credits used (6.00% of total).
- APP_BACKEND_LARGE with 0.842 credits used (2.00% of total).
- APP_BACKEND_SMALL with 0.625 credits used (1.00% of total).
The top 5 query patterns in the last 24 hours were:
1. Example query ID: 01af1c27-0000-33b3-0000-3de90338e232
- 28 executions.
- 2m average execution time.
- 7h 13m 16s of weighted execution time.
- Users APP, APP_BACKEND.
- Warehouses APP, APP_BACKEND_SMALL.
...
Where weighted execution time is calculated as execution time * warehouse size as integer (e.g. 1 for X-Small, 2 for Small, etc.)

health_check.core.savings_estimate(date)

This stored procedure provides an annualized estimate for the automated savings that the full SELECT app would have provided on a given date.

To run this procedure:

call health_check.core.savings_estimate('2023-08-01'); -- update the date as required for an up-to-date estimate

Example response:

WAREHOUSE_NAMEANNUALIZED_USAGE_DOLLARSANNUALIZED_USAGE_DOLLARS_WITH_SELECTANNUALIZED_SELECT_SAVINGS_DOLLARSSELECT_SAVINGS_PERCENT
APP_BACKEND11789510840594908.05
APP_SMALL7665068255839510.95
APP_LARGE5146543800766514.89
ADMIN642405913051107.95
APP124109855255520.59
APP_DBT_MEDIUM98558760109511.11