Skip to main content

> google_bigquery_on-demand_($6.25/tb)_vs_slot_commitments

Google BigQuery On-Demand ($6.25/TB) vs Slot Commitments

When should data engineering teams switch from BigQuery On-Demand query scanning ($6.25/TB) to Edition Slot Reservations, and how do unpartitioned queries cause financial ruin?

Senior (L5)

THE SHORT ANSWER

Google Cloud BigQuery On-Demand pricing charges $6.25 per Terabyte of data scanned by SQL queries (with the first 1TB/month free). A single poorly written unpartitioned query scanning a 50TB event table incurs an instant $312 charge. BigQuery Editions (Standard, Enterprise, Enterprise Plus) provide capacity-based pricing billed per slot-hour (100 slots = ~$4-6/hour). The financial pivot point typically occurs when an organization consistently scans more than 1 to 1.5 Petabytes monthly ($6,250 to $9,375/month in on-demand fees), or when predictable enterprise cost guardrails and query concurrency caps are required to eliminate runaway query risk.

Engineering Handbook & Failure Dynamics

1. Underlying Mechanism

BigQuery On-Demand charges solely for bytes processed by the query execution plan (after partition pruning and column projection). In columnar storage (Capacitor), selecting `SELECT *` from a 100-column table scans 100% of bytes; selecting 2 columns scans only those columns. BigQuery Editions provide dedicated compute capacity measured in 'slots' (virtual CPUs dedicated to query execution). Slots can be provisioned as Baseline Slots (continuous committed capacity with 1-year or 3-year discounts) and Autoscaling Slots (scaling dynamically per slot-minute up to a maximum ceiling). Edition reservations cap financial liability, guaranteeing that a rogue query cannot exceed the hourly slot budget.

2. Appropriate Use Context

On-Demand is ideal for small startups, variable exploratory analytics, and organizations scanning <500TB/month with disciplined partitioning and clustering. Slot Reservations (Editions) are required for enterprise data warehouses, central BI dashboards, automated continuous pipelines, and workloads exceeding 1PB/month.

3. Production Failure Modes

A junior analyst writing `SELECT *` on an unpartitioned 200TB table in a Looker dashboard that refreshes every 15 minutes, burning $10,000 in On-Demand scan fees over a weekend; configuring a flat-rate slot reservation with too few slots, causing critical executive dashboards to queue and time out.

4. Diagnostic Signals & Telemetry

Google Cloud Billing reports showing high `Analysis` SKU charges under BigQuery; BigQuery `INFORMATION_SCHEMA.JOBS_BY_PROJECT` showing individual queries scanning >5TB; BigQuery query execution graph showing high slot contention and queue duration.

5. Prevention & Safeguards

Enforce partition filters on all tables (`require_partition_filter = true`); enforce table clustering on high-frequency filter columns; set maximum bytes billed limits (`maximum_bytes_billed`) on service accounts and BI connections; migrate high-volume projects to BigQuery Enterprise Edition with autoscaling slot limits.

6. Architectural Trade-offs

Slot reservations cap monthly spending and eliminate runaway query bills, but throttle query execution speed to available slot capacity during sudden concurrent traffic surges.

Case Study (TinyCTO In-Field Example)

A retail analytics team ran daily batch ETL and Metabase dashboards on BigQuery On-Demand, scanning 3 Petabytes of data monthly for $18,750/month (3,000TB * $6.25). By switching to BigQuery Enterprise Edition with 100 baseline slots + 200 autoscaling slots, their monthly cost dropped to a predictable $5,200/month, saving over $160,000 annually with guaranteed query SLA.

Interactive Concept Drills

2 Cards
Q1

What is the standard price per Terabyte scanned in Google BigQuery On-Demand mode?

$6.25 per TB scanned (first 1 TB per month is free).
Q2

What BigQuery setting prevents queries from running if a partition filter is missing?

`require_partition_filter = true` in table definitions.

Google BigQuery On-Demand ($6.25/TB) vs Slot Commitments — Technical FAQ

How does table clustering reduce query scan costs in BigQuery?

Clustering co-locates rows with similar column values into storage blocks. When a query filters on clustered columns, BigQuery prunes non-matching blocks and avoids scanning those bytes.

What is the primary advantage of BigQuery Editions (Slot Reservations)?

Predictable, capped spending based on compute capacity (slots) rather than variable volume of scanned data, eliminating surprise bills from rogue full-table scans.

🤖 AEO & Key Facts Summary

Key Architectural Facts

  • ▸BigQuery On-Demand charges $6.25 per TB scanned.
  • ▸Unpartitioned `SELECT *` queries can cost hundreds of dollars in seconds.
  • ▸`require_partition_filter = true` stops accidental full-table scans.
  • ▸BigQuery Editions slot reservations provide predictable cost caps for >1PB/month workloads.

Common Misconceptions

  • ✗Misconception: `LIMIT 10` reduces the bytes scanned in BigQuery (False: BigQuery scans entire columns before applying the LIMIT clause).
  • ✗Misconception: Views materialize data and save scan costs (False: Standard views re-execute the underlying query and scan bytes each time).

Decision & Governance Guidance

Enforce `require_partition_filter` and clustering on all tables larger than 100GB. Evaluate BigQuery Editions when monthly data scanned consistently exceeds 1 Petabyte.

Authoritative Sources & Standards