Billable usage system table reference

  • Article

Important

This feature is in Public Preview.

This article provides you with 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.

Usage is aggregated hourly, so each record in system.billing.usage represents an hour of billable usage.

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). {cluster_id: 12345; instance_pool_id: null; warehouse_id: null; job_id: null; node_type: 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 certain tag?

You can break down costs in various ways. This example shows you a way 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