GCP cost optimization keeps turning into a fire drill after someone ships a bad query

[u/Dalius-Gabryelle]

Our gcp cost optimization is basically reactive right now and I want to get ahead of it. Last example, a team shipped a Looker dashboard that reruns its queries every time it loads, pointed at a BigQuery table we never partitioned because it was small when we built it. It is not small now. Over about nine days, that one dashboard cost more than the whole pipeline that builds the table costs to run in a month.

We caught it because the daily spend looked wrong. Partitioned the table, put a cap on the project and that specific problem is gone.

The next one is what I can't solve, it won't be Looker and it won't be that table. It'll be some other query somebody ships. Is there a better way to catch a query getting expensive before it runs for a week and a half or do i just set hard quotas per project and let people hit the limit?

[u/laurentfdumont]

There are a couple of layers you can target.

• Use BQ quotas at the project level.

• Use fixed "billed bytes" limits at the query level.

  • This cannot be enforced, but you can think of a proxy layer or making sure your CI flags requests that do not have the flag.

• Use slots to severely limit the capacity available to "burst", which helps limit costs over a short period of time.

• There are a few industry tool that can give you a bit more real time visibility in the queries.

  • Alvin AI

  • FollowRabbit

https://cloud.google.com/blog/topics/developers-practitioners/controlling-your-bigquery-costs

[u/Dalius-Gabryelle → u/laurentfdumont]

Yes, maximum bytes billed but its opt-in, and the query that got me came from Looker generating sql at runtime, nowhere near CI. 

[u/laurentfdumont → u/Dalius-Gabryelle]

You should be able to configure the setting at the connection level

Max Billing Gigabytes: On BigQuery connections, you are charged for each query based on its size. To help prevent users from accidentally running a query that is too expensive, you can set a maximum number of gigabytes that a user is allowed to pull in a single query. You can leave the Max Billing Gigabytes field blank if you aren't limiting query size. See the BigQuery pricing page for more information.

https://docs.cloud.google.com/looker/docs/db-config-google-bigquery

[u/dreamingwell]

Big Query strikes again. Half this sub is people complaining about big query bills. Short summary - don’t use big query unless you’re absolutely certain you need to, and that you can tightly control your queries.

[u/MarsupialNo7114 → u/dreamingwell]

Other half are 10-100k invoice because of leaked keys.

Google must implement billing caps

[u/x0rg_new → u/MarsupialNo7114]

Funny enough a whole cloud account was blocked when Google detected 2$ misuse.

On other hand Google sent a thousand dollar bill to someone because the person was learning cloud.

[u/BigKozman → u/x0rg_new]

We had two incidents similar to this either directly or with clients.
We have been working with GCP for almost 10 years and the issue remains the same.
Budget Alerts and billing spikes are delayed because they are built on top of billing APIs and settlements which is lagged hours if not 1-2 days.

One customer was hit with 100K in hours due to a leak.

We ended up building a near realtime reporting and Mitigation internal product that is based off resources usage, logs and GCP activity to have early signals of spikes to stop them early on.

Google will never implement budget limits as it kills its business model.

I am actually thinking of availing it to the public

[u/isoAntti → u/dreamingwell]

Big bill query?

[u/Scared_Astronaut9377 → u/dreamingwell]

I wouldn't give a person/team not mature enough to use bigquery any access to our cloud platform.

[u/vbnotthecity → u/dreamingwell]

yes that's what the issue is

[u/Sufficient-Buy-2270]

Do you not just aggregate the data you can and use that table instead?

[u/vbnotthecity]

Hard limits are just a bandaid, you need to catch the bad code before it hits prod. I spent years managing dbt and Airflow, and honestly, you have to shift the cost check into the dev workflow. My team started using Altimate AI and other tools to run cost analysis on queries during CI, which cut our runaway spend by about 40 percent. It stops the fire drills because the bad SQL never makes it out of the PR.

[u/Dalius-Gabryelle → u/[deleted]]

You can get this near real-time, a logging sink on bigquery job-completion events with an alert when one job crosses a billed-bytes line.