Implement Cost Monitoring Dashboards using Snowsight

Thank you for reading my earlier blogs on the Snowflake cost series. If you haven’t read the earlier blogs in the series, you can refer to them here —

1. Cost optimization series — https://medium.com/@poojakelgaonkar/snowflake-cost-optimization-series-7aac34697608
2. Understanding Snowflake Cost — https://medium.com/snowflake/understanding-snowflake-cost-10165aea767f
3. Understanding Warehouse Cost and Optimization — https://medium.com/snowflake/understanding-warehouse-cost-and-optimization-8fdffe2f68e6
4. Understanding Optimization Services — https://medium.com/snowflake/understanding-snowflake-optimization-services-b8c8dbad1f52
5. Implementing Snowflake cost optimization techniques — https://medium.com/snowflake/implementing-snowflakes-cost-optimization-techniques-b5778249b5f7

You can also subscribe and read my earlier blogs on Snowflake and Data topics if not done so already.

This is the last blog in the series that focuses more on implementing the dashboards and monitoring techniques that will help to optimize the performance and cost as well as help in implementing the controls to monitor and report the utilization.

You can start with small steps and standards while creating or defining the Snowflake components. You can consider below scenarios and examples from below to implement optimization techniques.

With last blog, you can have below services and components setup before you start with implementing dashboards.

1. Resource Monitors — You can have monitors setup to monitor and notify you in case of warehouse usage reaches defined threshold.

/* create a resource monitor for PROD warehouse */

create resource monitor PROD_WH_MONITOR with credit_quota=500
frequency = daily
start_timestamp = IMMEDIATELY 
notify_users = (POOJAKELGAONKAR, "POOJAKELGAONKAR")
triggers on 90 percent do notify;

/* assign resource monitor to the warehouse  */

ALTER WAREHOUSE PROD_WH SET RESOURCE_MONITOR = PROD_WH_MONITOR;

2. Notification Integrations — You can setup the notification integration to send emails to the team. You can also use cloud native services like AWS SNS as an integration to send notification to SNS and subscribe it to send notification to slack or email or any other ticketing system if needed.

CREATE NOTIFICATION INTEGRATION email_int
  TYPE=EMAIL
  ENABLED=TRUE
  ALLOWED_RECIPIENTS=('first1.last1@example.com','first2.last2@example.com');

use snowflake stored procedure to send an email —

CALL SYSTEM$SEND_EMAIL(
    'email_int',
    'first1.last1@example.com, first2.last2@example.com',
    'Email Alert: Task A has finished.',
    'Task A has successfully finished.\nStart Time: 10:10:32\nEnd Time: 12:15:45\nTotal Records Processed: 115678'
);

You can use this notification integration and send email stored procedure to generate alerts as and when needed without any external integration with AWS/GCP/Azure. You can integrate with these clouds depending on your business/application use cases.

3. Query & Object Tagging — You can tag the objects to identify their usage and build necessary metrics for monitoring and using them to analyze usage on the dashboards. You can set the query tags to identify the application workloads, ad-hoc workloads or the queries that consume high usage — compute, cloud services etc. You can also view the tagged objects in snowsight -> Data Governance -> Tagged objects pane.

4. Setting up access to Snowflake metadata views — Snowflake share consist of ACCOUNT_USAGE and INFORMATION_SCHEMA. These schemas contain bunch of views that stores metadata information about the Snowflake objects, queries, workloads, warehosues, tasks, resource monitors, serverless tasks, pipes, and all the operations being performed on the platform. You need to have read access to these views to see the relevant information needed to setup the dashboards. You can also create a custom role and assign read access to these USAGE views. The custom role can be assigned to users who needs access to the dashboards.

Implementing Dashboards —

You can use Snowsight’s dashboarding feature to setup the appropriate usage dashboards adding widgets to the single dashboard. Typically, you can setup 3–4 categories of dashboards depending on the type, usage and consumers. You can plan on creating dashboard for your finops team, DataOps team, Data engineering and Platform support teams. You can customize your dashboard depending on the consumer and their requirements to view specific dashboards.

In this given use case, you can focus on the FinOps dashboard where you should share the usage and cost details for your platform. You can consider below metrics that team might be interested and you can share these to management as well.

• Overall Cost -> cost trend for a given period
• Mutiple application’s cost spend → if there are multiple applications hosted on single platform. This is typically used as chargeback to the application teams.
• Warehouse Usage
• Cloud services Usage
• Storage Usage

You can use metadata views to derive metrics for these dashboards.

1. Create Compute credit consumption

SELECT ORGANIZATION_NAME, ACCOUNT_NAME, REGION, 
DATE_TRUNC(MONTH, USAGE_DATE) as USAGE_MONTH, 
SUM(CREDITS_BILLED) AS CREDITS_BILLED
FROM SNOWFLAKE.ORGANIZATION_USAGE.METERING_DAILY_HISTORY
GROUP BY ORGANIZATION_NAME, ACCOUNT_NAME, REGION, DATE_TRUNC(MONTH, USAGE_DATE)
ORDER BY USAGE_MONTH;

2. Storage Usage -

select date_trunc(month, usage_date) as usage_month
  , avg(storage_bytes + stage_bytes + failsafe_bytes) / power(1024, 2) as billable_tb
from storage_usage
group by 1
order by 1;

3. Credit Usage by Warehouse — You can implement multitenancy using warehouse and databases in Snowflake. You can serve to multiple applications — assign separate warehouse, databases to them and track their usage for chargeback as well as identify if any additional $ over the period of time.

select warehouse_name, date_trunc(month, start_time) as month, 
       sum(credits_used) as total_credits_used
from warehouse_metering_history
where start_time >= date_trunc(month, current_date)
group by 1,2
order by 2;

4. Storage Usage by Databases — Track databases storages using below SQL

SELECT TO_DATE(USAGE_DATE) AS USAGE_DATE, 
DATABASE_NAME, 
SUM(AVERAGE_DATABASE_BYTES+AVERAGE_FAILSAFE_BYTES)/(1024*1024) AS STORAGE_MB
FROM SNOWFLAKE.ACCOUNT_USAGE.DATABASE_STORAGE_USAGE_HISTORY 
GROUP BY RULE_NAME, TO_DATE(USAGE_DATE), DATABASE_NAME ORDER BY TO_DATE(USAGE_DATE) DESC;

5. You can also monitor the query workloads however these are often considered for engineering, dataops dashboards. From usage perspective, you can use query_history to find the % of queries where cloud service or warehouse cost is more than acceptable % ratio.

6. You can use each of the given queries to define metrics and create a dashboard.

Usage Dashboard

You can follow my earlier blog to implement dashboards step by step here — https://medium.com/snowflake/building-dashboards-using-snowsight-daacf4bd42a8

Hope this blog helps you to get started with implementing cost dashboard to report as well as optimize the usage. You can also implement various metrics and monitoring, alerting on top of usage views. You can setup the resource monitors or use tasks to invoke the queries to compare the usage % and generate an email notification using send email stored procedure.

Please follow my blog series on Snowflake topics to understand various aspects of data design, engineering, data governance, cost optimizations, etc.

About Me :

I am one of the Snowflake Data Superheroes 2023. I am also one of the Snowflake SnowPro Core SME- Certification Program. I am a DWBI and Cloud Architect! I am currently working as Senior Data Architect — GCP, Snowflake. I have been working with various Legacy data warehouses, Bigdata Implementations, and Cloud platforms/Migrations. I am SnowPro Core certified Data Architect as well as Google certified Google Professional Cloud Architect. You can reach out to me LinkedIn if you need any further help on certification, Data Solutions, and Implementations!