Skip to main content

Warehouse Monitoring (WAREHOUSE_METERING_HISTORY)

Virtual Warehouses burn credits, and credits cost money. As a Snowflake Administrator, your primary FinOps responsibility is to monitor warehouse usage, identify inefficiencies, and prevent runaway costs.

The core view used for this is SNOWFLAKE.ACCOUNT_USAGE.WAREHOUSE_METERING_HISTORY.

Topics Covered​

  • Understanding Warehouse States
  • Querying Credit Consumption
  • Identifying Inefficiencies
  • Setting Up Alerts

Understanding Warehouse States​

A warehouse only consumes compute credits when it is actively running. A warehouse transitions between three main states:

  1. STARTED (Running): The warehouse is fully provisioned and actively burning credits, whether it is executing queries or sitting idle waiting for auto-suspend.
  2. SUSPENDED: The warehouse is shut down. It costs zero compute credits.
  3. RESIZING: The warehouse is transitioning (e.g., from Small to Large).

Querying Credit Consumption​

The WAREHOUSE_METERING_HISTORY view logs exactly how many credits each warehouse burned every hour. It retains data for 365 days.

(To ensure you have access to the ACCOUNT_USAGE schema, run SELECT CURRENT_ROLE(); and ensure it is ACCOUNTADMIN or a role granted access to the SNOWFLAKE database.)

USE ROLE ACCOUNTADMIN;

-- Find the total credits burned by each warehouse over the last 30 days
SELECT
WAREHOUSE_NAME,
SUM(CREDITS_USED_COMPUTE) AS TOTAL_COMPUTE_CREDITS,
SUM(CREDITS_USED_CLOUD_SERVICES) AS TOTAL_CS_CREDITS,
SUM(CREDITS_USED) AS TOTAL_CREDITS
FROM SNOWFLAKE.ACCOUNT_USAGE.WAREHOUSE_METERING_HISTORY
WHERE START_TIME >= DATEADD(day, -30, CURRENT_DATE())
GROUP BY WAREHOUSE_NAME
ORDER BY TOTAL_CREDITS DESC;

Identifying Inefficiencies​

Simply knowing how much a warehouse costs isn't enough. You must correlate the cost with actual usage to find inefficiencies.

For example, imagine a large consulting firm in Bangalore has an X-Large warehouse named BLR_ANALYTICS_WH. It burns 16 credits an hour. If it runs for 10 hours a day, it costs 160 credits. But what if it only executes 5 simple queries during those 10 hours?

Finding Over-Provisioned Warehouses​

We can join WAREHOUSE_METERING_HISTORY with QUERY_HISTORY to find warehouses that are large and expensive, but are executing very little work.

WITH WAREHOUSE_COST AS (
-- Get total credits burned per warehouse in the last 7 days
SELECT
WAREHOUSE_NAME,
SUM(CREDITS_USED_COMPUTE) AS CREDITS
FROM SNOWFLAKE.ACCOUNT_USAGE.WAREHOUSE_METERING_HISTORY
WHERE START_TIME >= DATEADD(day, -7, CURRENT_DATE())
GROUP BY WAREHOUSE_NAME
),
WAREHOUSE_USAGE AS (
-- Get total execution time and query count per warehouse
SELECT
WAREHOUSE_NAME,
COUNT(QUERY_ID) AS QUERY_COUNT,
SUM(EXECUTION_TIME) / (1000 * 60) AS TOTAL_EXECUTION_MINUTES
FROM SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY
WHERE START_TIME >= DATEADD(day, -7, CURRENT_DATE())
GROUP BY WAREHOUSE_NAME
)
-- Join them together to find the "Cost per Minute of Actual Work"
SELECT
C.WAREHOUSE_NAME,
C.CREDITS AS TOTAL_CREDITS_BURNED,
U.QUERY_COUNT,
U.TOTAL_EXECUTION_MINUTES,
(C.CREDITS / NULLIF(U.TOTAL_EXECUTION_MINUTES, 0)) AS CREDITS_PER_MINUTE_OF_WORK
FROM WAREHOUSE_COST C
JOIN WAREHOUSE_USAGE U ON C.WAREHOUSE_NAME = U.WAREHOUSE_NAME
ORDER BY CREDITS_PER_MINUTE_OF_WORK DESC;

How to act on this data:

  • If CREDITS_PER_MINUTE_OF_WORK is extremely high, and the warehouse size is Large or X-Large, the warehouse is likely over-provisioned. You should Scale-Down to a Small.
  • If the warehouse has very few queries but high credit consumption, its Auto-Suspend might be set too high (e.g., 60 minutes). Lower it to 1 minute.

Setting Up Resource Monitors (Alerts)​

Instead of manually running these queries every day, Administrators use Resource Monitors to automatically track spending and send alerts if a warehouse goes rogue.

USE ROLE ACCOUNTADMIN;

-- Create a Resource Monitor for the Indian Development Team
CREATE OR REPLACE RESOURCE MONITOR INDIA_DEV_MONITOR
CREDIT_QUOTA = 500
FREQUENCY = MONTHLY
START_TIMESTAMP = IMMEDIATELY
TRIGGERS
ON 75 PERCENT DO NOTIFY
ON 90 PERCENT DO NOTIFY
ON 100 PERCENT DO SUSPEND
ON 110 PERCENT DO SUSPEND_IMMEDIATE;

-- Apply the monitor to specific warehouses
ALTER WAREHOUSE BLR_DEV_WH SET RESOURCE_MONITOR = INDIA_DEV_MONITOR;
ALTER WAREHOUSE DEL_TEST_WH SET RESOURCE_MONITOR = INDIA_DEV_MONITOR;

Note: A Resource Monitor requires ACCOUNTADMIN privileges. SUSPEND_IMMEDIATE will aggressively kill currently running queries to stop billing.

Summary​

  • Use WAREHOUSE_METERING_HISTORY to track historical credit consumption.
  • Join it with QUERY_HISTORY to find over-provisioned warehouses that are burning credits without doing much actual work.
  • Always implement Resource Monitors to alert you (and optionally suspend warehouses) when budgets are exceeded.