Share via

Warehouses system table reference

  • Article

Important

This system table is in Public Preview. To access the table, the schema must be enabled in your system catalog. For more information, see Enable system table schemas.

In this article, you learn how to use the warehouses system table to monitor and manage the SQL warehouses in your workspaces. Each row is a snapshot of the SQL warehouse properties at that moment. A new snapshot is created when the properties change.

The warehouses system table is located at system.compute.warehouses.

Warehouses table schema

Column name Data type Description Example
warehouse_id string The ID of the SQL warehouse. 123456789012345
workspace_id string The ID of the workspace where the warehouse is deployed. 123456789012345
account_id string The ID of the Azure Databricks account. 7af234db-66d7-4db3-bbf0-956098224879
warehouse_name string The name of the SQL warehouse. My Serverless Warehouse
warehouse_type string The type of SQL warehouse. Possible values are CLASSIC, PRO, and SERVERLESS. SERVERLESS
warehouse_channel string The channel of the SQL warehouse. Possible values are CURRENT and PREVIEW. CURRENT
warehouse_size string The cluster size of the SQL warehouse. Possible values are 2X_SMALL, X_SMALL, SMALL, MEDIUM, LARGE, X_LARGE, 2X_LARGE, 3X_LARGE, and 4X_LARGE. MEDIUM
min_clusters int The minimum number of clusters permitted. 1
max_clusters int The maximum number of clusters permitted. 5
auto_stop_minutes int The number of minutes before the SQL warehouse auto-stops due to inactivity. 35
tags map Tags for the SQL warehouse. {"budget":"research"}
change_time timestamp Timestamp of change to the SQL warehouse definition. 2023-07-20T19:13:09.504Z
delete_time timestamp Timestamp of when the SQL warehouse was deleted. The value is null if the SQL warehouse is not deleted. 2023-07-20T19:13:09.504Z

Sample queries

The following sample queries are templates. Plug in whatever values make sense for your organization. You can also add alerts to these queries to help you stay informed about changes to your warehouses. See Create an alert.

Use the following sample queries to gain insight into warehouse behavior:

Identify the settings for all active warehouses

This query identifies the settings for all warehouses that are currently active.

USE CATALOG `system`;

SELECT
    warehouse_id,
    warehouse_name,
    warehouse_type,
    warehouse_channel,
    warehouse_size,
    min_clusters,
    max_clusters,
    auto_stop_minutes,
    tags,
    change_time,
    delete_time
FROM
    system.compute.warehouses
QUALIFY
    ROW_NUMBER() OVER (PARTITION BY warehouse_id ORDER BY change_time DESC) = 1
    and delete_time is null;

Which warehouses were created this week?

This query identifies the warehouses that were created in the last seven days.

SELECT
    warehouse_id,
    warehouse_name,
    warehouse_type,
    warehouse_channel,
    warehouse_size,
    min_clusters,
    max_clusters,
    auto_stop_minutes,
    tags,
    change_time as datetime_created,
    delete_time
FROM
    system.compute.warehouses
QUALIFY
    ROW_NUMBER() OVER (PARTITION BY warehouse_id ORDER BY change_time ASC) = 1
    and change_time >= DATE_TRUNC('day', CURRENT_DATE) - INTERVAL 7 days
    and delete_time is null;