Author: Tanvi Patni
SNOWSIGHT
Snowsight, the Snowflake web interface, is easy to use and navigate. It provides a visualization view for most of the objects present in Snowflake. Because of the better UI and visualization tool provided by Snowsight, we have created some dashboards using Snowsight for monitoring credit consumption at different levels.
RESOURCE MONITOR
Resource Monitor helps in controlling credit consumption by different warehouses running in the Snowflake account. And Resource Monitor can only be created by the Account Admin. Account Admin can give view and modify access on resource monitor for different users with other roles. User Managed warehouses can be controlled by resource Monitor based on credit consumption, But cloud service warehouses can not be controlled by Resource Monitor.
IMPORTANCE OF HAVING CREDIT CONSUMPTION DASHBOARD
● To track credit consumption and make the necessary decisions on it.
● To avoid excess credit consumption by different user-managed warehouse
● To find a Snowflake warehouse that lacks a resource monitor.
● To plan budget allocation based on credit consumption
STEPS TO CREATE DASHBOARD
1) Go to the Snowsight console from the classic UI
2) Click on the dashboard
3) And after that, click on the dashboard in the top right corner
4) Give the name of the dashboard and click on create a dashboard
5) Now you will see the below screen; select Warehouse for run the dashboard
6) Now start adding some tiles in your dashboard; click on add button in the top left
corner
7) Now write the query here
8) For getting query results in the form of a chart, click on the chart and select the chart whichever you want
9) Add more tiles in the same way
10) Refresh the dashboard to get updated results
QUERIES FOR CREATING DASHBOARDS
1) Sum of credit consumption at the account level
select
sum(credits_used)
from
account_usage.metering_history
where
start_time = :daterange;
2)Total storage used till current date at account level
select
avg(storage_bytes + stage_bytes + failsafe_bytes) / power(1024, 4) as billable_tb
from account_usage.storage_usage
where USAGE_DATE = current_date() -1;
3) Total no of jobs executed in the account till current date
select
count(*) as number_of_jobs
from
account_usage.query_history
where
start_time >= date_trunc(month, current_date);
4) How much data is stored monthly for failsafe, database, stage
select
date_trunc(month, usage_date) as usage_month,
avg(storage_bytes + stage_bytes + failsafe_bytes) / power(1024, 4) as billable_tb,
avg(storage_bytes) / power(1024, 4) as Storage_TB,
avg(stage_bytes) / power(1024, 4) as Stage_TB,
avg(failsafe_bytes) / power(1024, 4) as Failsafe_TB
from
account_usage.storage_usage
group by
1
order by
1;
5) Sum of credits that are consumed monthly at the account level
select
date_trunc('MONTH', usage_date) as Usage_Month,
sum(CREDITS_BILLED)
from
account_usage.metering_daily_history
group by
Usage_Month;
6) Total credit consumed by a particular 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;
7) sum of credit that is consumed by the cloud service warehouse and compute warehouse
select
warehouse_name,
sum(credits_used_cloud_services) credits_used_cloud_services,
sum(credits_used_compute) credits_used_compute,
sum(credits_used) credits_used
from account_usage.warehouse_metering_history where
True and start_time = :daterange
group by 1 order by 2 desc limit 10;
8) Query execution time by different query types
select
query_type,
warehouse_size,
avg(execution_time) / 1000 as average_execution_time
From account_usage.query_history
Where start_time = :daterange
group by 1,2 Order by 3 desc;
9) Average query execution time by a particular user
select
user_name,
(avg(execution_time)) / 1000 as average_execution_time
from
account_usage.query_history
where
start_time = :daterange
group by
1
order by
2 desc;
10) Execution time is taken by the repeated query
select
query_text,
(sum(execution_time) / 60000) as exec_time
From account_usage.query_history
where execution_status = 'SUCCESS'
group by query_text order by exec_time desc limit 25;
11) How much credit is consumed daily in terms of the dollar at the organizational level
select USAGE_IN_CURRENCY , USAGE_DATE from
snowflake.organization_usage.usage_in_currency_daily;
12) Sum of Credit consumption based on Warehouse tags
Prerequisite:
To get the below chart, first apply tags on the warehouse based on your need and then execute the below query in snow sight to get the chart.
select tag_value as department,
sum(credits_used) as credits
from
"SNOWFLAKE"."ACCOUNT_USAGE"."WAREHOUSE_METERING_HISTORY","SNO
WFLAKE"."ACCOUNT_USAGE"."TAG_REFERENCES"
where true
and warehouse_name=object_name
and tag_name='COST_CENTER'
and tag_database='DEV_ACCIDENTS_DB'
and tag_schema='LANDING_SCHEMA'
and start_time >= dateadd('days',-30,current_date())
group by 1
order by 2 desc;
CONCLUSION
Created credit consumption dashboards using Snowsight as a tool so that users can make the necessary decisions and monitor credit consumption very closely at different levels and parameters.
REFERENCES:
https://docs.snowflake.com/en/sql-reference/account-usage.html#overview-of-account-u
sage-schemas
https://medium.com/snowflake/monitoring-snowflake-with-snowsight-e9990a2898f1