I’m interested in knowing exactly how much each of my queries are spending on BigQuery. The problem is that the sum of billed bytes for the queries DO NOT add up to the bytes billed in the Billing page. The sum is ~10% less than I see on the Billing Page.
As suggested in the official BigQuery Monitoring page, I tried:
Audit Log: Creating a sink with Cloud Logs and after that summing the totalBilledBytes for each query collected by the logs.
INFORMATION_SCHEMA view: Querying the INFORMATION_SCHEMA.JOBS_BY_PROJECT dataset for the total_billed_bytes:
DECLARE timezone STRING DEFAULT "America/Sao_Paulo";
DECLARE gb_divisor INT64 DEFAULT CAST(POWER(2, 30) AS INT64);
DECLARE tb_divisor INT64 DEFAULT CAST(POWER(2, 40) AS INT64);
DECLARE cost_per_tb_in_dollar INT64 DEFAULT 5;
WITH q AS (
SELECT
DATE(creation_time, timezone) creation_date,
FORMAT_TIMESTAMP("%F %H:%I:%S", creation_time, timezone) as query_time,
job_id,
total_bytes_processed / gb_divisor AS bytes_processed_in_gb,
(total_bytes_processed / tb_divisor)*cost_per_tb_in_dollar AS cost_in_dollar,
project_id,
user_email,
FROM
`region-us`.INFORMATION_SCHEMA.JOBS_BY_PROJECT
WHERE
DATE(creation_time, timezone) = "2023-06-02"
AND project_id = <<my_project>>
ORDER BY
bytes_processed_in_gb DESC
)
SELECT SUM(q.bytes_processed_in_gb)
FROM q
But each of these solutions gave me an answer ~10% smaller from the total BigQuery Analysis I see on Billing (below).
Am I doing something wrong in the calculations? Any idea of what’s missing?