Labels, billing export and cost attribution in BigQuery

How to find out which team, pipeline or dashboard spends your BigQuery money. Job labels reach billing data, dataset labels reach only storage billing, table labels never do, and the billing export to BigQuery lets you query costs by label.

A BigQuery invoice says how much the project spent on queries and storage, not who spent it. Two tools close that gap. Labels are key-value tags (team:growth, pipeline:nightly-orders) attached to jobs, datasets and reservations. Billing export writes your detailed Cloud Billing data into a BigQuery table, labels included, so you can query costs with SQL. Where a label ends up in the billing data depends on what it’s attached to, and that is where cost attribution usually goes wrong.

Which labels reach the bill

So to attribute query cost, label the jobs. Labelling the dataset a team owns doesn’t tell you who queried it, and table labels are only for organizing.

Labelling jobs

From bq:

bq query --nouse_legacy_sql --label=team:growth --label=dashboard:weekly-kpis \
  'SELECT ...'

In the API, set configuration.labels on the job. Client libraries, dbt, Dataform, Airflow operators and most BI tools have a setting for job labels; use it so their queries are attributable.

In a script or session, SET @@query_label = 'team:growth,dashboard:weekly-kpis' labels every query that follows.

We ran a query with two labels; INFORMATION_SCHEMA.JOBS_BY_USER returned them as an array of key-value structs:

SELECT
  job_id,
  (SELECT STRING_AGG(CONCAT(l.key, '=', l.value), ',' ORDER BY l.key)
   FROM UNNEST(labels) AS l) AS labels
FROM `region-us`.INFORMATION_SCHEMA.JOBS_BY_USER
WHERE creation_time > TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 1 HOUR);
-- label_26373 | dashboard=weekly-kpis,team=growth

Label rules: up to 64 labels per resource; keys up to 63 characters, starting with a lowercase letter; keys and values only lowercase letters, digits, underscores and dashes (international characters are allowed). A job’s labels can’t be changed once it is submitted. Querylab.io’s Query history matches labels in its search and has a Labels quick filter.

Cost by label from INFORMATION_SCHEMA

For on-demand queries, INFORMATION_SCHEMA.JOBS already has everything needed, no billing export required:

SELECT
  (SELECT value FROM UNNEST(labels) WHERE key = 'team') AS team,
  ROUND(SUM(total_bytes_billed) / POW(1024, 4) * 6.25, 2) AS approx_usd
FROM `region-us`.INFORMATION_SCHEMA.JOBS_BY_PROJECT
WHERE creation_time >= TIMESTAMP_TRUNC(CURRENT_TIMESTAMP(), MONTH)
  AND job_type = 'QUERY'
  AND statement_type != 'SCRIPT'
GROUP BY team
ORDER BY approx_usd DESC;

Jobs without the label show up as NULL, which is the first thing to fix. See Monitoring jobs and costs.

Billing export to BigQuery

INFORMATION_SCHEMA covers one project and one region and has no prices. The Cloud Billing export covers every project on the billing account, every service, with the actual cost after discounts. Turn it on in the Cloud Billing console (Billing export, BigQuery export); it writes to a table named gcp_billing_export_v1_<BILLING_ACCOUNT_ID> in a dataset you choose, several times a day.

It isn’t retroactive beyond the start of the previous month: turn it on early, before you need the history.

BigQuery cost per team and SKU this month, from the standard export:

SELECT
  (SELECT value FROM UNNEST(labels) WHERE key = 'team') AS team,
  sku.description AS sku,
  ROUND(SUM(cost) + SUM(IFNULL((SELECT SUM(c.amount) FROM UNNEST(credits) AS c), 0)), 2) AS net_cost
FROM mydataset.gcp_billing_export_v1_XXXXXX_XXXXXX_XXXXXX
WHERE service.description = 'BigQuery'
  AND usage_start_time >= TIMESTAMP_TRUNC(CURRENT_TIMESTAMP(), MONTH)
GROUP BY team, sku
ORDER BY net_cost DESC;

Credits (the free tier, committed-use discounts, promotions) are negative amounts in credits, so add them to cost for the net figure. The SKU names separate on-demand analysis, storage (active and long-term, logical and physical), streaming and the editions’ slot SKUs. The export table is itself billed like any table when you query it, so filter on usage_start_time and select only the columns you need.

What can’t be attributed this way

• Capacity pricing. Slots are billed per reservation, not per job. To split a reservation’s cost, use each job’s total_slot_ms from INFORMATION_SCHEMA.JOBS as the share, or give teams separate reservations with labels. See Reservations.
• Storage by table. Use INFORMATION_SCHEMA.TABLE_STORAGE for bytes per table, and multiply by the storage prices. See Storage pricing.
• Cached results cost nothing and don’t appear in billing at all.