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:
- STARTED (Running): The warehouse is fully provisioned and actively burning credits, whether it is executing queries or sitting idle waiting for auto-suspend.
- SUSPENDED: The warehouse is shut down. It costs zero compute credits.
- 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_WORKis extremely high, and the warehouse size isLargeorX-Large, the warehouse is likely over-provisioned. You should Scale-Down to aSmall. - 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_HISTORYto track historical credit consumption. - Join it with
QUERY_HISTORYto 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.