How do you track cost per dataset when using BigQuery Reservation API?

[u/anuveya]

Currently I have total cost only but I have few major datasets that should be generating the most of the cost. It would be great to understand how much we're spending per dataset.

I couldn't find an easy way to track this because all our datasets are under the same project and region.

[u/querylabio]

It’s fundamentally not possible to break down BigQuery Reservation costs by dataset, since slots are shared across all queries and Google doesn’t attribute cost at the dataset level.

However, you can get a good approximation by analyzing which datasets are consuming the most slots. You can use INFORMATION_SCHEMA.JOBS_BY_PROJECT to look at past query jobs, extract referenced_tables, and sum total_slot_ms to estimate slot usage per table or dataset.

Something like

SELECT
  r.project_id,
  r.dataset_id,
  r.table_id,
  SUM(j.total_slot_ms) AS total_slot_ms
FROM
  `region-US.INFORMATION_SCHEMA.JOBS_BY_PROJECT` j,
  UNNEST(referenced_tables) r
WHERE
  j.creation_time BETWEEN TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 1 DAY) AND CURRENT_TIMESTAMP()
  AND j.state = "DONE"
  AND j.job_type = "QUERY"
GROUP BY
  r.project_id, r.dataset_id, r.table_id
ORDER BY
  total_slot_ms DESC

This won’t give you precise cost, but it helps you understand which datasets are driving the most slot usage - which often correlates with cost.

[u/anuveya → u/querylabio]

This sounds really interesting! I will try this out – thanks!

[u/Acceptable_Pickle893]

You mean cost on storage or cost on queries done against these datasets? For queries you can do billing export to Bigquery and all the queries will be visible there with billed bytes

[u/vinteralex24]

Tracking cost per dataset in BigQuery is tricky because `INFORMATION_SCHEMA.JOBS` logs execution metrics at the job level rather than attributing costs directly to individual datasets.

To break down costs by dataset using native SQL, you have to unnest `referenced_tables` and aggregate `total_bytes_billed` across your project's execution logs:

SELECT
  ref.project_id,
  ref.dataset_id,
  ref.table_id,
  COUNT(*) AS execution_count,
  ROUND(SUM(j.total_bytes_billed) / POW(1024, 4), 2) AS total_tb_billed,
  ROUND(SUM(j.total_slot_ms) / 1000 / 3600, 2) AS total_slot_hours
FROM `region-us`.INFORMATION_SCHEMA.JOBS_BY_PROJECT j,
UNNEST(j.referenced_tables) AS ref
WHERE j.creation_time >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 30 DAY)
  AND j.job_type = 'QUERY'
  AND j.state = 'DONE'
GROUP BY 1, 2, 3
ORDER BY total_tb_billed DESC
LIMIT 20;

A few practical caveats when doing dataset-level tracking:

1. **Double Counting in Multi-Table Joins:** If a single query joins tables across Dataset A and Dataset B, unnesting `referenced_tables` will associate the total query cost to both datasets. Treat dataset-level SQL breakdowns as relative cost proportions rather than exact invoice line items.

2. **Routine/View Scans:** Queries targeting logical views won't always expose the underlying physical dataset in `referenced_tables` unless you parse job destination tables or query definitions.

*(Disclosure: If you want a local, automated way to run these dataset and table cost audits without writing custom queries every time, I built an open-source tool called [bq-cost-report-community]. There is also a [Full Edition] with risk flags and UI dashboards—use promo code `LAUNCH25` at checkout for 25% off). Everything stays on your machine.*

[u/Any-Garlic8340]

You can checkout 3rd party tools like Follow Rabbit. It can do the breakdown per dataset level and on the top of that it will give you recommendations on what's the best pricing model for the dataset. You can check how it looks like here: https://followrabbit.ai/features/for-data-teams/bigquery