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;
Please note that if you rename the share from health_check to something else, you will need to replace the health_check text in the SQL above with the name you used.
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:
- Unused warehouses
- The top 5 warehouses by credit usage from the last 24 hours
- 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:
-- this may take more than a few minutes to complete
call health_check.core.savings_estimate('2024-11-25'); -- update the date as required for an up-to-date estimate
Example response:
| WAREHOUSE_NAME | ANNUALIZED_USAGE_DOLLARS | ANNUALIZED_USAGE_DOLLARS_WITH_SELECT | ANNUALIZED_SELECT_SAVINGS_DOLLARS | SELECT_SAVINGS_PERCENT |
|---|---|---|---|---|
| APP_BACKEND | 117895 | 108405 | 9490 | 8.05 |
| APP_SMALL | 76650 | 68255 | 8395 | 10.95 |
| APP_LARGE | 51465 | 43800 | 7665 | 14.89 |
| ADMIN | 64240 | 59130 | 5110 | 7.95 |
| APP | 12410 | 9855 | 2555 | 20.59 |
| APP_DBT_MEDIUM | 9855 | 8760 | 1095 | 11.11 |
For an accurate savings estimate, we recommend running the savings_estimate procedure across several dates and average the results. Please reach out to the SELECT team by clicking the chat bubble at the bottom right if you run into any issues.