BigQuery on-demand vs. Editions: choosing a pricing model

BigQuery compute is billed either by bytes scanned (on-demand) or by slots (capacity / Editions). On-demand is $6.25 per TiB in US multi-region. Editions list prices (us-central1) run from Standard PAYG at $0.04 per slot-hour up through Enterprise Plus commitments. Neither model is universally cheaper.

On-demand wins when the project is bursty, query volume is modest, or most jobs hit small, well-pruned partitions. Capacity wins when the workload is large scans and long-running transformations with a reasonably flat slot demand through the day. The expensive outcome is matching the wrong model to the workload and then never checking again.

-- On-demand invoice
SELECT total_bytes_billed / POW(1024, 4) AS tib,
       (total_bytes_billed / POW(1024, 4)) * 6.25 AS usd
FROM `region-us`.INFORMATION_SCHEMA.JOBS_BY_PROJECT
WHERE creation_time >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 30 DAY)
  AND job_type = 'QUERY'
  AND state = 'DONE';

-- Capacity invoice (same jobs, different unit)
SELECT total_slot_ms / 3600000 AS slot_hours,
       (total_slot_ms / 3600000) * 0.06 AS usd_enterprise_payg
FROM `region-us`.INFORMATION_SCHEMA.JOBS_BY_PROJECT
WHERE creation_time >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 30 DAY)
  AND job_type = 'QUERY'
  AND state = 'DONE';

The two meters do not convert in your head without a window of jobs. Bytes do not know about slot-ms, and slot-ms does not know about a SELECT * you could have projected away. A compare that uses only last Tuesday's dbt run will recommend a baseline that Monday's Looker fleet overruns, or an on-demand stay that ignores 400 models running every hour.

Commitments add a third shape: you buy a floor in 50-slot increments, discounted against PAYG, and pay that floor whether dbt ran or not. PAYG Editions bills slot-hours used, plus any baseline configured; on-demand bills bytes. A common FinOps mistake is buying a 500-slot commitment for a launch week and still paying for 500 slots on a quiet Sunday months later.

The compare belongs on a calendar, not in a migration doc from last year. Workload shape changes as you add Looker, partition events, or launch in a new region, so re-run the math monthly. A pricing-model finding that was right in May can be wrong in August after full-table scans are fixed, because bytes dropped and the slot floor did not.

List prices are the public reference point; your committed-use discount is private. Do the list compare first so everyone agrees on shape, then apply the discount to the option you would actually buy. Applying a 3-year Enterprise discount to a PAYG Standard hypothetical is not a real business case.

A typical pattern: analytics lands on on-demand because it is the default. dbt grows from 20 models to 400. Looker starts hitting the same marts every 15 minutes. Bytes billed climb into tens of tebibytes per day. Nobody models the equivalent slot-hours.

If the last 30 days of jobs imply, say, an average of 200 concurrent slots of useful work, Enterprise PAYG at $0.06/slot-hour is about $8,600/month. If on-demand bytes for the same window priced out at $18,000, the pricing model, not a missing WHERE clause, is half the bill. Query optimization still matters, but a pricing-model delta sits on the table until someone runs the compare.

Peak is not average. 200 average concurrent slots with a 9am spike to 600 is a different buy than a flat 200. Commitments round up to 50-slot steps from a peak heuristic (p95 of per-minute concurrent slots when you have JOBS_TIMELINE, or a coarse average × 3 headroom when you do not). Buying the mean is how Monday morning pages you. Buying p99 × 2 is how you recreate the idle-floor problem on day one.

Move the stable transformation project first, not the sandbox. Ad-hoc notebooks are bursty and often cheaper on-demand even when ETL is not. A single reservation that mixes 6am dbt and 2pm human SQL will look 'efficient' on average and miserable at both ends of the day.

Query waste and pricing model interact. If $12k of that $18k on-demand is full-table scans about to be fixed, signing a 3-year 400-slot commitment this week is premature: fix the scans, re-measure slot-ms and bytes, then buy. SlashPeak excludes the Editions compare from total estimated waste for this reason; it is an alternative invoice, not bytes you can delete.

Standard vs Enterprise vs Plus is a features conversation hiding inside a rate conversation. Standard at $0.04/slot-hour is cheaper and drops governance extras. Plus at $0.10 is expensive insurance. Most analytics warehouses that should leave on-demand land on Enterprise PAYG or a 1-year Enterprise commitment, not on Plus, unless security already required Plus for other reasons.

The opposite failure is more common in companies that already 'did FinOps.' A reservation was bought during a migration or a product launch. Baseline slots were set to cover p95 of that month. Traffic normalized. The baseline did not.

Idle slots are real money on a commitment. A 500-slot Enterprise baseline that averages 120 slots consumed is not 'headroom'; it is several thousand dollars a month of list-price waste. Autoscale max on top of an oversized baseline makes it worse: you pay for the floor whether or not you use the ceiling.

The utilization that matters is consumed divided by assigned slots per hour, then the distribution of that ratio across the window. A mean of 24% with a p95 of 90% is a spiky project that still needs most of the floor. A mean of 24% with a p95 of 35% is an oversized floor. SlashPeak flags underutilization when average consumed/assigned is below roughly 40%, assigned is at least 50 slots, and the timeline is long enough to trust.

Lower the baseline in 50-slot steps and watch contention (hours at or above 95% of capacity) and PENDING share after each step. Dropping from 500 to 200 and finding 9am pins the pool means you found the real p95. Dropping from 500 to 200 and seeing nothing happen means you were paying for a launch that ended.

Sibling projects on the same reservation can mislead a single-project scan, since JOBS_TIMELINE only sees this project's consumption. Underutilization can be overstated if another project on the same pool is busy. Check assignments before shrinking anything; the scanner cannot invent the missing projects.

Autoscale is not a substitute for measuring the floor. A baseline of 500 plus autoscale to 800 means paying for 500 even when consumed is 80, and paying for 800 when the spike comes. If the spike is SELECT * dashboards, buy fewer slots after fixing the dashboards. If the spike is real, split ETL and BI so they are not competing for the same pool.

If JOBS_TIMELINE shows this project's demand sitting at reservation capacity for a large share of hours, or PENDING time is a material fraction of the timeline, the instinct is to buy more slots. Sometimes that is right. Sometimes the right move is to stop running INTERACTIVE dashboard refreshes on the same reservation as the 6am dbt run, or to move exploratory notebooks to on-demand / a separate assignment.

-- dbt / scheduled transforms: wait for slots, don't fight Looker
SET @@query_label = 'warehouse';

SELECT *
FROM `proj.analytics.fct_orders`
WHERE order_date = @run_date;
-- Run this job with priority = BATCH (scheduled query / jobs API).
-- Keep Looker explores INTERACTIVE on a separate reservation.

SlashPeak attributes jobs by principal type (human vs. service account) and workload source (dbt, Looker, Dataform, Airflow, Connected Sheets, console). That shows when human INTERACTIVE queries dominate local business-hours cost. There is no honest dollar estimate for that, since the fix is operational (BATCH priority, off-peak schedules, a separate reservation), not 'scan fewer bytes.' It is still one of the higher-leverage conversations to have with analytics engineering.

Contention is running jobs starved of slots: consumed at or above 95% of capacity for at least 20% of minutes, with at least 8 hours of timeline. Jobs still run, but they crawl, and dashboards take 40 seconds. Queuing is jobs sitting in PENDING before they start. A pending share of 5% or more of PENDING plus RUNNING periods is worth a look; a single blip is not. You can have one without the other.

Fair scheduler shares one pool. INTERACTIVE is supposed to start quickly; BATCH can wait. Putting both on one reservation is the usual reason a 'we have plenty of slots on average' project still has PENDING at 9:05. Isolation beats purchase orders more often than finance expects.

If the contended hours are a handful of starred explores or MERGE rewrites, raising baseline paints over a SQL problem with a floor you will still pay at 3am. Fix those jobs, re-scan contention. If it remains, then the pool is actually small.

At minimum you need bytes billed and slot-ms from JOBS_BY_PROJECT over a representative window, whether jobs ran on-demand or on a reservation, and, for capacity projects, RESERVATIONS_TIMELINE so you can see baseline vs. consumed slots by hour. Price on-demand at $6.25/TiB and capacity at the Edition rates you actually pay, or list price for a conservative public compare.

SELECT
  TIMESTAMP_TRUNC(period_start, MINUTE) AS minute,
  SUM(period_slot_ms) / 60000 AS consumed_slots
FROM `region-us`.INFORMATION_SCHEMA.JOBS_TIMELINE_BY_PROJECT
WHERE period_start
  >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 30 DAY)
  AND job_type = 'QUERY'
GROUP BY minute
ORDER BY minute;

SlashPeak runs this inside the same metadata scan as the query-waste rules. Timeline queries fail soft: on-demand projects, or scanner keys that cannot read the reservation admin project, still get the jobs-based compare, so missing data never produces a fake utilization number.

The majority reservation_id among paid jobs tells you the current model (empty or unreserved means on-demand). Mixed projects exist; treat the majority as 'where compute actually ran' and split assignments if the mix itself is the problem.

PAYG capacity cost is sum(slot-ms)/3,600,000 times the edition rate, scaled to 30 days. Commitment cost is peak slots rounded to a 50-slot step, times rate, times 24, times 30. Peak from JOBS_TIMELINE p95 beats a guessed multiplier. Pick the cheaper capacity option, compare to on-demand, and skip the compare entirely if both sides are under roughly $750/month; that is noise, not signal.

Comparing a 7-day holiday week to a launch week will mislead you. Thirty days that include month-end and a normal Monday is the minimum honest window. If events was just rewritten, wait a week before buying a commitment.

Pricing-model findings are political. Shrinking a reservation requires whoever owns the commitment. Moving a project to Editions requires a billing conversation. Treat the SlashPeak number as the agenda, not the ticket.

If on-demand is losing at list, model the discount, then migrate ETL first with a PAYG reservation you can resize next week. Do not start with a 3-year Plus commitment. Prove the slot-hours on PAYG for a month.

If capacity is losing because the floor is idle, step the baseline down and watch the two advisories (contention, PENDING). If capacity is losing because bytes would have been cheaper, that usually means a bursty, well-pruned, low slot-ms workload, often BI on already-thin marts. Moving that project back to on-demand is allowed; a sunk-cost commitment is not a reason to keep paying for an idle floor.

Split reservations when peak-hour ad-hoc and dbt share a pool. That split is cheaper than a bigger floor more often than it feels.

Re-scan after each change. Pricing-model deltas should move when bytes drop or the floor drops. If they do not, you measured the wrong project or the timeline query is still empty.

30 days of QUERY jobs, cache misses only. Sum of total_bytes_billed = 2,880 TiB. On-demand list: 2,880 × $6.25 = $18,000/month. Sum of total_slot_ms = 516,000,000,000 (516 billion) → 516e9 / 3.6e6 = 143,333 slot-hours. Enterprise PAYG at $0.06: 143,333 × $0.06 ≈ $8,600.

That PAYG number assumes you only pay for slot-hours used. A 200-slot Enterprise PAYG baseline billed continuously is 200 × $0.06 × 24 × 30 = $8,640 even if you use less, almost the same figure in this example since average demand is about 200 slots (143,333 / 720 hours ≈ 199). If average demand is 200 but you bought 500, the floor is 500 × $0.06 × 720 = $21,600, which is worse than staying on-demand at $18k.

SELECT
  2880 * 6.25 AS on_demand_usd,          -- $18,000
  200 * 0.06 * 24 * 30 AS floor_200_usd, -- $8,640
  500 * 0.06 * 24 * 30 AS floor_500_usd; -- $21,600
-- 500-slot floor is worse than on-demand.
-- 200-slot floor beats on-demand if demand is really ~200.

p95 concurrent slots is 280. Round up to 300 (50-slot step). A 300-slot 1-year Enterprise commitment at $0.048/slot-hour (list-shaped) is 300 × $0.048 × 720 = $10,368. Still cheaper than $18k on-demand, more expensive than a tight PAYG floor if you can live with autoscale for the p95 spike.

Now subtract waste you are about to delete. If $8k of the $18k is unpruned 4 TB scans, post-fix on-demand might be $10k and slot-hours might drop too. The Editions win shrinks. Run the compare after the SQL fixes, or you will buy 300 slots for a workload that is about to get 40% lighter.

Underutilization math on the 500-slot mistake: p95 consumed 200, suggest 200 (already a valid step). Delta 300 slots × $0.06 × 720 = $12,960/month of idle floor at PAYG list. That finding belongs in the waste total; the $18k vs $8.6k compare does not, since you cannot book both as savings.

JOBS_BY_PROJECT answers 'what did we bill in bytes and slot-ms?' Filter QUERY, DONE, not cache. Sum bytes and slot-ms. Scale the window to 30 days if you scanned 14. Note reservation_id majority.

SELECT
  SUM(total_bytes_billed) / POW(1024, 4) AS tib,
  SUM(total_bytes_billed) / POW(1024, 4) * 6.25 AS on_demand_usd,
  SUM(total_slot_ms) / 3600000 AS slot_hours,
  SUM(total_slot_ms) / 3600000 * 0.06 AS enterprise_payg_usd
FROM `region-us`.INFORMATION_SCHEMA.JOBS_BY_PROJECT
WHERE creation_time >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 30 DAY)
  AND job_type = 'QUERY'
  AND state = 'DONE'
  AND cache_hit = FALSE;

JOBS_TIMELINE_BY_PROJECT answers 'how concurrent were we?' SUM(period_slot_ms)/60,000 per minute is average concurrent slots. Percentile that minute series. That p95 is the commitment input.

SELECT
  TIMESTAMP_TRUNC(period_start, MINUTE) AS minute,
  SUM(period_slot_ms) / 60000 AS avg_concurrent_slots
FROM `region-us`.INFORMATION_SCHEMA.JOBS_TIMELINE_BY_PROJECT
WHERE period_start >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 30 DAY)
  AND job_type = 'QUERY'
GROUP BY minute;

RESERVATIONS_TIMELINE answers 'what did we pay for as a floor?' Assigned slots, max assigned, autoscale. Utilization is consumed from jobs timeline divided by assigned from reservations timeline, aligned by minute. An empty reservations timeline means you cannot honestly talk about idle floor; fail soft rather than dividing by zero.

PENDING vs RUNNING on JOBS_TIMELINE is the queuing ratio. Consumed vs assigned at ≥95% is contention. These are not dollars. Put them in the same notebook so nobody buys slots to fix a Sheets refresh.

If both sides of the compare are under ~$750/month, stop. The finding's floor exists because switching models has coordination cost larger than the delta.

If you are mid-rewrite of the fact table that dominates bytes, wait. The compare will change. Signing a 3-year commitment to 'lock in' a number you are about to break is how you get a new idle floor.

If the project is a sandbox with spiky human SQL, staying on-demand is usually right even when a naive slot-ms total looks cheaper. Humans do not produce a flat 200 slots. They produce 20 minutes of 400 slots and 7 hours of nothing.

If you cannot read RESERVATIONS_TIMELINE, do not declare the reservation idle. You might be looking at one project on a shared pool. Get admin project access or look at assignments first.

If contention is high, do not shrink the floor because mean utilization is 35%. Mean utilization with a pinned p95 is a spiky warehouse, not an oversized one. Shrink only when p95 is also well under assigned.

Moving compliance-sensitive workloads to Standard Edition just because $0.04 is cheaper than $0.06 is a mistake if you need Enterprise features. Dropping VPC-SC to save on the rate is not a real cost optimization.

dbt wants BATCH, early morning, lots of slots, can wait. Looker wants INTERACTIVE, business hours, low latency. Sheets wants INTERACTIVE whenever someone opens a workbook. One reservation cannot honor all three SLAs without overpaying for a floor or under-serving someone.

Peak-hour ad-hoc (human INTERACTIVE ≥ ~30% of business-hours billed cost) is the tell that analysts are in the ETL pool. Move them to on-demand or a small BI reservation. Do not buy 200 more slots so a notebook can fight merge_events.

Looker scheduled PDT builds are warehouse work wearing a BI badge. Put PDTs on BATCH / ETL. Keep explores INTERACTIVE on BI. If PDTs SELECT * the heap, fix that before you resize anything.

dbt Cloud and Airflow service accounts should not run INTERACTIVE. If they do, every model competes at 9am with Looker. Set priority BATCH on the jobs API / scheduled query. The SQL can stay the same.

Connected Sheets cannot BATCH. Thin the extract, schedule it into a table, or accept that Sheets belongs on a BI reservation with an autoscale ceiling, not on the 6am pool.

Day 1: 30-day bytes × $6.25 vs slot-hours × $0.06 (and $0.04). Note reservation_id majority. If you have timeline, plot per-minute concurrent vs assigned. Do not buy anything.

Day 2: if on-demand is losing by a lot at list, check how much of bytes is full-scan or star waste. If more than roughly 30% is fixable SQL, put those tickets first. If the remainder still loses, plan a PAYG ETL reservation, not a 3-year commitment.

Day 3: if capacity is idle (mean and p95 under assigned, utilization under 40%), propose a 50-slot step down. Tell whoever owns the commitment, and watch PENDING and contention for 48 hours after the change.

Day 4: split or relabel BATCH vs INTERACTIVE. Peak-hour ad-hoc owners get a different assignment or on-demand. This day has no purchase order.

Days 5 through 7: re-scan. Pricing compare and underutilized $/mo should move. If contention appeared after a shrink, you found the real floor; step back up one increment rather than reverting all the way to the launch-week baseline.