Alright, I’ve been holding this one in for a while, and after seeing three separate teams at my last gig get burned by the same pattern, I have to put it out there. I love BigQuery—it’s a phenomenal product—but its on-demand pricing model is a siren song for teams with stable, predictable workloads. It lures you in with simplicity and the illusion of only paying for what you use, then quietly runs up a tab that could have been a fraction of the cost with a little planning.
Let me walk you through a real migration post-mortem. We moved a mid-sized SaaS analytics platform from an aging Redshift cluster to BigQuery. The initial logic was sound: our query patterns were spiky, we had unpredictable ad-hoc exploration from the product team, and the on-demand model seemed to align perfectly. For the first few months, it was great. The bills were reasonable, and the flexibility was a win.
Then we grew. The predictable parts of our workload—the daily ETL pipelines to refresh customer dashboards, the scheduled data quality checks, the nightly aggregations for reporting—became a larger and larger percentage of the total. We were still on on-demand. The bill kept climbing linearly with our data processed. It felt inevitable, just the cost of doing business. Here’s the trap: you get used to the model, and you stop evaluating it.
The wake-up call came when I finally did a detailed monthly breakdown. I used the `INFORMATION_SCHEMA` to analyze a month of jobs. The pattern was painfully clear.
```sql
-- A simplified version of the analysis that opened our eyes
SELECT
DATE(creation_time) as job_date,
job_type,
SUM(total_bytes_processed) / POWER(1024, 3) as GB_processed,
COUNT(*) as job_count
FROM `region-us`.INFORMATION_SCHEMA.JOBS_BY_PROJECT
WHERE creation_time >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 30 DAY)
AND job_type = 'QUERY'
GROUP BY 1, 2
ORDER BY 1, 3 DESC;
```
The output showed that over 70% of our bytes processed came from about a dozen recurring, nearly identical queries running every day. They were as predictable as sunrise. We were paying the premium on-demand rate for what was essentially a committed, steady-state workload.
The fix? We moved those specific workloads to **Flat-Rate Pricing**. We started with a flex slot commitment (which lets you commit to slots for, say, 60 seconds), perfect for our batch pipelines. Later, we moved to monthly commitments for our core BI workloads. The cost savings were dramatic—on the order of 60% for that chunk of processing—with zero performance impact.
My step-by-step advice if you're on BigQuery now:
* **Immediately** analyze your last 30-60 days of query jobs. Use the `INFORMATION_SCHEMA.JOBS` views. Categorize them.
* **Identify the predictable**: Look for queries that run on a schedule (via cron, Airflow, etc.) with consistent byte processing volumes.
* **Evaluate commitment options**: For predictable daily batch jobs, **Flex Slots** are a brilliant first step. You commit to 100 slots for 60 seconds? That's all you pay for. For 24/7 predictable load, look at monthly or annual commitments.
* **Keep the unpredictable on-demand**: Your ad-hoc analyst queries, your one-off data explorations, your low-volume dashboard refreshes? Leave those on-demand. That's what it's good for.
The moral isn't that on-demand pricing is bad. It's incredibly valuable for its intended use case: irregular, unpredictable query patterns. The trap is letting it become your default for *everything* once your platform matures. You wouldn't run your always-on production database on a per-query priced service; don't do it with your data warehouse either. The key is a hybrid approach, but you have to do the analysis to get there.
Has anyone else made this switch? What was your tipping point?
-- MigrateMentor
MigrateMentor