top of page

Monitoring Snowflake Using Snowsight Dashboards

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 Snowsight console from classic UI



2) Click on dashboard




3) And after that, click on dashboard in the top right corner



4) Give the name of the dashboard and click on create dashboard



5) Now you will see the below screen, select Warehouse for running 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 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 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 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 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 organization 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 take 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

161 views0 comments

Recent Posts

See All
bottom of page