turbot/snowflake
steampipe plugin install snowflake

Table: snowflake_warehouse_metering_history - Query Snowflake Warehouse Metering History using SQL

Snowflake Warehouse Metering History is a feature within Snowflake that tracks the consumption of Snowflake credits by virtual warehouses over time. It provides a detailed breakdown of resource utilization, enabling users to monitor and manage their Snowflake credit usage. This can aid in identifying patterns, optimizing costs, and managing resources more efficiently.

Table Usage Guide

The snowflake_warehouse_metering_history table provides insights into the consumption of Snowflake credits by virtual warehouses over time. As a data analyst or a cloud cost manager, explore detailed breakdowns of resource utilization through this table, including the number of Snowflake credits consumed, the time period of consumption, and the specific warehouses involved. Utilize it to uncover information about credit consumption patterns, cost optimization opportunities, and efficient resource management.

Examples

Basic info

Discover the segments that have utilized resources in your Snowflake warehouse. This query is beneficial as it allows you to analyze the consumption of credits, providing insights into resource usage and aiding in efficient resource management.

select
warehouse_name,
warehouse_id,
start_time,
end_time,
credits_used,
credits_used_compute,
credits_used_cloud_services
from
snowflake_warehouse_metering_history;
select
warehouse_name,
warehouse_id,
start_time,
end_time,
credits_used,
credits_used_compute,
credits_used_cloud_services
from
snowflake_warehouse_metering_history;

List the metering history for a particular warehouse

Gain insights into the usage history of a specific warehouse by analyzing its consumption of credits over time. This aids in cost management and optimization by tracking resource usage.

select
warehouse_name,
warehouse_id,
start_time,
end_time,
credits_used,
credits_used_compute,
credits_used_cloud_services
from
snowflake_warehouse_metering_history
where
warehouse_name = 'COMPUTE_WH';
select
warehouse_name,
warehouse_id,
start_time,
end_time,
credits_used,
credits_used_compute,
credits_used_cloud_services
from
snowflake_warehouse_metering_history
where
warehouse_name = 'COMPUTE_WH';

List the metering history for the inactive warehouses

Explore the metering history of warehouses that are currently inactive. This can be useful to analyze past resource usage and expenditure for warehouses that are no longer in use.

select
warehouse_name,
warehouse_id,
start_time,
end_time,
credits_used,
credits_used_compute,
credits_used_cloud_services
from
snowflake_warehouse_metering_history as h,
snowflake_warehouse as w
where
h.warehouse_name = w.name
and state = 'SUSPENDED';
select
warehouse_name,
warehouse_id,
start_time,
end_time,
credits_used,
credits_used_compute,
credits_used_cloud_services
from
snowflake_warehouse_metering_history as h,
snowflake_warehouse as w
where
h.warehouse_name = w.name
and state = 'SUSPENDED';

List the metering history for the last 10 days

Explore the credit usage of your warehouse in the last 10 days. This helps in understanding the resource consumption for better planning and management.

select
warehouse_name,
warehouse_id,
start_time,
end_time,
credits_used,
credits_used_compute,
credits_used_cloud_services
from
snowflake_warehouse_metering_history
where
start_time >= now() - interval '10' day;
select
warehouse_name,
warehouse_id,
start_time,
end_time,
credits_used,
credits_used_compute,
credits_used_cloud_services
from
snowflake_warehouse_metering_history
where
start_time >= datetime('now', '-10 days');

List the top 5 warehouses with the highest credits used for cloud services in a particular account

Explore the top five warehouses with the highest usage of credits for cloud services within a specific account. This can be beneficial in identifying potential areas of cost savings and optimizing resource allocation.

select
warehouse_id,
warehouse_name,
account,
credits_used_cloud_services
from
snowflake_warehouse_metering_history
where
account = 'desired_account'
order by
credits_used_cloud_services desc
limit
5;
select
warehouse_id,
warehouse_name,
account,
credits_used_cloud_services
from
snowflake_warehouse_metering_history
where
account = 'desired_account'
order by
credits_used_cloud_services desc
limit
5;

Calculate the average credits used per hour for each warehouse

Analyze the usage of each warehouse by calculating the average credits consumed per hour. This can help in cost optimization and efficient resource allocation.

select
warehouse_id,
warehouse_name,
AVG(credits_used) as avg_credits_per_hour
from
snowflake_warehouse_metering_history
group by
warehouse_id,
warehouse_name;
select
warehouse_id,
warehouse_name,
AVG(credits_used) as avg_credits_per_hour
from
snowflake_warehouse_metering_history
group by
warehouse_id,
warehouse_name;

Calculate the percentage of cloud services credits used compared to total credits for each warehouse

Determine the proportion of cloud services credits utilized in relation to the total credits for each warehouse. This is useful for understanding the extent of cloud services usage and managing resource allocation effectively.

select
warehouse_id,
warehouse_name,
(credits_used_cloud_services / credits_used) * 100 as cloud_services_percentage
from
snowflake_warehouse_metering_history
where
credits_used > 0;
select
warehouse_id,
warehouse_name,
(credits_used_cloud_services / credits_used) * 100 as cloud_services_percentage
from
snowflake_warehouse_metering_history
where
credits_used > 0;

Schema for snowflake_warehouse_metering_history

NameTypeOperatorsDescription
_ctxjsonbSteampipe context in JSON form.
accounttext=, !=, ~~, ~~*, !~~, !~~*The Snowflake account ID.
credits_useddouble precisionNumber of credits billed for this warehouse in this hour.
credits_used_cloud_servicesdouble precisionNumber of credits used for cloud services in the hour.
credits_used_computedouble precisionNumber of credits used for the warehouse in the hour.
end_timetimestamp with time zoneThe end of the hour in which this warehouse usage took place.
regiontextThe Snowflake region in which the account is located.
sp_connection_nametext=, !=, ~~, ~~*, !~~, !~~*Steampipe connection name.
sp_ctxjsonbSteampipe context in JSON form.
start_timetimestamp with time zoneThe beginning of the hour in which this warehouse usage took place.
warehouse_idtext=Internal/system-generated identifier for the warehouse.
warehouse_nametext=Name of the warehouse.

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_warehouse_metering_history