Spotting Idle Warehouses Before They Burn Through Your Budget

Now that we’ve loaded our Costs Dashboard with all sorts of goodness, let’s take it a step further and make it even more useful. The goal: track unused warehouses to help manage spend and reduce clutter.

Step 1: Snapshot the Current State of Warehouses

First, we’ll create a table to capture the current state of all active warehouses:

CREATE OR REPLACE TABLE MONITORING.WAREHOUSE_SNAPSHOT (
      warehouse_name STRING,
      state STRING,
      size STRING,
      auto_suspend NUMBER,
      auto_resume BOOLEAN,
      created_on TIMESTAMP_LTZ,
      snapshot_time TIMESTAMP_LTZ DEFAULT CURRENT_TIMESTAMP()
);

This table gives us a point-in-time snapshot of each warehouse’s configuration.

Step 2: Populate the Table with Live Data

To populate the table, we’ll use the SHOW WAREHOUSES command. Since SHOW commands return results outside of normal result sets, we’ll use the RESULT_SCAN function to capture the output:

SHOW WAREHOUSES;

INSERT INTO MONITORING.WAREHOUSE_SNAPSHOT 
(warehouse_name, state, size, auto_suspend, auto_resume, created_on)
SELECT "name", 
       "state",
       "size",
       "auto_suspend",
       "auto_resume",
       "created_on"
FROM TABLE(RESULT_SCAN(LAST_QUERY_ID()));

📌 Note: Because SHOW is a standalone command, the RESULT_SCAN(LAST_QUERY_ID()) workaround is required to query its results. This insert should be rerun whenever a new warehouse is added—or on a regular schedule—so that your snapshot remains current.

Step 3: Identify Warehouses Unused in the Last 30 Days

Let’s now build a view to identify warehouses that haven’t been used in the past 30 days. These are prime candidates for disabling or deletion.

CREATE OR REPLACE VIEW MONITORING.UNUSED_WAREHOUSES AS

WITH used_warehouses AS (
    SELECT
        WAREHOUSE_NAME,
        COUNT(*) AS query_count,
        MAX(START_TIME) AS last_used
    FROM SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY
    WHERE START_TIME >= DATEADD(DAY, -30, CURRENT_TIMESTAMP())
    GROUP BY WAREHOUSE_NAME  )
SELECT
    w.warehouse_name,
    w.state,
    w.size,
    w.auto_suspend,
    w.auto_resume,
    w.created_on,
    w.snapshot_time,
    COALESCE(u.query_count, 0) AS query_count_last_30_days,
    u.last_used
FROM MONITORING.WAREHOUSE_SNAPSHOT w
LEFT JOIN used_warehouses u
    ON w.warehouse_name = u.warehouse_name
WHERE u.warehouse_name IS NULL
ORDER BY w.warehouse_name;

Running this view for the first time might make you feel like a Snowflake superhero… or send you into cleanup mode. If your environment has warehouses spinning without a purpose, this will bring them into the light.

Step 4: Surface the Insight in Your Dashboard

Finally, add this query as a new tile to your existing dashboard:

SELECT *
FROM MONITORING.UNUSED_WAREHOUSES
ORDER BY WAREHOUSE_NAME;

You now have two powerful perspectives:

• Daily spend (via your existing chart), and
• Unused compute resources (via this new view).

Together, they’ll help you manage cost, streamline warehouse usage, and keep your Snowflake environment lean and mean.

With that, it is time for me to sign off once again. Hopefully, you now have even more tools to monitor your Snowflake space and learn how to utilize Snowflake dashboards to make your life a little easier. Until next time, my friends – make sure your Snowflake environment is like OutKast – so fresh and so clean, clean!