Computing Cost of a Query/Job

[mdideles]

Hi BigQuery Community,

I am looking for the correct formula to compute the cost of a job/query in $ amount.  We are asked to show the cost of a query so team will have an idea how much they are contributing to the overall cost and drive them to optimize their jobs/queries.  We have a commitment, and projects are assigned to several reservations.  Can you show what formula should we use to compute the cost for each job?

[kolban · Accepted solution]

Assuming you are using BigQuery Editions and are running on a Capacity Pricing model, I would use INFORMATION_SCHEMA.JOBS table to determine the slot milliseconds consumed by the job.   You could then assume that the price would roughly be:

slot milliseconds / (1000 * 60 * 60) * $0.06

Is that guaranteed to be absolutely correct?  Nope.  It can vary depending on what edition of BigQuery you are using (Standard, Enterprise, Enterprise Plus).  It can vary depending on the mix of your reservation between base line slots and autoscale slots.   It can vary based on idle slot sharing.  I can vary depending on whether or not you have a baseline slot commit and, if so, is it 1year or 3 years.

That said ... the algorithm above will give you "a" number and illustrates that your "cost" of a query is proportional to slot milliseconds used by the query.  Maybe instead of trying to consider an absolute, concentrate on relatives.

[kolban · Accepted solution]

Assuming you are using BigQuery Editions and are running on a Capacity Pricing model, I would use INFORMATION_SCHEMA.JOBS table to determine the slot milliseconds consumed by the job.   You could then assume that the price would roughly be:

slot milliseconds / (1000 * 60 * 60) * $0.06

Is that guaranteed to be absolutely correct?  Nope.  It can vary depending on what edition of BigQuery you are using (Standard, Enterprise, Enterprise Plus).  It can vary depending on the mix of your reservation between base line slots and autoscale slots.   It can vary based on idle slot sharing.  I can vary depending on whether or not you have a baseline slot commit and, if so, is it 1year or 3 years.

That said ... the algorithm above will give you "a" number and illustrates that your "cost" of a query is proportional to slot milliseconds used by the query.  Maybe instead of trying to consider an absolute, concentrate on relatives.

[mdideles]

Thanks, @kolban. this is very helpful.

for the On-demand, is it computed based off total_bytes_billed?  Like (total_bytes_billed /1024/1024/1024/104) * $6.25

[kolban · Accepted solution]

Precisely .... but I usually write:

total_bytes_billed / (1024*1024*1024*1024) * 6.25

[mdideles]

thanks, @kolban.  I think, I got answers to my question.

[DianaCastro314]

I'm having the same question but with an addition, in my case BigQuery Editions and running on a Capacity Pricing model (Enterprise). So, my formula would be slot milliseconds / (1000 * 60 * 60) * $0.06. But I have doubts about the source to get slots for the formula.

I have differences when querying INFORMATION_SCHEMA.JOBS_TIMELINE_BY_ORGANIZATION and INFORMATION_SCHEMA.JOBS_BY_ORGANIZATION, the first one in some jobs give me more slots than the second one. Reading documentation for costs mostly posts uses INFORMATION_SCHEMA.JOBS_BY_ORGANIZATION, but for me INFORMATION_SCHEMA.JOBS_TIMELINE_BY_ORGANIZATION shows me the exact slots that Capacity Management (slots adminitration) shows. So, whats the source of true for costs?