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

0
votes

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?

2
google-cloud-platformgoogle-bigqueryexportbilling
For this kind of issue best advice is to approach Gcp billing team for inspection or open a case with them as due to security of your project no one could have best insight of your issue in billing. cloud.google.com/support/billing – Neelam
Are you removing any account credit? Sometimes the billing reports include this by default? – Ben P
@BenP - no, I'm not. – Dmitri Ilin
@Neelam - thanks for advice. Will check that – Dmitri Ilin

2 Answers

1
votes

This is because in GCP billing report, the data is shown using Pacific Time Zone, while the data in BigQuery is using UTC. To match both numbers, try the following WHERE clause in BigQuery:

WHERE usage_end_time >= '2020-10-9 00:00:00 America/Los_Angeles' AND usage_end_time < '2020-10-10 00:00:00 America/Los_Angeles'

You can find more information here. The documentation says:

Note: Time periods in the Cloud Billing report use the Pacific Time Zone, and observe daylight saving time shifts.

0
votes

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;