Monitoring your Snowflake Organization with Snowsight
This post builds on my previous blog in regards to monitoring your Snowflake environment (here). In this post, I will be focusing on queries and visualizations to allow you to better monitor your Snowflake Organization to show the simplicity of getting a cost overview of your entire Snowflake organization.
Updated Snowsight Usage Visualizations in Snowflake
Before I start, I want to give a shout-out to our product team who have created built-in visualizations in the new UI to ensure that spend is transparent — including across Snowflake Organizations.
In a demo from Snowflake Summit (45:00 mark here) you can see how you can individually monitor each account. Recently, our team has also added Organization-wide monitoring that allows ORGADMINS to visualize and track their total consumption, drill-in to individual accounts, and analyze the query breakdown for warehouses. Customers now have the ability to filter by date, account, type (compute vs. cloud services), and unit (credit/currency). This will allow ORGADMINs to drill in deeper to see their org usage easily visualized without out-of-the-box updates in the new UI. This is all available out-of-the-box, so click the Snowsight button in the classic console to get started!
Organizations in Snowflake
For those not familiar with the Organization feature on Snowflake, it is a first-class Snowflake object that links accounts to one business entity. For example, if a customer has multiple Snowflake accounts (these accounts can be in different regions and clouds), an ORGADMIN can monitor and manage all accounts within the Organization. Please see the Snowflake docs page (here) for additional information on the Organization Feature.
Personalized Snowflake Organizations Dashboard Sample
Organization Dashboard Setup
In addition to the out-of-the-box visualizations, many organizations want to create custom views to track usage and spending across different departments or projects. While many third-party tools and partners also have templates available, it’s even easier to have these views available directly in Snowflake. The below queries and setup are provided to assist when creating an administration dashboard using Snowsight. Prior to utilizing, please be sure you are either running with the ACCOUNTADMIN privilege or the role has use of the ORGANIZATION_USAGE schema which is located in the SNOWFLAKE database.
For instructions on visualizing data in Snowsight, utilize the following documentation (here). Each query below is its own tile on the dashboard with the screenshots showcasing how to configure the dashboard.
Note: If the organization schema shows up as blank, please reach out to Snowflake support as you may have to enable the Organization feature on your account
**Any queries that have :daterange is using a filter built-in specific to the Snowsight UI. If you’d like to have your own custom range you can do so by hard-coding the dates desired. See here for additional details on Filters.**
Dollars Remaining ($)
select
capacity_balance
from
snowflake.organization_usage.remaining_balance_daily
where
date =(
select
max(DATE)
from
snowflake.organization_usage.remaining_balance_daily
);
Note: I am using an internal account, hence the capacity balance is 0. If you are a capacity customer, this will show dollars remaining accordingly.
Yearly Run Rate (Credits/Year)
select
round(sum(credits_billed), 0) * 12
from
snowflake.organization_usage.metering_daily_history
where
usage_date > current_date() -30;
Note: Formula = Credits for past 30 days * 12
Current Storage in Organization (TB)
select
round(sum(average_bytes) / power(1024, 4), 2)
from
snowflake.organization_usage.storage_daily_history
where
usage_date =(
select
max(usage_date)
from
snowflake.organization_usage.storage_daily_history
);
Credits Used by Account
select
account_name,
sum(credits_billed)
from
snowflake.organization_usage.metering_daily_history
where
usage_date = :daterange
group by
account_name;
Top 10 Warehouses in Organization
select
account_name,
warehouse_name,
sum(credits_used_compute) as total_compute_credits
from
snowflake.organization_usage.warehouse_metering_history
where
start_time = :daterange
group by
account_name,
warehouse_name
order by
total_compute_credits desc
limit
10;
Storage Over Time by Account
select
usage_date,
account_name,
(average_bytes) / power(1024, 4) as TB
from
snowflake.organization_usage.storage_daily_history
where
usage_date = :daterange;
Note: The organization I am utilizing has 1000+ Snowflake accounts. Trends will show more optimally with your account as most customers have significantly fewer accounts.
Organization Usage by Day
select
usage_date,
account_name,
sum(usage_in_currency) as Total_Dollars
from
snowflake.organization_usage.usage_in_currency_daily
where
usage_date = :daterange
group by
usage_date,
account_name
order by
usage_date desc;
Organization Credit Trend
select
usage_date,
account_name,
round(sum(credits_billed), 2)
from
snowflake.organization_usage.metering_daily_history
where
usage_date = :daterange
group by
usage_date,
account_name;
Conclusion
Once the Organization dashboard setup steps are complete, you are ready to utilize the visualization on Snowflake. As a reminder, these serve as a template dashboard, and you do have the ability to rearrange and add/remove tiles as needed. If there is anything missing or any issues, please don’t hesitate to post in the comments!
In addition to this, stay tuned for a future post on how you can utilize the Organizations feature for chargeback scenarios across departments and even multiple companies with an Organization.
Hope you enjoyed the post!