BigQuery records every job it runs in INFORMATION_SCHEMA.JOBS, with the bytes it billed and the slot time it used. Run this in the BigQuery console for the project you want to price. It reads job metadata only, never your tables, and returns your last 30 days as the three numbers above, plus a link that opens this page with them filled in. Nothing is sent to this site unless you open that link, and even then the values stay after the #.
-- Your last 30 days of BigQuery queries as cost calculator inputs:
-- TiB billed a month, average slots while queries run, and for how many hours a day.
-- Reads job metadata only. Change region-us to where your data lives.
-- Scope: this project. For a whole organisation, use JOBS_BY_ORGANIZATION.
DECLARE days INT64 DEFAULT 30;
WITH jobs AS (
SELECT total_bytes_billed, total_slot_ms, start_time, end_time
FROM `region-us`.INFORMATION_SCHEMA.JOBS
WHERE creation_time >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL days DAY)
AND job_type = 'QUERY'
AND state = 'DONE'
-- A script's parent job repeats the bytes and slots of the statements inside it.
AND IFNULL(statement_type, '') != 'SCRIPT'
),
-- Every clock hour in which at least one query was running.
active AS (
SELECT COUNT(DISTINCT hour) AS hours
FROM jobs,
UNNEST(GENERATE_TIMESTAMP_ARRAY(TIMESTAMP_TRUNC(start_time, HOUR), end_time, INTERVAL 1 HOUR)) AS hour
WHERE start_time IS NOT NULL
),
totals AS (
SELECT
-- Scaled from `days` to an average month, 730 hours, as Google bills.
SUM(total_bytes_billed) / POW(1024, 4) * (730 / 24) / days AS tib_per_month,
SUM(total_slot_ms) / (1000 * 3600) AS slot_hours,
-- The calculator takes 1 to 24 hours a day.
LEAST(GREATEST(IFNULL((SELECT hours FROM active) / days, 1), 1), 24) AS hours_per_day
FROM jobs
)
SELECT
ROUND(tib_per_month, 2) AS tib_billed_per_month,
-- Average slots in use during those hours, so slots x hours x days = slot-hours used.
ROUND(SAFE_DIVIDE(slot_hours, hours_per_day * days), 1) AS avg_slots_while_active,
ROUND(hours_per_day, 1) AS active_hours_per_day,
CONCAT(
'https://lp-data.cloud/tools/bigquery-cost-calculator',
'#tib=', FORMAT('%.2f', IFNULL(tib_per_month, 0)),
'&slots=', FORMAT('%.1f', IFNULL(SAFE_DIVIDE(slot_hours, hours_per_day * days), 0)),
'&hours=', FORMAT('%.1f', hours_per_day),
'&via=sql-bigquery'
) AS calculator_url
FROM totals;Run it per project, or swap in JOBS_BY_ORGANIZATION to price everything at once, because commitments are shared across an organisation. If your projects are already on Editions, total_bytes_billed is still recorded, so the same query tells you what on-demand would have cost.