Warehouses system table reference

Preview

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 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;