Monitoring Snowflake with Snowsight
2024 Update: For those looking for a Streamlit in Snowflake Version please see here.
Snowsight Overview
Since last year the Snowflake team has added large improvements to its user interface including improvements such as auto-complete, a minimalistic visualization layer, and even dashboarding capabilities. With this, it creates the perfect solution to easily be able to add additional monitoring capabilities on top of the ACCOUNT_USAGE schema that’s already in the SNOWFLAKE database. Many of my customers have been utilizing the dashboard below and wanted to open this to the larger community.
Admin Dashboard Sample
The below queries aim to assist in setting up an Administrator Dashboard on Snowflake to drill in even deeper into what is going on in your Snowflake environment. See below for a sample completed output of what an Admin Dashboard would look like
Setting up an Admin Dashboard on Snowflake
In the below section I will go over each of the setup steps to configure the dashboard tile by tile. The below queries and setup are provided to assist when creating an Admin Dashboard using Snowsight. Prior to utilizing, please be sure you are either running with the ACCOUNTADMIN privilege or the role has the ability to use the ACCOUNT_USAGE schema.
For instructions on how to Visualize Data in Snowsight utilize the following (here). Each query below is its own tile on the dashboard with the screenshots showcasing how to configure the dashboard.
Note: Each tile can be placed and resized to your liking. By double-clicking the title of each worksheet each tile can also be renamed.
Credits Used
select
sum(credits_used)
from
account_usage.metering_history
where
start_time = :daterange;
Total Number of Jobs Executed
select
count(*) as number_of_jobs
from
account_usage.query_history
where
start_time >= date_trunc(month, current_date);
Current Storage
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;
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;
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;
Warehouse Usage Greater than 7 Day Average
SELECT
WAREHOUSE_NAME,
DATE(START_TIME) AS DATE,
SUM(CREDITS_USED) AS CREDITS_USED,
AVG(SUM(CREDITS_USED)) OVER (
PARTITION BY WAREHOUSE_NAME
ORDER BY
DATE ROWS 7 PRECEDING
) AS CREDITS_USED_7_DAY_AVG,
(TO_NUMERIC(SUM(CREDITS_USED)/CREDITS_USED_7_DAY_AVG*100,10,2)-100)::STRING || '%' AS VARIANCE_TO_7_DAY_AVERAGE
FROM
"SNOWFLAKE"."ACCOUNT_USAGE"."WAREHOUSE_METERING_HISTORY"
WHERE
START_TIME = :daterange
GROUP BY
DATE,
WAREHOUSE_NAME
ORDER BY
DATE DESC;
Execution Time by Query Type (Avg Seconds)
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;
Top 25 Longest Queries
select
query_id,
query_text,
(execution_time / 60000) as exec_time
from
account_usage.query_history
where
execution_status = 'SUCCESS'
order by
execution_time desc
limit
25
Total Execution Time by Repeated Queries
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;
Credits Billed by Month
select
date_trunc('MONTH', usage_date) as Usage_Month,
sum(CREDITS_BILLED)
from
account_usage.metering_daily_history
group by
Usage_Month;
Average Query Execution Time (By 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;
GS Utilization by Query Type (Top 10)
select
query_type,
sum(credits_used_cloud_services) cs_credits,
count(1) num_queries
from
account_usage.query_history
where
true
and start_time >= timestampadd(day, -1, current_timestamp)
group by
1
order by
2 desc
limit
10;
Compute and Cloud Services by 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;
Credit Breakdown by Day with Cloud Services Adjustment
select
*
from
account_usage.metering_daily_history
where
USAGE_DATE = :daterange;
Data Storage used Overtime
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;
Total Row Loaded by Day
SELECT
TO_CHAR(TO_DATE(load_history.LAST_LOAD_TIME),
'YYYY-MM-DD') AS "load_history.last_load_time_date",
COALESCE(SUM(load_history.ROW_COUNT),
0) AS "load_history.total_row_count"
FROM
SNOWFLAKE.ACCOUNT_USAGE.LOAD_HISTORY AS load_history
WHERE
(((load_history.LAST_LOAD_TIME) = :daterange
AND (load_history.LAST_LOAD_TIME) = :daterange))
GROUP BY
TO_DATE(load_history.LAST_LOAD_TIME)
ORDER BY
1 DESC;
Logins by User
select
user_name,
sum(iff(is_success = 'NO', 1, 0)) as Failed,
count(*) as Success,
sum(iff(is_success = 'NO', 1, 0)) / nullif(count(*), 0) as login_failure_rate
from
account_usage.login_history
where
event_timestamp = :daterange
group by
1
order by
4 desc;
Logins by Client
select
reported_client_type as Client,
user_name,
sum(iff(is_success = 'NO', 1, 0)) as Failed,
count(*) as Success,
sum(iff(is_success = 'NO', 1, 0)) / nullif(count(*), 0) as login_failure_rate
from
account_usage.login_history
where
event_timestamp = :daterange
group by
1,
2
order by
5 desc;
Once all the above setup is complete you are now all ready to go to utilize the Admin Dashboard on Snowflake. Once again, you can rearrange and add/remove tiles as you need. If there is anything missing that you’d like to see please don’t hesitate to put it in the comments below!
Hope you enjoyed the post!
Fun fact: All query formatting was done by Snowsight’s Format Query option. My SQL is usually not this cleanly formatted :)