Cloud leaders love BigQuery for its power, but its flexible pricing can lead to runaway costs. Here’s how Rabbit’s Intelligent BQ Job Optimizer leverages Google’s latest job-level reservation assignments feature to deliver 15-20% savings—automatically.

Decoding BigQuery Pricing: On-Demand vs. Capacity-Based

Google BigQuery offers two main ways to pay for processing:

On-Demand Pricing: Billed for the volume of data scanned by your queries (e.g., $6.25 per TB in US multi-region). This model shines when the amount of data processed is relatively small compared to the computing power needed. For example, queries with complex intermediate stages can be cheaper on-demand. Consider this query that generates all possible DNA strings of length 17:

WITH RECURSIVE
dna_strings AS (
  SELECT 1 AS lenght, c AS dna_string FROM UNNEST(['A', 'C', 'G', 'T']) AS c
  UNION ALL
  SELECT lenght + 1, CONCAT(dna_string, c)
  FROM dna_strings
  CROSS JOIN UNNEST(['A', 'C', 'G', 'T']) AS c
  WHERE lenght <= 16
)
SELECT * FROM dna_strings
WHERE lenght = 17
ORDER BY dna_string
LIMIT 10000000

This query reads zero bytes of data (on-demand cost: $0) but requires significant computing power to generate billions of combinations consuming around 185 slot hours. Assuming a $0.04 / slot hour standard pay-as-you-go reservation price the capacity based pricing is:

$0.04 × 185 = $7.4

While this is an extreme example, it illustrates how compute-intensive operations with minimal data scanning favor on-demand pricing. (Side note: It’s intriguing to consider how this “free” compute capacity could be leveraged for practical use cases in BigQuery.)

Capacity-Based Pricing (Slots): You purchase dedicated query processing power (“slots”). BigQuery’s capacity-based pricing is now highly flexible: you can manage workloads with baseline and autoscaling slots, isolate workloads with reservations, and share idle slots across your organization. If a query processes a large amount of data but computationally is simple, capacity-based pricing can be dramatically cheaper than on-demand. For example:

SELECT * FROM `bigquery-public-data.crypto_bitcoin_cash.transactions` LIMIT 1000

This query needs to process 945.55 GB of data. At $6.25 per TB in the US multi-region, the on-demand cost would be:

$6.25 × (945.55 / 1024) ≈ $5.77

However, the query only requires 3198 slot-milliseconds to execute — so the cost under capacity-based pricing is virtually zero. In such cases, slot-based pricing is far more economical than on-demand.

Verify the Pricing Yourself

You can check the pricing details for any query directly in the BigQuery UI. After running a query, the “Query results” panel shows both the bytes processed (for on-demand pricing) and slot usage (for capacity-based pricing):

This information is also available programmatically through BigQuery’s Information Schema. While calculating on-demand costs is straightforward (multiply bytes processed by your rate), determining slot-based costs requires considering multiple factors:

• Commitments: Different discount levels based on commitment terms
• Idle slot sharing: Slots can be shared across projects when idle
• Reservation waste: Inefficient slot utilization can lead to higher effective costs. Learn more about optimizing reservation costs if you want to understand waste better

Rabbit eliminates the complexity by automatically calculating and displaying both pricing models for every query, with all these factors taken into account. No manual calculations are needed.

Analyze Your Savings Potential

Want to understand the potential savings from optimizing your BigQuery pricing models? We’ve open-sourced two SQL scripts that help you analyze your BigQuery jobs and calculate potential savings:

1. Project-Level Analysis: Analyzes jobs within a specific project
2. Organization-Level Analysis: Examines jobs across your entire organization

You can find these scripts in our awesome-rabbit repository.

This data-driven approach helps you quantify the benefits of implementing job-level optimization in your environment.

The Breakthrough: Job-Level Reservation Assignments

Google Cloud has recently introduced job-level reservation assignments. As detailed in the documentation, you can now decide for each individual query whether it should run on-demand or use a specific slot reservation. This powerful feature lets you specify reservations at runtime through multiple interfaces including the CLI, UI, SQL, and API - overriding any default assignments at the project, folder or organization level. The only requirement is that the assigned reservation must be in the same region as your query. This flexibility eliminates the need to create separate projects just for reservation management.

This is a monumental shift: You’re no longer locked into a single pricing model for all queries within a project. Instead, you gain fine-grained control to optimize costs based on each job’s characteristics and real-time slot utilization.

The Million-Dollar Question: Which Pricing Model for Which Job?

This flexibility is powerful, but it raises a key question: How do you determine, before execution, whether a specific job will be cheaper on-demand or using reserved capacity?

To truly capitalize on this, you need to:

• Analyze detailed query metadata: data to be processed, query complexity.
• Monitor real-time slot utilization across reservations.
• Review historical cost data for similar or identical queries to inform decisions.
• Predict costs for both models and route the job accordingly.

Rabbit’s Intelligent BQ Job Optimizer: Automated Savings, Effortlessly

At Rabbit, we’ve engineered the solution. Our Intelligent BQ Job Optimizer dynamically routes query jobs to the most cost-effective pricing model—on-demand or your capacity reservations — on a per-job basis.

Here’s how it works:

1. Query Interception: Before submission to BigQuery, your query job is intercepted and the query configuration is sent to Rabbit’s API for optimization.

2. Intelligent Analysis: Rabbit’s API analyzes the query configuration to determine the optimal pricing model.

3. Optimized Execution: Your orchestrator receives the optimized configuration and submits the query to BigQuery with the most cost-effective pricing model selected.

You can easily integrate this optimization into your existing workflows through:

• Workflow Tools: Ready-to-use plugins for Apache Airflow and dbt
• Client Libraries: Native SDKs for Java, Python, JavaScript, and Go
• Direct Integration: RESTful API for custom implementations

The optimization is completely transparent to your applications - they continue working exactly as before, just with lower costs.

The result? Rabbit users are consistently achieving 15-20% (and sometimes more) in BigQuery cost savings, with zero disruption to their existing workflows.

Why This Is a Game Changer for Your Cloud Strategy

BigQuery’s workload management model has matured significantly, offering incredible flexibility. But to truly capitalize on this flexibility and maximize your ROI, automation isn’t just helpful — it’s essential.

Our mission at Rabbit is to make GCP cost monitoring and optimization effortless. This new capability is a prime example of how we turn complex cloud features into tangible savings for your business.

Ready to see your BigQuery savings potential in action? Start your free 30-day trial today and experience smarter BigQuery cost management with zero commitment.