We brought a clients BigQuery bill down by over 50% by implementing one thing, here’s the code for free.

The One Feature I Wish BigQuery Would Add (And Why I Built a Script Instead)

I love BigQuery. It’s fast, powerful, and scalable. But after analyzing storage costs for a client’s terabytes of event data, I realized there is one glaring feature missing that could save companies thousands of dollars—and it’s surprisingly simple.

“Auto-Optimize Storage Billing Model.”

Here is what happened when we manually optimized a client’s setup recently. We didn’t delete any data; we just changed how Google bills us for it.

[Insert Image: Screenshot of the blue/orange bar chart showing the cost drop]

The Hidden Cost of “Logical” by Default

BigQuery defaults new datasets to the Logical Storage billing model. For many use cases, this is fine. But for anyone working with highly compressible data—like IoT streams, logs, or repetitive event data—it is often a money pit.

We discovered that switching to the Physical Storage model (which bills based on compressed bytes) could reduce storage costs by 50% to 80% for these specific datasets.

The problem? You have to calculate the difference manually.

Why isn’t this automatic?

Google Cloud provides amazing visibility into query costs, but storage optimization feels like a blind spot. To find these savings, you currently have to:

1. Find the compression ratio of your tables.
2. Calculate the cost of Logical vs. Physical.
3. Factor in “Time Travel” and “Fail Safe” storage costs (which apply differently to Physical storage).
4. Compare them for every single dataset.

This is tedious math that a machine should be doing.

So, I Built a Script for It

Since the “Easy Button” doesn’t exist yet, I wrote a Python script to do the heavy lifting.

It scans your entire GCP Project, looks at every dataset, and crunches the numbers. It compares your current costs against what you would pay if you switched models, and generates a simple recommendation: KEEP or SWITCH.

You can grab it for free here: github.com/thatapicompany/bigquery-storage-optimizer

The “Wishlist” Feature

My open request to the BigQuery team: Sherlock needs a Watson.

Imagine a “Storage Recommendations” tab in the BigQuery console that simply says:

“We noticed dataset iot_logs is highly compressed. Switching to Physical Storage would save you ~$X/month. [Switch Now]”

Or better yet, an “Auto-Optimize Storage” toggle that automatically picks the cheaper model for you.

Until then, feel free to use the script to find the hidden savings.

Give us a shout if you’d like us to do the same for you: https://thatapicompany.com/contact-us/