Limit BQ Cost

dash7 · 04-24-2023 08:23 AM

I want to test BQ. I created a test project for this purpose. I want to make that I do not exceed the capacity included in the free tier. I know that I will have to set quota IAM & Admin (https://cloud.google.com/bigquery/quotas).

Is there a service which I can set a quota to that would make sure that no cost will occur? The list of services is rather extensive. Or can you point me to the document that explains which quota will restrict BQ to the free tier. Thanks!

kolban

I am not at all authoritative in this area so please do NOT take this as a formal Google response.

I think of quotas as "guard rails" to prevent accidental over-usage. For example, if I wrote a program that makes an expensive query and accidentally put the API call in a tight loop, I could rack up a bill quite quickly. The quota mechanism should then kick-in and block my app. I think of it like a "fuse" in an electrical fuse box. However, just like a fuse ... I don't think of quotas as a mechanism for "governing" my normal usage. If what you want to do is constrain your monthly spend, I'd suggest setting up a budget alert. If your spend exceeds a threshold, then an alert can be fired which can be an email or a pub/sub message.

For example ... if the spend is > $1 send me an email or send a message to pub/sub which triggers the automated disabling of a service.

See: https://cloud.google.com/billing/docs/how-to/budgets

When I look at the quota restrictions on BigQuery, I seem to see that there are many in various different dimensions ... however ... none of them apply to "cost" or "spend". For example, and using my electrical fuse as an analogy again, a fuse will blow if the amperage through it is exceeded ... eg. a 10 amp fuse. However, there is no fuse that says "Blow if you have spent more than $50 this month". The latter would be the billing alert.

dash7

Hi kolban and thanks so much for your reply!

I really like your analogy of quota being a "fuse" preventing accidents. That is exactly what I am looking for.

I have already set up a budget alert! What I am now looking for is something like "a general fuse" to prevent accidents caused by my errors!

I think that the BQ free tier has the following monthly limits https://cloud.google.com/bigquery/pricing#free-tier

- Storage: 10GB

- Queries: 1TB processed

- BQ ML: The first 10 GB of data processed by queries that contain CREATE MODEL statements

- BI Engine: 1GB storage for Looker (since I don't use Looker I guess I can not cross this limit anyways?)

I am overwhelmed by the options available in the quotas list for BQ. I don't want to make a costly mistake that will end up on my credit card! So what I am looking for is the one or the couple of quotas I need to set to avoid cost being generated.

Thank you very much!

kolban

I don't think we are going to find a quota that will shutdown your BigQuery usage based on achieving a per month consumption. For example ... I am sensing that you want to use BigQuery but keep the cost to zero by constraining your usage to not exceed the free tier limits. If we look at the free tier allotment, I think we find that Google gives you 1TB of processed data per month. If we look at the quotas, we see that they constrain in a variety of dimensions, but neither total monthly cost nor consumption per month are available. One possibility that comes to mind, would be to take your monthly allotment ... 1TB ... and divide that by the maximum number of days in a month (31) which gives us ~32GB/day. If my arithmetic is sound, then you could set your quota to allow no more than 32GB of queries per day and that should result in no more than 1TB/month. However, that is likely to over-constrain you. For example, if you did no BigQuery queries until the last day of the month where you could have 1TB available, your quota constraint would still limit you to only 32GB.

Through the billing dashboard and through quotas, Google gives you the information for you to track your spend and ensure that any given run of your application "doesn't go crazy" ... Using the quotas ... you can cap your own "daily" spend or cap your spend per query ... but the macro spend (what am I spending in a month), isn't capped through quota. The responsibility falls to you to spend to your own limits.

databaseman

Would it help to update " Maximum bytes billed field" ?

kolban

That is a nice find ... here is a link to the docs.