Billable usage system table reference

  • Article

Important

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. Possible values include DBUs. 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 System-provided metadata about the identities involved in the usage. See Analyze identity metadata. {run_as: example@email.com}
record_type string Whether the record is a correction. Possible values are ORIGINAL, RETRACTION, and RESTATEMENT. ORIGINAL
ingestion_date date Date the record was ingested into the usage table. 2024-01-01
billing_origin_product string The product that originated the usage. Some products can be billed as different SKUs. For possible values, see View information about the product associated with the usage. JOBS
product_features struct Details about the specific product features used. For possible values, see Product features.
usage_type string The type of usage attributed to the product or workload for billing purposes. Possible values are COMPUTE_TIME, COMPUTE_SLOT, STORAGE_SPACE, NETWORK_BYTES, API_CALLS, TOKEN, or GPU_TIME. STORAGE_SPACE

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

View information about the product associated with the usage

Some Databricks products are billed under the same shared SKU. To help you differentiate usage, the billing_origin_product and product_features columns provide more insight into the specific product and features associated with the usage.

The billing_origin_product column shows the Databricks product associated with the usage record. The values include:

  • JOBS
  • DLT
  • SQL
  • ALL_PURPOSE
  • MODEL_SERVING
  • INTERACTIVE
  • MANAGED_STORAGE
  • VECTOR_SEARCH
  • LAKEHOUSE_MONITORING
  • PREDICTIVE_OPTIMIZATION
  • ONLINE_TABLES

The product_features column is an object containing information about the specific product features used and includes the following key/value pairs:

  • jobs_tier: values include LIGHT, CLASSIC, or null
  • sql_tier: values include CLASSIC, PRO, or null
  • dlt_tier: values include CORE, PRO, ADVANCED, or null
  • is_serverless: values include true or false, or null
  • is_photon: values include true or false, or null
  • serving_type: values include MODEL, GPU_MODEL, FOUNDATION_MODEL, FEATURE, or null

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