BigQuery Billing - difference between JOBS table and Billing report

[sadeveloper]

I have a question about billing - trying to reconcile the billing amount that we see on the report vs the total bytes billed from JOBS.

For the JOBS, the query I am using (assumption is $5 per TB processed): 

select round(sum(total_bytes_billed)/(1024*1024*1024*1024)*5) from region-us.INFORMATION_SCHEMA.JOBS
where creation_time > current_timestamp() - interval 30 DAY;

For the billing report (https://console.cloud.google.com/billing) : I have filtered the report for last 30 days, selected only BigQuery service (not the BI Engine). Also selected only the relevant project. 

The cost as per billing report shows as more than twice the we are getting from JOBS. What could account for the difference?

[sadeveloper]

I realized I had missed the storage cost. Including the storage, the costs match with the reports. 

Here is the query I used for storage costs, it can be different if you are using logical model.

select round(sum(ACTIVE_PHYSICAL_BYTES)/POW(1024, 3)) active_GB, round(((SUM(ACTIVE_PHYSICAL_BYTES)/ POW(1024, 3)) * 0.04)) monthly_cost_GB, 

round(SUM(LONG_TERM_PHYSICAL_BYTES)/POW(1024, 3)) long_term_GB, round(((SUM(LONG_TERM_PHYSICAL_BYTES)/POW(1024, 3)) * 0.02)) long_term_cost_GB

from region-us.INFORMATION_SCHEMA.TABLE_STORAGE;

[Roderick]

There are a few reasons why the billing report might show a higher cost than you expect.

• The JOBS query might not be capturing all of the data that is being billed. For example, if you are using a managed service like BigQuery Dataproc, the cost of the resources that are used by the service will not be reflected in the JOBS query.
• The billing report might be including costs from other services, such as BigQuery Dataflow or BigQuery ML.
• The billing report might be using a different pricing model than the JOBS query. For example, the billing report might be using a sustained use discount, while the JOBS query might be using the standard pricing model.

If you are not sure why the billing report is showing a higher cost than you expect, you can contact Google Cloud Platform support for assistance.