Building Dashboard for Snowflake Account Monitoring
Building dashboards on Snowflake is very simply, where you can visualize data by collection of customized charts, that are generated by query results.
For demonstration, lets build a dashboard to monitor your free trail account that illustrates following charts -
Days Remaining for Free Snowflake Account Expiry
Storage in TB
Storage Usage
Remaining $ worth Free Usage of given $400
Credit Usage by Warehouse
Compute Credits Used
Credit Usage Overtime
Users vs WH Usage
The illustrated Trail Account is Enterprise edition hoisted on Azure cloud platform at Central India (Pune) which is about to expire in 9 days.
The pricing associated is $3.30 cost per credit and $46 per TB / per month.
Alother way is to et the daily pricing from SNOWFLAKE.ORGANIZATION_USAGE.RATE_SHEET_DAILY.
Note: I have hardcoded the rates in this exercise, instead of which you can make use of the above view.
Creating Dashboard -
To build a dashboard, we can add new tiles from the dashboard page
or we move the worksheets to the dashboard
In the worksheet below section, select chart option after validating the results, to
Following are the dashboard tiles created using SNOWFLAKE share database. See to that your role has usage access on that database, else you can get up getting authorization errors.
Key account monitoring queries for dashboard:
Days Remaining for Free Snowflake Account Expiry
SELECT
DATEDIFF(D,CURRENT_DATE(),END_DATE — 1) DAYS_LEFT_IN_FREE_TRIAL
FROM SNOWFLAKE.ORGANIZATION_USAGE.CONTRACT_ITEMS
;
Storage in TB
/* 1 KB is equal to 1024 Bytes (binary) and 1000 Bytes (decimal)*/
/* KB → MB → GB → TB → PB */
select
avg (storage_bytes) / power(1024, 4) as “Database Storage”,
avg (stage_bytes) / power(1024, 4) as “Stage Storage”,
avg (failsafe_bytes) / power(1024, 4) as “Failsafe Storage”,
avg (storage_bytes + stage_bytes + failsafe_bytes) / power(1024, 4) as “Total Storage”
from snowflake.account_usage.storage_usage
where usage_date = current_date() — 1
;
Storage Usage
/* 1 KB is equal to 1024 Bytes (binary) and 1000 Bytes (decimal)*/
/* KB → MB → GB → TB → PB */
select
avg (storage_bytes + stage_bytes + failsafe_bytes) / power(1024, 4) as storage_tb,
storage_tb * 46 as storage_cost
from snowflake.account_usage.storage_usage
where usage_date = current_date() — 1
;
Remaining $ worth Free Usage of given $400
/* Select Tire has $400 free usage */
Select
400
-
(
(
select
(avg (storage_bytes + stage_bytes + failsafe_bytes) / power(1024, 4)) * 46 as storage_cost
from account_usage.storage_usage
where usage_date = current_date() — 1
)
+
(
select
sum(credits_used) * 3.3 as credit_cost
from account_usage.metering_history
— where start_time = :daterange
— and start_time >= date_trunc(month, current_date)
)
)
;
Credit Usage by Warehouse
select
warehouse_name,
sum(credits_used) as total_credits_used
from account_usage.warehouse_metering_history
/* where start_time = :daterange */
group by 1
order by 2 desc;
Compute Credits Used
select sum(credits_used)
from account_usage.metering_history
/* where start_time = :daterange
and start_time >= date_trunc(month, current_date) */
;
Credit Usage Overtime
select
start_time::date as usage_date,
warehouse_name,
sum(credits_used) as total_credits_used
from account_usage.warehouse_metering_history
/* where start_time = :daterange */
group by 1,2
order by 2,1
;
Users vs WH Usage
select WAREHOUSE_NAME,USER_NAME, COUNT (*) FROM SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY
where USER_NAME <> ‘SYSTEM’
and WAREHOUSE_NAME is not NULL
group by 1,2
;
Using the given metrics as sample, you can try different appearances with different data and cart types. The tiles can be rearranged and more can be added to the dashboard.
We can share the dashboard to other users within the Snowflake account to view just the results or to execute the dashboards.
References: https://docs.snowflake.com/en/user-guide/ui-snowsight-dashboards.html
Follow and Clap if you like the content and feel free to ask if you have any questions in the comments. I will be more than happy to assist and guide you.
