How to NOT get a $70,000 BigQuery Bill

When you are using pay-per-use services like Google BigQuery, you are working with explosives. A single misstep can trigger massive, unexpected charges that blow through budgets in minutes.

A couple of months ago, I stumbled upon this X post from someone who received a $70,000 Firebase bill overnight:

This nightmare scenario happens more often than cloud providers care to admit, affecting organizations of all sizes. The combination of pay-per-query pricing models and massive datasets creates a perfect storm where innocent mistakes can have serious financial consequences.

Then again the other day, there was this post on LinkedIn:

🚨 𝐁𝐢𝐠𝐐𝐮𝐞𝐫𝐲’𝐬 𝐑𝐈𝐃𝐈𝐂𝐔𝐋𝐎𝐔𝐒 𝐩𝐫𝐢𝐜𝐢𝐧𝐠 𝐦𝐨𝐝𝐞𝐥… | Yingjun Wu | 679 comments

🚨 𝐁𝐢𝐠𝐐𝐮𝐞𝐫𝐲’𝐬 𝐑𝐈𝐃𝐈𝐂𝐔𝐋𝐎𝐔𝐒 𝐩𝐫𝐢𝐜𝐢𝐧𝐠 𝐦𝐨𝐝𝐞𝐥 𝐜𝐨𝐬𝐭 𝐮𝐬 $10,000 𝐢𝐧 𝐣𝐮𝐬𝐭 22 𝐬𝐞𝐜𝐨𝐧𝐝𝐬!!! 🚨 I am serious—𝐭𝐡𝐢𝐬 𝐢𝐬… | 679 comments on LinkedIn

LinkedInYingjun Wu

This immediately took me back to an early data role in my career. I was working on Google BigQuery and accidentally ran SELECT * on the wrong tables a few times. We're talking about work that was carried out within maybe 30 minutes.

I got a message asking what we were working on as someone noticed a spike in cost. The price? A few thousand USD burned within a 30-minute time frame.

This could have been a much more expensive mistake had we not watched the GCP spend like hawks. Nevertheless, the experience stuck with me and taught me a very valuable lesson: never underestimate the financial danger of working with services like Google BigQuery.

Fast forward almost a decade, and this lesson is more relevant than ever.

If a single human error can be this expensive, imagine what damage an AI agent with access to pay-per-use cloud resources can do.

Agents on the Cloud = Big Risk

When it comes to AI agents interacting with cloud services, the potential for costly mistakes is enormous. So what can you do about this? Avoid using LLMs and agents with cloud services altogether? With the current capabilities of LLMs, this is no longer an option.

Every company doing anything serious with AI and analytics is on the cloud. And it doesn't matter what cloud it is:

• GCP
• AWS
• Azure
• Databricks

If you think you can avoid pay-per-use warehouses, think again. Here's a snapshot of some of the most used pay-per-use warehouses:

The list will be at least five times longer if we include them all and add serverless computing services such as AWS Lambda functions, Google Cloud Functions, etc.

While enthusiasm for cloud alternatives is growing, cloud usage and serverless computing will continue to expand - at least in the near term - driven by substantial existing investments and established infrastructure momentum.

The adoption already underway across industries makes cloud services an unavoidable reality for most organizations, particularly those leveraging advanced analytics and AI capabilities.

Any company you work with will be (or should be) highly concerned with security when deploying agents on the cloud for real-world use cases.

So, the only option is to grab the bull by its horns and manage the risk associated with agents interacting with the cloud.

GA4 and BigQuery: A Perfect Storm for Unexpected Costs

This risk is particularly pronounced when working with Google Analytics 4 (GA4) and BigQuery integration. Since Google migrated from Universal Analytics to GA4, many organizations have embraced the BigQuery export feature to analyze their web and app analytics data. However, this convenience comes with significant cost implications that are easy to overlook:

1. Data Volume Explosion: GA4 collects event-based data much more granularly than previous analytics versions. Organizations with high traffic can generate terabytes of data monthly in BigQuery, and each query against these massive tables incurs costs.
2. Complex, Nested Data Structures: GA4 data in BigQuery uses deeply nested JSON-like structures. Inexperienced analysts often resort to resource-intensive operations like UNNEST across multiple levels or run broad SELECT * queries rather than selecting specific fields, dramatically increasing processing costs.
3. Default Partitioning Challenges: While GA4 data is automatically partitioned by date, queries that don't correctly utilize these partitions will scan the entire dataset, resulting in unnecessary expenses.
4. Automated Reporting: Many organizations want to set up automated AI agents or reporting systems to analyze GA4 data regularly. If these systems aren't carefully optimized, they can repeatedly run inefficient queries, compounding costs daily or hourly.
5. Ad Hoc Exploration: The temptation to explore GA4 data through iterative, exploratory queries is high, especially when stakeholders request quick insights. Each of these exploratory queries contributes to the mounting costs.

When AI agents are given access to this environment - to automatically generate insights or answer business questions - the potential for unconstrained, expensive queries increases exponentially.

Without proper guardrails, an AI agent might enthusiastically analyze every aspect of your GA4 data, generating impressive insights and equally impressive BigQuery bills.

Cost Control with PydanticAI

PydanticAI offers a robust foundation for building cost-aware agents thanks to Pydantic's validation system.

The framework's decorator-based approach, particularly the result validator decorator, creates natural interception points where you can examine and enhance AI-generated outputs before execution.

This allows for integrating cost control mechanisms without disrupting the main agent logic.

Pydantic's typing and data validation also ensure that cost parameters are properly structured and validated, preventing unexpected behavior in production.

Analytics with BigQuery-Safe Agents

Here's what we are going to accomplish in this playbook:

1. Build a BigQuery SQL agent from scratch using PydanticAI.
2. Go through agent design patterns that allows you to safe-proofing agents.
3. Creating a template for BigQuery agents with embedded cost control - the workflows will be safer than running queries generated by humans.

In the follow-up playbook, we will use the BigQuery-safe agent design to build agents capable of analyzing Google Analytics 4 data in a fast and cost-effective way. Specifically, we will:

1. Build safe views optimized for agent interaction.
2. Develop a Google Analytics 4 agent using PydanticAI capable of extracting valuable insights from the optimized views.
3. Demonstrate specific marketing analytics use cases that the agent can effectively handle.

The practical GA4 implementation guide builds directly on the principles covered in our current playbook, showing you how to implement these safeguards with real-world marketing data analysis scenarios.

1. PydanticAI & BigQuery SQL

We'll start by creating a simple PydanticAI agent that generates and executes BigQuery SQL queries based on natural language requests. This foundation will allow business users to ask questions in plain language and get SQL responses that can be executed immediately.