BigQuery near real-time cost anomaly for on-demand pricing model
BigQuery provides native views such as INFORMATIONSCHEMA.JOBSBY_ORGANIZATION(https://cloud.google.com/bigquery/docs/information-schema-jobs-by-organization) which contain near real-time metadata about all jobs submitted in the organization or project.
Late last year, I developed a custom solution for a local customer who had a huge cost spike because of an application bug.
The idea is to run a Cloud Function job that check the cost of BQ jobs in the past hour (can be user defined). If it exceeds a threadshold (let's say $100), it will write a log to Cloud Logging, which then trigger an alert based on a custom Cloud Logging metric.
This solution is doable because Google charges on-demand BQ users $6.25 per TiB data scanned. We can calculate it like this: ROUND(SUM(totalbytesbilled) * 6.25 / POWER(2, 40), 2) as estimated_cost.
Details and discussion on Slack
https://doitintl.slack.com/archives/C010V5SE3T5/p1738026192438959
Derrick Qin
shared this idea
-
Zaar Hai commented
A bit of a background:
We have a customer that, due to a wrongly architected solution, started to accumulate BQ spend at the rate of $100,000 PER DAY (yes, per day). Our billing data-based anomaly detection caught it after about 25 hours, plus, it took the customer some more time to assess and react. But the time they closed the tap, they were $200,000 out of pocket in just under days.This is how the necessity of real-time solution came in.