Billable usage system table reference

Preview

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.

The billable usage system table is located at system.billing.usage.

Billable usage table schema

The billable usage system table 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

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