Skip to main content

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 nameData typeDescriptionExample
warehouse_idstringThe ID of the SQL warehouse.123456789012345
workspace_idstringThe ID of the workspace where the warehouse is deployed.123456789012345
account_idstringThe ID of the Databricks account.7af234db-66d7-4db3-bbf0-956098224879
warehouse_namestringThe name of the SQL warehouse. My Serverless Warehouse
warehouse_typestringThe type of SQL warehouse. Possible values are CLASSIC, PRO, and SERVERLESS.SERVERLESS
warehouse_channelstringThe channel of the SQL warehouse. Possible values are CURRENT and PREVIEW.CURRENT
warehouse_sizestringThe 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_clustersintThe minimum number of clusters permitted.1
max_clustersintThe maximum number of clusters permitted.5
auto_stop_minutesintThe number of minutes before the SQL warehouse auto-stops due to inactivity.35
tagsmapTags for the SQL warehouse.{"budget":"research"}
change_timetimestampTimestamp of change to the SQL warehouse definition.2023-07-20T19:13:09.504Z
delete_timetimestampTimestamp 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.

SQL
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.

SQL
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;
Last updated on