Costs in GCP billing report and costs exported to GBQ do not match

Question

I expect that costs I see in billing reports and the very same costs I export to BigQuery via "Billing export" option must match. But as far as I see, there's a strong discrepancy between figures in reports.

I used these instructions for billing export/monitoring.

https://medium.com/google-cloud/visualize-gcp-billing-using-bigquery-and-data-studio-d3e695f90c08

Yes, I made it yesterday, so, probably there's a certain time lag between actual report and exported data. But interesting thing that exported figures show MORE costs than the billing report. How can that be?

Any ideas?

Answer 1

To get a date range to match exactly you have to search for usage_start_time for both the beginning and ending of the period you are looking at. To get invoice month reports to match your BQ results you have to remove the usage_start_time and only look for invoice_month to match. I use the ancestry_numbers field to organize the output by folder structure, so that field can be omitted if not needed.

--Exact match for date range:
SELECT
  project.name,
  sku.description,
  project.ancestry_numbers,
  SUM(usage.amount) as usage, 
  sum(cost) as total_cost,
  SUM(IFNULL((SELECT SUM(c.amount) FROM UNNEST(credits) c), 0)) as total_credits
  FROM <billing_acct_ID_here>
  WHERE DATE(usage_start_time, "America/Los_Angeles") >= DATE("2022-02-01") 
  AND DATE(usage_start_time, "America/Los_Angeles") <= DATE("2022-02-28")
  GROUP BY 1, 2, 3
  ORDER BY 1;

--Exact match for monthly invoice data:
SELECT
  project.name,
  sku.description,
  SUM(usage.amount) as usage, 
  sum(cost) as total_cost,
  SUM(IFNULL((SELECT SUM(c.amount) FROM UNNEST(credits) c), 0)) as total_credits
  FROM <billing_acct_ID_here>
  WHERE invoice.month = "202202"
  GROUP BY 1, 2
  ORDER BY 1;