Billable usage system table reference

Preview

This feature is in Public Preview.

This article provides an overview of the billable usage system table, including the schema and example queries. With system tables, your account’s billable usage data is centralized and routed to all regions, so you can view your account’s global usage from whichever region your workspace is in.

Billable usage table schema

The billable usage system table is located at system.billing.usage and uses the following schema:

Column name

Data type

Description

Example

record_id

string

Unique ID for this record

11e22ba4-87b9-4cc2-9770-d10b894b7118

account_id

string

ID of the account this report was generated for

23e22ba4-87b9-4cc2-9770-d10b894b7118

workspace_id

string

ID of the Workspace this usage was associated with

1234567890123456

sku_name

string

Name of the SKU

STANDARD_ALL_PURPOSE_COMPUTE

cloud

string

Cloud this usage is relevant for. Possible values are AWS, AZURE, and GCP.

AWS, AZURE, or GCP

usage_start_time

timestamp

The start time relevant to this usage record

2023-01-09 10:00:00.000

usage_end_time

timestamp

The end time relevant to this usage record

2023-01-09 11:00:00.000

usage_date

date

Date of the usage record. This field can be used for faster aggregation by date.

2023-01-01

custom_tags

map

Tags applied by the users to this usage

{ “env”: “production” }

usage_unit

string

Unit this usage is measured in

DBU

usage_quantity

decimal

Number of units consumed for this record

259.2958

usage_metadata

struct

System-provided metadata about the usage, including IDs for compute resources and jobs (if applicable). See Analyze usage metadata.

{cluster_id: null; instance_pool_id: null; notebook_id: null; job_id: null; node_type: null}

identity_metadata

struct

Used to attribute serverless records in Azure and AWS. The value will be null.

null

record_type

string

Whether the record is a correction. Possible values are ORIGINAL, RETRACTION, and RESTATEMENT.

ORIGINAL

ingestion_date

date

Date when the record was ingested into the usage table

2024-01-01

Analyze usage metadata

The values in usage_metadata tell you about the resources involved in the usage record.

Value

Data type

Description

cluster_id

string

ID of the cluster associated with the usage record

instance_pool_id

string

ID of the instance pool associated with the usage record

node_type

string

The instance type of the compute resource

job_id

string

ID of the job associated with the usage record

job_run_id

string

ID of the job run associated with the usage record

notebook_id

string

ID of the notebook associated with the usage record

dlt_pipeline_id

string

ID of the Delta Live Tables pipeline associated with the usage record

Note

In rare cases, job_run_id isn’t populated for long-running jobs whose compute started running before Databricks began capturing the job_run_id metadata. Restart the job’s compute to begin recording the job_run_id.

Find a job or notebook in the UI using the job_id or notebook_id

These instructions explain how to pull up a specific job or notebook in the UI based on its ID.

To find a job in the UI based on its job_id:

  1. Copy the job_id from the usage record. For this example, assume the ID is 700809544510906.

  2. Navigate to the Workflows UI in the same Databricks workspace as the job.

  3. Ensure the Only jobs owned by me filter is unchecked.

  4. Paste the ID (700809544510906) into the Filter jobs search bar.

To find a notebook in the UI based on its notebook_id, use the following instructions:

  1. Copy the notebook_id from the usage record. For this example, assume the ID is 700809544510906.

  2. Navigate to the Workspaces UI in the same Databricks workspace as the notebook.

  3. click any notebook you see.

  4. After you’ve opened the notebook, examine the URL in the browser address bar. It should look like https://<account-console-url>/?o=<workspace ID>#notebook/<notebook ID>/command/<command ID>.

  5. In the browser address bar, replace the notebook ID with the ID you copied in the first step, then delete everything after the notebook ID. It should look like https://<account-console-url>/?o=<workspace ID>#notebook/700809544510906.

  6. After you pull up the notebook, you can click the Share button to view the notebook owner.

Sample queries

You can use the following sample queries to answer common questions about billable usage:

What is the daily trend in DBU consumption?

SELECT usage_date as `Date`, sum(usage_quantity) as `DBUs Consumed`
  FROM system.billing.usage
WHERE sku_name = "STANDARD_ALL_PURPOSE_COMPUTE"
GROUP BY usage_date
ORDER BY usage_date ASC

How many DBUs of each SKU have been used throughout this month?

SELECT sku_name, usage_date, sum(usage_quantity) as `DBUs`
    FROM system.billing.usage
WHERE
    month(usage_date) = month(NOW())
    AND year(usage_date) = year(NOW())
GROUP BY sku_name, usage_date

How much of each SKU did a workspace use on June 1?

Be sure to replace workspace_id with your actual workspace ID.

SELECT sku_name, sum(usage_quantity) as `DBUs consumed`
FROM system.billing.usage
WHERE workspace_id = 1234567890123456
AND usage_date = "2023-06-01"
GROUP BY sku_name

Note

This query returns one row per unique SKU ID used in the workspace on the chosen date.

Which jobs consumed the most DBUs?

SELECT usage_metadata.job_id as `Job ID`, sum(usage_quantity) as `DBUs`
FROM system.billing.usage
WHERE usage_metadata.job_id IS NOT NULL
GROUP BY `Job ID`
ORDER BY `DBUs` DESC

How much usage can be attributed to resources with a specific tag?

You can break down costs in various ways. This example shows you how to break down costs by a custom tag. Be sure to replace the custom tag’s key and value in the query.

SELECT sku_name, usage_unit, SUM(usage_quantity) as `DBUs consumed`
FROM system.billing.usage
WHERE custom_tags.{{key}} = "{{value}}"
GROUP BY 1, 2

Show me the SKUs where usage is growing

SELECT after.sku_name, before_dbus, after_dbus, ((after_dbus - before_dbus)/before_dbus * 100) AS growth_rate
FROM
(SELECT sku_name, sum(usage_quantity) as before_dbus
    FROM system.billing.usage
WHERE usage_date BETWEEN "2023-04-01" and "2023-04-30"
GROUP BY sku_name) as before
JOIN
(SELECT sku_name, sum(usage_quantity) as after_dbus
    FROM system.billing.usage
WHERE usage_date BETWEEN "2023-05-01" and "2023-05-30"
GROUP BY sku_name) as after
where before.sku_name = after.sku_name
SORT by growth_rate DESC

What is the usage trend of All Purpose Compute (Photon)?

SELECT sku_name, usage_date, sum(usage_quantity) as `DBUs consumed`
    FROM system.billing.usage
WHERE year(usage_date) = year(CURRENT_DATE)
AND sku_name = "ENTERPRISE_ALL_PURPOSE_COMPUTE_(PHOTON)"
AND usage_date > "2023-04-15"
GROUP BY sku_name, usage_date

What is the DBU consumption of a materialized view or streaming table?

To determine the DBU usage and SKU for a specific materialized view or streaming table, you need the associated Pipeline ID (dlt_pipeline_id). Find the Pipeline ID in the Details tab when viewing the relevant materialized view or streaming table in Catalog Explorer.

SELECT
  sku_name,
  usage_date,
  SUM(usage_quantity) AS `DBUs`
FROM
  system.billing.usage
WHERE
  usage_metadata.dlt_pipeline_id = "113739b7-3f45-4a88-b6d9-e97051e773b9"
  AND usage_start_time > "2023-05-30"
GROUP BY
  ALL