BigQuery On-Demand vs Editions: Per-Query Cost Comparison

Why This Matters

BigQuery Editions pricing can save 60-80% compared to on-demand for the right workloads, but it can also cost more if your usage patterns don't align. Making this decision without data is a gamble. This query gives you the data to make an informed choice by comparing costs across all pricing tiers for every query in your project.

How It Works

The query calculates the on-demand cost (bytes billed * $6.25/TiB) and Editions costs (slot-hours * hourly rate per edition) for each query. It accounts for BigQuery's minimum billing rules: 1-minute minimum duration for Editions and 100-slot minimum increments for the autoscaler.

SQL Query

-- Compare on-demand vs Editions pricing for every query

DECLARE lookback_days INT64 DEFAULT 30;

WITH base AS (
  SELECT
    project_id, query, start_time, end_time,
    SAFE_DIVIDE(total_slot_ms,
      TIMESTAMP_DIFF(end_time, start_time, MILLISECOND)) AS avg_slots,
    COALESCE(total_bytes_billed, 0) / POW(1024, 4) AS tib_billed,
    TIMESTAMP_DIFF(end_time, start_time, MILLISECOND) AS duration_ms
  FROM `your-project`.`region-us`.INFORMATION_SCHEMA.JOBS_BY_PROJECT
  WHERE creation_time >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL lookback_days DAY)
    AND state = 'DONE' AND total_slot_ms IS NOT NULL
),
billing AS (
  SELECT *,
    GREATEST(tib_billed, 10.0 / POW(1024, 2)) * 6.25 AS on_demand_usd,
    CEIL(avg_slots / 100) * 100 AS billed_slots,
    GREATEST(duration_ms, 60000) / 3.6e6 AS billed_hours
  FROM base
),
with_editions AS (
  SELECT *,
    billed_slots * billed_hours * 0.04 AS standard_usd,
    billed_slots * billed_hours * 0.06 AS enterprise_usd,
    billed_slots * billed_hours * 0.048 AS enterprise_1yr_usd,
    billed_slots * billed_hours * 0.036 AS enterprise_3yr_usd
  FROM billing
)
...

Query Explanation

The query first calculates approximate slot count and execution time for each query. It then applies Editions billing rules: rounding slots up to the nearest 100 and enforcing a 1-minute minimum duration. On-demand cost uses $6.25/TiB. Edition costs use published per-slot-hour rates: Standard $0.04, Enterprise $0.06, Enterprise 1yr $0.048, Enterprise 3yr $0.036.

Key Insights

• lightbulb

  Queries that use few slots for a long time are cheaper on-demand. Queries that use many slots briefly are cheaper on Editions.

• lightbulb

  If >70% of your queries recommend the same edition, that's a strong signal to switch your entire project.

• lightbulb

  Mixed recommendations suggest a hybrid approach: Editions for scheduled heavy queries, on-demand for light ad-hoc work.

• lightbulb

  The biggest savings come from Enterprise 3-Year Commit ($0.036/slot-hour vs $6.25/TiB on-demand).

Best Practices

1. 1

   Run this analysis over at least 30 days of data for reliable recommendations.

2. 2

   Sum the total cost across all queries for each billing model to get the project-level recommendation.

3. 3

   Factor in your growth trajectory — if data volumes are growing, Editions becomes relatively cheaper over time.

4. 4

   Start with Standard or Enterprise Edition (no commit) to test before committing to 1-year or 3-year terms.

Related Guides