BigQuery Pricing is not working

[LearnGCP]

Hi Team,

I am trying to understand the monthly cost of my query which is running in Bigquery using cloud composer schedule?

My query is showing “This query will process 19.17 GB when run”.

Final output table storage info is showing 12.01 MB in Active Logical bytes.

In BigQuery Pricing calculator using ‘On-Demand”,we need to provide Amount of data queried and Active logical storage.

I have given 19.17GB in Amount of Data queried . But it was showing $0/month.

I have added 12.01MB which is 12.01/1024=0.012 GB

I have 2 questions.

1. How do I get the total cost for my query including the storage cost?
2. It is showing per month cost. Is it on the basis that query will run once in a month?

Thank you for your help.

[kolban]

You will want to study the BigQuery pricing documentation found here in depth.  There are so many permutations its difficult to pick one.  However, for many customers, the simplest model is based on the "on-demand" model.

At this time, your spend will be $6.25 / TB of data scanned to generate query results plus $0.02 / GB / Month for storage.  What you will want to do is estimate your monthly TBs of data scanned and the monthly amount of storage living in your database.  Looking at just one query being run per month is likely not useful.  Remember, that the first 10GB of data is free per month and the first 1TB of data scanned is also free per month.  So plugging in your above numbers does indeed result in a zero cost.

[LearnGCP]

@kolban Thank you for your response. Within our organization, we have various business divisions that utilize Google Cloud Platform (GCP). We are endeavoring to conduct a session for the entire group to enhance their understanding of how to determine the costs associated with individual BigQuery queries. This knowledge will empower them to estimate the cost of each task or SQL statement they execute.

One aspect I need to clarify is the free usage limits for BigQuery. Specifically, I need to understand whether these limits apply to each individual project or to the entire organization. In our case, each business division operates under its own dedicated project. Please confirm.

Can you also confirm whether the "Active logical storage" is the sum of all the table's Active logical bytes or not?

[kolban · Accepted solution]

My understanding is that the free tier is per organization.  So if you have a GCP organization, and there are multiple projects under it, your total BQ spend is rolled up to the organization and, if you are on-demand billing, the 1st TB per month is credited as is the first 10GB of storage.

Storage is associated with a table ... which rolls up to a dataset which rolls up to a project which rolls up to an organization.  Active Logical Storage is the amount of storage (as found associated with a table) currently attributed to that table.  If the data in the table (or partition) has not been modified for 90 consecutive days then the storage is automatically "flagged" as long term storage and the price per month of that storage drops.   If you look at a table, you will see its active logical bytes and that is the total amount of storage (uncompressed) for THAT table by itself.  I don't know what we would mean by the "sum" in that context.  As always, there are permutations.  Google offers you different billing models and you get to choose which one you want to utilize as a function of minimizing costs will maintaining the attributes you want.  For example, you can opt to be billed based on physical storage instead of logical storage.  Physical storage is a charge based on how much data you store on disk ... which will almost always be less than logical storage as Google saves the data compressed.  However, when you opt for physical storage, that will also include storage needed for recovery and time travel where when you pay for logical storage, the additional storage needed for recovery and time travel is not a factor of the cost.

[LearnGCP]

Thank you @kolban . I got the answer .🙂