Compute system tables 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.
This article provides you with a reference guide for the compute system tables. You can use these tables to monitor the activity and metrics of all-purpose and jobs compute in your account:
clusters
: Records compute configurations in your account.node_types
: Includes a single record for each of the currently available node types, including hardware information.node_timeline
: Includes minute-by-minute records of your compute’s utilization metrics.
Cluster table schema
Note
This table is only available in us-east1
and asia-southeast1
.
The cluster table is a slow-changing dimension table that contains the full history of compute configurations over time for all-purpose and jobs compute.
The clusters system table is located at system.compute.clusters
and has the following schema:
Column name |
Data type |
Description |
Example |
---|---|---|---|
|
string |
ID of the account where this cluster was created. |
|
|
string |
ID of the workspace where this cluster was created. |
|
|
string |
ID of the cluster for which this record is associated. |
|
|
string |
User defined name for the cluster. |
|
|
string |
Username of the cluster owner. Defaults to the cluster creator, but can be changed through the Clusters API. |
|
|
timestamp |
Timestamp of the change to this compute definition. |
|
|
timestamp |
Timestamp of when the cluster was deleted. The value is |
|
|
string |
Driver node type name. This matches the instance type name from the cloud provider. |
|
|
string |
Worker node type name. This matches the instance type name from the cloud provider. |
|
|
bigint |
Number of workers. Defined for fixed-size clusters only. |
|
|
bigint |
The set minimum number of workers. This field is valid only for autoscaling clusters. |
|
|
bigint |
The set maximum number of workers. This field is valid only for autoscaling clusters. |
|
|
bigint |
The configured autotermination duration. |
|
|
boolean |
Autoscaling disk enablement status. |
|
|
map |
User-defined tags for the cluster (does not include default tags). |
|
|
string |
Indicates the creator for the cluster: |
|
|
array |
Set of paths for init scripts. |
|
|
struct |
AWS specific settings. This field will be empty. |
|
|
struct |
Azure specific settings. This field will be empty. |
|
|
struct |
GCP specific settings. |
|
|
string |
Instance pool ID if the driver is configured on top of an instance pool. |
|
|
string |
Instance Pool ID if the worker is configured on top of an instance pool. |
|
|
string |
The Databricks Runtime of the cluster. |
|
|
timestamp |
Timestamp of change to the compute definition. |
|
|
date |
Change date. Used for retention. |
|
Node types table schema
Note
This table is only available in us-east1
and asia-southeast1
.
The node type table captures the currently available node types with their basic hardware information. The node type system table is located at system.compute.node_types
and has the following schema:
Column name |
Data type |
Description |
Example |
---|---|---|---|
|
string |
ID of the account where this cluster was created. |
|
|
string |
Unique identifier for node type. |
<driver type> |
|
double |
Number of vCPUs for the instance. |
|
|
long |
Total memory for the instance. |
|
|
long |
Number of GPUs for the instance. |
|
Node timeline table schema
The node timeline table captures node-level resource utilization data at minute granularity. Each record contains data for a given minute of time per instance.
The node timeline system table is located at system.compute.node_timeline
and has the following schema:
Column name |
Data type |
Description |
Example |
---|---|---|---|
|
string |
ID of the account where this compute resource is running. |
|
|
string |
ID of the workspace where this compute resource is running. |
|
|
string |
ID of the compute resource. |
|
|
string |
ID for the specific instance. |
|
|
timestamp |
Start time for the record in UTC. |
|
|
timestamp |
End time for the record in UTC. |
|
|
boolean |
Whether the instance is a driver or worker node. |
|
|
double |
Percentage of time the CPU spent in userland. |
|
|
double |
Percentage of time the CPU spent in the kernel. |
|
|
double |
Percentage of time the CPU spent waiting for I/O. |
|
|
double |
Percentage of the compute’s memory that was used during the time period (including memory used by background processes running on the compute). |
|
|
double |
Percentage of memory usage attributed to memory swap. |
|
|
bigint |
The number of bytes sent out in network traffic. |
|
|
bigint |
The number of received bytes from network traffic. |
|
|
map |
The disk utilization grouped by mount point. This is ephemeral storage provisioned only while the compute is running. |
|
|
string |
The name of the node type. This will match the instance type name from the cloud provider. |
<driver type> |
Known limitations
Compute resources that were marked deleted before October 23, 2023 do not appear in the clusters table. This might result in joins from the
system.billing.usage
table not matching records in the clusters table. All active compute resources have been backfilled.These tables only includes records for all-purpose and jobs compute. They do not contain records for serverless compute, Delta Live Tables compute, or SQL warehouses.
Nodes that ran for less than 10 minutes might not appear in the
node_timeline
table.
Sample queries
You can use the following sample queries to answer common questions:
Note
Some of these examples join the cluster table with the system.billing.usage
table. Since billing records are cross-regional and cluster records region-sepcific, billing records only match cluster records for the region in which you are querying. To see records from another region, please execute the query in that region.
Join cluster records with the most recent billing records
This query can help you understand spending over time. Once you update the usage_start_time
to the most current billing period, it grabs the most recent updates to the billing records to join into clusters data.
Each record is associated with the cluster owner during that particular run. So, if the cluster owner changes, costs will roll up to the correct owner based on when the cluster was used.
SELECT
u.record_id,
c.cluster_id,
c.owned_by,
c.change_time,
u.usage_start_time,
u.usage_quantity
FROM
system.billing.usage u
JOIN system.compute.clusters c
JOIN (SELECT u.record_id, c.cluster_id, max(c.change_time) change_time
FROM system.billing.usage u
JOIN system.compute.clusters c
WHERE
u.usage_metadata.cluster_id is not null
and u.usage_start_time >= '2023-01-01'
and u.usage_metadata.cluster_id = c.cluster_id
and date_trunc('HOUR', c.change_time) <= date_trunc('HOUR', u.usage_start_time)
GROUP BY all) config
WHERE
u.usage_metadata.cluster_id is not null
and u.usage_start_time >= '2023-01-01'
and u.usage_metadata.cluster_id = c.cluster_id
and u.record_id = config.record_id
and c.cluster_id = config.cluster_id
and c.change_time = config.change_time
ORDER BY cluster_id, usage_start_time desc;
Attribute costs to the cluster owner
If you are looking to reduce compute costs, you can use this query to find out which cluster owners in your account are using the most DBUs.
SELECT
u.record_id record_id,
c.cluster_id cluster_id,
max_by(c.owned_by, c.change_time) owned_by,
max(c.change_time) change_time,
any_value(u.usage_start_time) usage_start_time,
any_value(u.usage_quantity) usage_quantity
FROM
system.billing.usage u
JOIN system.compute.clusters c
WHERE
u.usage_metadata.cluster_id is not null
and u.usage_start_time >= '2023-01-01'
and u.usage_metadata.cluster_id = c.cluster_id
and c.change_time <= u.usage_start_time
GROUP BY 1, 2
ORDER BY cluster_id, usage_start_time desc;
Identify the compute resources with the highest average utilization and peak utilization
Identify the all-purpose and jobs compute that have the highest average CPU utilization and the highest peak CPU utilization.
SELECT
distinct cluster_id,
driver,
avg(cpu_user_percent + cpu_system_percent) as `Avg CPU Utilization`,
max(cpu_user_percent + cpu_system_percent) as `Peak CPU Utilization`,
avg(cpu_wait_percent) as `Avg CPU Wait`,
max(cpu_wait_percent) as `Max CPU Wait`,
avg(mem_used_percent) as `Avg Memory Utilization`,
max(mem_used_percent) as `Max Memory Utilization`,
avg(network_received_bytes)/(1024^2) as `Avg Network MB Received per Minute`,
avg(network_sent_bytes)/(1024^2) as `Avg Network MB Sent per Minute`
FROM
node_timeline
WHERE
start_time >= date_add(now(), -1)
GROUP BY
cluster_id,
driver
ORDER BY
3 desc;