Table: snowflake_resource_monitor - Query Snowflake Resource Monitors using SQL

A Snowflake Resource Monitor is a tool within Snowflake that allows you to track and control the usage of resources within your Snowflake account. It provides a way to set up and manage alerts for various resources, including virtual warehouses, databases, and more. Snowflake Resource Monitor helps you stay informed about the health and performance of your Snowflake resources and take appropriate actions when predefined conditions are met.

Table Usage Guide

The snowflake_resource_monitor table provides insights into Resource Monitors within Snowflake. As a Database Administrator, explore monitor-specific details through this table, including usage, limits, and associated metadata. Utilize it to uncover information about resources, such as those nearing their limits, the usage patterns, and the verification of resource usage.

Examples

Basic info

Analyze your Snowflake resource monitor to understand your warehouse's credit usage. This can help you manage your resources more efficiently by showing you how much credit quota is available, how much has been used, and what remains.

select
  name as warehouse,
  credit_quota,
  used_credits,
  remaining_credits
from
  snowflake_resource_monitor;

select
  name as warehouse,
  credit_quota,
  used_credits,
  remaining_credits
from
  snowflake_resource_monitor;

List warehouses and % credit left

Determine the areas in which your warehouse's credit usage exceeds 75% of the total quota. This query helps in monitoring resource consumption, alerting you to potential overruns before they occur.

select
  account,
  name as warehouse,
  credit_quota,
  used_credits,
  remaining_credits,
  round((used_credits / credit_quota * 100) :: numeric, 1) as percent_used,
  case
    when used_credits / credit_quota * 100 > 90 then 'alert'
    when used_credits / credit_quota * 100 > 75 then 'warning'
    else 'ok'
  end as type
from
  snowflake_resource_monitor
where
  used_credits / credit_quota * 100 > 75
order by
  used_credits / credit_quota desc;

select
  account,
  name as warehouse,
  credit_quota,
  used_credits,
  remaining_credits,
  round((used_credits / credit_quota * 100), 1) as percent_used,
  case
    when used_credits / credit_quota * 100 > 90 then 'alert'
    when used_credits / credit_quota * 100 > 75 then 'warning'
    else 'ok'
  end as type
from
  snowflake_resource_monitor
where
  used_credits / credit_quota * 100 > 75
order by
  used_credits / credit_quota desc;

List warehouses which have used all their credits

The query is used to identify Snowflake warehouses that have exhausted their credit allocation. This is beneficial in managing resources effectively and avoiding potential disruptions in data processing tasks.

select
  account,
  name as warehouse
from
  snowflake_resource_monitor
where
  remaining_credits < 1;

select
  account,
  name as warehouse
from
  snowflake_resource_monitor
where
  remaining_credits < 1;

Schema for snowflake_resource_monitor

Name	Type	Description
_ctx	jsonb	Steampipe context in JSON form, e.g. connection_name.
account	text	The Snowflake account ID.
comment	text	Comment for the warehouse.
created_on	timestamp with time zone	Date and time when the monitor was created.
credit_quota	double precision	Specifies the number of Snowflake credits allocated to the monitor for the specified frequency interval.
end_time	timestamp with time zone	Date and time when the monitor was stopped.
frequency	text	The interval at which the used credits reset relative to the specified start date (Daily,Weekly,Monthly,Yearly,Never).
level	text	Specifies whether the resource monitor is used to monitor the credit usage for your entire Account (i.e. all warehouses in the account) or a specific set of individual warehouses.
name	text	Name for warehouse.
notify_at	jsonb	Levels to which to alert.
notify_users	text	Who to notify when alerting.
owner	text	Role that owns the warehouse.
region	text	The Snowflake region in which the account is located.
remaining_credits	double precision	Number of credits still available to use in the current monthly billing cycle.
start_time	timestamp with time zone	Date and time when the monitor was started.
suspend_at	jsonb	Levels to which to suspend warehouse.
suspend_immediately_at	jsonb	Levels to which to suspend warehouse.
used_credits	double precision	Number of credits used in the current monthly billing cycle by all the warehouses associated with the resource monitor.

Export

This table is available as a standalone Exporter CLI. Steampipe exporters are stand-alone binaries that allow you to extract data using Steampipe plugins without a database.

You can download the tarball for your platform from the Releases page, but it is simplest to install them with the steampipe_export_installer.sh script:

/bin/sh -c "$(curl -fsSL https://steampipe.io/install/export.sh)" -- snowflake

You can pass the configuration to the command with the --config argument:

steampipe_export_snowflake --config '<your_config>' snowflake_resource_monitor