DEV Community

Cover image for BigQuery On-Demand vs Editions & Reservations: When Each Pricing Model Wins
Gowtham Potureddi
Gowtham Potureddi

Posted on

BigQuery On-Demand vs Editions & Reservations: When Each Pricing Model Wins

#python #sql #interview #dataengineering

bigquery pricing is the single most-asked cost interview question in senior data-engineering loops in 2026 — and the one topic where a wrong answer at a whiteboard translates directly into six-figure line items on a real cloud bill. The BigQuery pricing surface has expanded from a single bigquery on-demand dial (dollars per terabyte scanned) into a five-dial model that mixes on-demand bytes with three tiers of bigquery editions (Standard, Enterprise, Enterprise Plus), a slot-based bigquery autoscaler, and 1-year or 3-year bigquery reservations commitments — each with its own break-even, its own feature surface, and its own failure mode when a senior engineer picks it for the wrong workload.

This guide is the senior-DE walkthrough that answers the questions interviewers actually ask about bigquery cost — "walk me through when you'd move a nightly ELT off bigquery on-demand onto bigquery slots", "size a commit for a 500-slot ELT plus a BI dashboard that spikes at 09:00", "compare standard edition versus enterprise edition for a workload that needs BI Engine but not CMEK", "how do you tune the autoscaler baseline and max so the flat-rate cost doesn't collapse when a Looker refresh hits during ingestion". It walks the billing atoms — bytes scanned versus slot-time — the three Editions with their per-tier rates and feature gates, the reservation + assignment model that lets you split a slot pool across QUERY / PIPELINE / BACKGROUND workloads, the idle-capacity sharing rules that let a bursty BI reservation borrow from a mostly-idle ELT reservation, and the mixed-workload patterns that senior engineers ship into every real GCP data warehouse. Each section pairs a teaching block with a Solution-Tail interview answer — code, a step-by-step trace, an output table, then a concept-by-concept breakdown of why it works.

PipeCode blog header for BigQuery pricing — bold white headline 'BigQuery Pricing' with subtitle 'On-Demand · Editions · Reservations' and a hero composition of a bytes-funnel on the left, a slot autoscaler card on the right, and a central wax-seal reading $$ on a dark gradient with purple, green, orange, and blue accents and a small pipecode.ai attribution.

When you want hands-on reps immediately after reading, drill the SQL practice library →, rehearse on the ETL practice library →, and sharpen the tuning axis with the optimization practice library →.

On this page

1. Why BigQuery pricing became the most-asked interview cost question

Five dials, two billing atoms — the 2026 bigquery pricing surface is a real design decision, not a checkbox

The one-sentence invariant: BigQuery in 2026 charges you either for bytes scanned (bigquery on-demand) or for slot-time consumed by an Edition on a reservation with an optional 1-year or 3-year commit — the workload has to match the billing atom, and a senior engineer picks the model per-project, per-workload, and per-time-of-day rather than for the whole account. Every other cost interview question — the on-demand $/TB rate, the per-tier Editions rate, the commit discount, the idle-capacity share, the autoscaler baseline — is a consequence of which of those two atoms you chose to be billed on.

The four "must-answer" axes interviewers actually probe.

  • Unit. Is the workload billed by bytes scanned (on-demand) or by slot-time (Editions)? The senior signal is naming both atoms in the first sentence — bytes is per-query and unpredictable; slot-time is per-second and predictable once the reservation is sized.
  • Elasticity. Does the workload need to burst instantly (on-demand: implicit up to burst cap; Editions: autoscaler baseline → max slots), or does it have a steady baseline with predictable peaks (reservations with a committed baseline plus an autoscaler headroom)? The elasticity story is where the autoscaler baseline and max values live — and where teams over-commit or under-commit.
  • Commit. No commit (pay-as-you-go on-demand or Editions), 1-year commit (~20% discount on slots), or 3-year commit (~40% discount)? The break-even calculation between on-demand-per-TB and slots-per-hour is one of the most-asked whiteboard math questions in senior GCP interviews.
  • Workload mix. A single account almost always has a mix — a nightly ELT, an interactive BI dashboard, ad-hoc analyst queries, and occasional ML training. The senior answer never picks one model for everything; it maps each workload to the model that fits its billing atom, then lets bigquery reservations and idle-capacity sharing recover the overhead.

What the interviewer listens for.

  • Do you say "BigQuery has two billing atoms — bytes scanned and slot-time" in the first sentence when asked about cost? — senior signal.
  • Do you distinguish between on-demand as a pricing model and Editions as a set of pricing tiers, and place reservations + commitments as a commitment layer on top of Editions? — senior signal.
  • Do you push back on "just move everything to Enterprise Plus with a 3-year commit" because it double-charges the ad-hoc workload that would run for 20% of the cost on-demand? — required answer.
  • Do you name the autoscaler baseline / max as the elasticity dial? — required answer.
  • Do you mention INFORMATION_SCHEMA.JOBS_BY_PROJECT or JOBS_TIMELINE_BY_PROJECT as the cost-attribution source of truth? — senior signal.

Why the five-dial model exists in 2026.

  • On-demand ($6.25/TB scanned as of 2026). The oldest BigQuery pricing model — you pay per byte the query engine reads from storage, regardless of how many slots the query used or how long it ran. Auto-provisions up to a per-project burst cap of slots (default 2000). The right answer for unpredictable, low-volume, ad-hoc workloads where you cannot forecast slot usage.
  • Standard Edition. The cheapest Editions tier (~$0.04/slot-hour, US multi-region as of 2026). Basic feature set — no BI Engine, no Data QnA, no CMEK, no VPC-SC. Autoscaler baseline 0 → max as configured. The right answer for cost-conscious steady workloads that only need vanilla SQL and streaming.
  • Enterprise Edition (~$0.06/slot-hour). Adds BI Engine, external tables, BigQuery ML integration, materialised views, and more autoscaler headroom. The right answer for BI dashboards, ML feature stores, and any workload that hits the BI Engine cache.
  • Enterprise Plus Edition (~$0.10/slot-hour). Adds CMEK (customer-managed encryption keys), VPC Service Controls, cross-region replication, extended time travel, and disaster-recovery reservations. The right answer only when compliance or DR requirements force it.
  • Reservations + commitments. A reservation is a named pool of slots assigned to a set of projects, folders, or organisations. Commit for 1 year (~20% discount) or 3 years (~40% discount) to lock in a baseline slot count; the autoscaler adds burst above the baseline at the on-demand slot rate. Assignments split the reservation across QUERY, PIPELINE, and BACKGROUND job types so an interactive dashboard cannot starve a nightly ELT.

The four "cost surprise" patterns interviewers use to probe depth.

  • The wide-scan surprise. A SELECT * on a 1 TB table costs $6.25 on-demand — but the query itself may have needed only two columns. The senior answer is column pruning + partition filters, not "move to Editions".
  • The unpartitioned-table surprise. A query with WHERE ts > NOW() - INTERVAL 1 DAY against a non-partitioned table scans the entire table. On-demand billing is linear in bytes, not linear in rows; the senior fix is PARTITION BY DATE(ts) + require_partition_filter = TRUE.
  • The autoscaler-runaway surprise. An Enterprise reservation with max = 2000 slots spins to 2000 during a bad query. If the query is a broken join, the slot-hour cost balloons; the senior fix is a job_timeout_ms per-project quota plus autoscaler max sized to the actual workload rather than the theoretical max.
  • The idle-slot surprise. A team commits to 1000 slots on a 3-year deal, then discovers the average utilisation is 30%. The 70% idle cost is a real number; the senior fix is right-sizing the commit to p50 and letting the autoscaler cover p90+ at the on-demand slot rate — or using idle-capacity sharing across reservations.

Worked example — bytes-scanned vs slot-time on the same workload

Detailed explanation. The single most useful cost exercise for a senior interview is pricing the same query two different ways. Take a nightly ELT that scans 5 TB of raw event data and consumes 400 slot-hours over a 2-hour window. Price it under on-demand and under Enterprise Edition with a 1-year commit; walk through the break-even.

  • The workload. 5 TB scanned per night; 400 slot-hours consumed; 30 nights per month.
  • The on-demand math. 5 TB × $6.25/TB × 30 = $937.50 per month. Straightforward, no commit, but scales with data volume.
  • The Editions math. Requires a slot commit sized to the ELT peak. If the ELT peaks at 1000 slots for 2 hours a night, a 1000-slot commit at $0.048/slot-hour with 20% annual discount = $0.0384/slot-hour × 1000 × 24 × 30 = $27,648/month. Idle cost is dominant.

Question. For a nightly ELT that scans 5 TB and consumes 400 slot-hours over 2 hours per night, calculate the on-demand cost and the Editions cost with a 1-year commit sized to the peak (1000 slots), then design a hybrid that uses on-demand for the ELT and a smaller commit for the always-on BI dashboard.

Input.

Parameter Value
Nightly bytes scanned 5 TB
Nightly slot-hours consumed 400
Peak concurrent slots during ELT 1000
Nights per month 30
BI dashboard steady-state slots 200
BI hours per day 12
On-demand rate $6.25 / TB
Standard slot rate $0.04 / slot-hour
Enterprise slot rate $0.06 / slot-hour
1-year commit discount 20%

Code.

-- On-demand cost per month (bytes-based)
WITH on_demand AS (
  SELECT 5   AS tb_scanned_per_night,
         30  AS nights_per_month,
         6.25 AS usd_per_tb
)
SELECT tb_scanned_per_night * nights_per_month * usd_per_tb AS monthly_cost_usd
FROM   on_demand;
-- monthly_cost_usd = 937.50

-- Enterprise Editions cost per month (commit sized to peak, 1-yr discount)
WITH editions AS (
  SELECT 1000  AS committed_slots,
         0.06  AS enterprise_slot_hour_rate,
         0.20  AS one_year_discount,
         24    AS hours_per_day,
         30    AS days_per_month
)
SELECT committed_slots
       * (enterprise_slot_hour_rate * (1 - one_year_discount))
       * hours_per_day
       * days_per_month                      AS monthly_cost_usd
FROM   editions;
-- monthly_cost_usd = 34,560.00 (dominant driver: idle slots the rest of the day)
Enter fullscreen mode Exit fullscreen mode 
-- Hybrid — on-demand for ELT + smaller Enterprise commit for BI
WITH hybrid AS (
  SELECT 5        AS elt_tb_per_night,
         30       AS nights,
         6.25     AS on_demand_usd_per_tb,
         200      AS bi_committed_slots,
         0.06     AS enterprise_slot_hour_rate,
         0.20     AS one_year_discount,
         24       AS hours_per_day
)
SELECT
    (elt_tb_per_night * nights * on_demand_usd_per_tb) AS elt_on_demand_cost,
    (bi_committed_slots
     * (enterprise_slot_hour_rate * (1 - one_year_discount))
     * hours_per_day * 30)                             AS bi_commit_cost,
    (elt_tb_per_night * nights * on_demand_usd_per_tb)
    + (bi_committed_slots
       * (enterprise_slot_hour_rate * (1 - one_year_discount))
       * hours_per_day * 30)                           AS total_monthly_cost
FROM hybrid;
-- elt_on_demand_cost = 937.50
-- bi_commit_cost     = 6,912.00
-- total_monthly_cost = 7,849.50
Enter fullscreen mode Exit fullscreen mode

Step-by-step explanation.

  1. On-demand for the ELT alone: 5 TB per night × 30 nights × $6.25/TB = $937.50. The number is small because the ELT runs briefly and only scans the raw event tables. On-demand rewards short, wide, infrequent workloads.
  2. Enterprise Editions sized to the ELT peak: to guarantee 1000 slots during the 2-hour ELT window, a 1000-slot commit is required. But the commit is charged 24 hours a day, not 2. The idle 22 hours dominate the cost — 1000 × $0.048 × 24 × 30 = $34,560/month. The commit is 37x more expensive than the on-demand equivalent because slot-time bills for slots you rent, not slots you use.
  3. The hybrid pattern: keep the ELT on on-demand (perfect match for a wide-but-brief scan) and put the always-on BI dashboard on a small Enterprise commit sized to its steady state (200 slots × 12 hours × 30 days). Total cost drops to $7,849.50 — 4.4x cheaper than the naive all-Enterprise commit.
  4. The lesson: slot-time bills for what you reserve; bytes-scanned bills for what you scan. A workload that runs 2 hours a day fits on-demand better than a 24-hour reservation.
  5. The design rule of thumb: pick on-demand for workloads with duty cycle < 10% (like the nightly ELT); pick a slot commit for workloads with duty cycle > 50% (like a BI dashboard). Between 10% and 50%, run the math both ways and pick the cheaper.

Output.

Model Cost per month Rationale
On-demand only (whole workload) $937 + BI on-demand est. $12,000 = ~$13,000 BI dashboard hammered on-demand blows up
Enterprise-only (1000-slot commit) $34,560 Idle slots dominate
Hybrid — on-demand ELT + 200-slot Enterprise BI commit $7,850 Match workload to atom

Rule of thumb. The single biggest bigquery cost win is not picking the newest pricing model — it's mapping each workload to the billing atom that fits its duty cycle. Nightly ELTs almost always cheaper on-demand; always-on BI dashboards almost always cheaper on a small Editions commit.

Worked example — the four axes on a whiteboard

Detailed explanation. A senior interviewer opens with: "You have three workloads — a nightly ELT, a Looker BI dashboard, and ad-hoc analyst queries. Talk me through the pricing model you'd choose for each and why." The right approach is to walk the four axes (unit, elasticity, commit, workload mix) for each workload out loud rather than jumping straight to an answer.

  • Nightly ELT. Unit = bytes scanned (wide, infrequent). Elasticity = high (2 hours, then 0). Commit = none (would waste money on 22 hours idle). Workload mix = isolated.
  • BI dashboard. Unit = slot-time (many small queries, cached data). Elasticity = medium (spike at 09:00 and 14:00). Commit = 1-year on the steady-state. Workload mix = shared with ad-hoc.
  • Ad-hoc analyst queries. Unit = bytes scanned (unpredictable, low volume). Elasticity = high (bursty). Commit = none. Workload mix = shared with BI in same reservation, or separate on-demand project.

Question. For the three-workload scenario, write out the four-axis table and the resulting pricing-model recommendation for each.

Input.

Workload Frequency Bytes/query Duration Concurrency
Nightly ELT 1x/night 500 GB avg 2 hours 800 slots peak
BI dashboard steady, spike at 09:00 / 14:00 5 GB avg (cached) 5 s p95 300 slots p95
Ad-hoc analyst ~50 queries/day 50 GB avg 30 s p95 100 slots p95

Code.

-- Cost estimator: run per-workload monthly
-- Nightly ELT on-demand
SELECT 'nightly_elt' AS workload,
       0.5 * 30 * 6.25 AS monthly_cost_usd;   -- 500 GB * 30 nights * $6.25/TB
-- 93.75

-- BI dashboard on Enterprise 200-slot 1-yr commit
SELECT 'bi_dashboard' AS workload,
       200 * 0.048 * 24 * 30 AS monthly_cost_usd; -- 200 slots * $0.048 * 720h
-- 6912.00

-- Ad-hoc analysts on on-demand
SELECT 'ad_hoc'      AS workload,
       0.05 * 50 * 30 * 6.25 AS monthly_cost_usd; -- 50 GB * 50 q/day * 30 * $6.25/TB
-- 468.75
Enter fullscreen mode Exit fullscreen mode 
Four-axis table for the three workloads
=======================================

Workload      | Unit           | Elasticity     | Commit      | Model
--------------+----------------+----------------+-------------+---------------------------
Nightly ELT   | bytes scanned  | 2h burst       | none        | on-demand
BI dashboard  | slot-time      | steady + spike | 1-yr (200)  | Enterprise reservation
Ad-hoc        | bytes scanned  | bursty         | none        | on-demand
Enter fullscreen mode Exit fullscreen mode

Step-by-step explanation.

  1. Nightly ELT — the unit is bytes scanned because the workload is short and wide; the elasticity is high because it goes from 0 slots to 800 in seconds; a commit would waste the 22 hours it doesn't use. On-demand at ~$94/month is the right answer.
  2. BI dashboard — the unit is slot-time because the workload is 24-hour steady with predictable spikes; the elasticity is medium (BI Engine catches most of the load); a 1-year commit on the steady-state (200 slots) trades ~20% off the sticker rate for a predictable bill.
  3. Ad-hoc analyst queries — like the ELT but chronic and low-volume. On-demand at ~$469/month is dominated by "one bad query" rather than by the steady-state, so the fix is a project-level job_timeout_ms and maximum_bytes_billed cap rather than a model change.
  4. The total: $94 + $6,912 + $469 = $7,475/month. A monolithic "all Enterprise 1000-slot 3-year commit" model would cost $28,800/month — 3.85x more.
  5. The whiteboard signal: the interviewer wants to hear you say "I would size the model per workload, not per account" and "the ELT and the ad-hoc queries live on on-demand; only the BI dashboard justifies a commit." That's the senior answer.

Output.

Workload Model Monthly cost Why
Nightly ELT on-demand $94 Wide, brief, infrequent
BI dashboard Enterprise 200-slot 1-yr commit $6,912 24-hour steady + BI Engine
Ad-hoc on-demand $469 Bursty, low volume
Total hybrid $7,475 Match atom to duty cycle

Rule of thumb. For any three-workload interview scenario, sketch the four-axis table on the whiteboard before writing a single number. The interviewer scores you on the axes, not on the arithmetic.

Worked example — reading INFORMATION_SCHEMA.JOBS_BY_PROJECT for pricing decisions

Detailed explanation. A senior data engineer never guesses at BigQuery cost — they query the audit tables. INFORMATION_SCHEMA.JOBS_BY_PROJECT and JOBS_TIMELINE_BY_PROJECT expose per-job bytes scanned, slot-milliseconds consumed, start / end times, and reservation assignment. Reading them well is a required senior skill and a common interview probe: "how would you know which of your workloads should move off on-demand?"

  • Bytes-scanned column. total_bytes_billed (always ≥ 10 MB per query, the on-demand billing floor).
  • Slot-time column. total_slot_ms — total slot-milliseconds consumed. Divide by (3600 × 1000) for slot-hours.
  • Reservation column. reservation_id — which reservation ran the job, or NULL for on-demand.
  • Duty cycle. Aggregate total_slot_ms per hour to see the shape of the day.

Question. Write the SQL to (a) find the top 10 most expensive on-demand queries in the last 30 days by dollars, (b) compute the average and p95 concurrent slot usage, and (c) recommend a commit size based on the p50 of the daily peak.

Input.

Table Purpose
region-us.INFORMATION_SCHEMA.JOBS_BY_PROJECT Per-job metadata
region-us.INFORMATION_SCHEMA.JOBS_TIMELINE_BY_PROJECT Per-second slot usage
On-demand rate $6.25 / TB

Code.

-- (a) Top 10 most expensive on-demand queries in last 30 days
SELECT
    job_id,
    user_email,
    ROUND(total_bytes_billed / POW(1024, 4), 3) AS tb_billed,
    ROUND(total_bytes_billed / POW(1024, 4) * 6.25, 2) AS estimated_usd,
    SUBSTR(query, 1, 100) AS query_prefix,
    creation_time
FROM   `region-us`.INFORMATION_SCHEMA.JOBS_BY_PROJECT
WHERE  creation_time >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 30 DAY)
  AND  job_type = 'QUERY'
  AND  reservation_id IS NULL          -- on-demand only
  AND  state = 'DONE'
ORDER BY total_bytes_billed DESC
LIMIT 10;
Enter fullscreen mode Exit fullscreen mode 
-- (b) Average and p95 concurrent slot usage per hour
WITH slot_seconds AS (
  SELECT
      TIMESTAMP_TRUNC(period_start, HOUR) AS hour,
      period_slot_ms / 1000.0             AS slots_used_this_second
  FROM   `region-us`.INFORMATION_SCHEMA.JOBS_TIMELINE_BY_PROJECT
  WHERE  period_start >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 30 DAY)
),
per_hour AS (
  SELECT
      hour,
      AVG(slots_used_this_second)                                   AS avg_slots,
      APPROX_QUANTILES(slots_used_this_second, 100)[OFFSET(95)]     AS p95_slots
  FROM   slot_seconds
  GROUP  BY hour
)
SELECT
    ROUND(AVG(avg_slots), 0) AS mean_avg_slots,
    ROUND(AVG(p95_slots), 0) AS mean_p95_slots,
    ROUND(MAX(p95_slots), 0) AS peak_p95_slots
FROM per_hour;
Enter fullscreen mode Exit fullscreen mode 
-- (c) Commit-size recommendation: p50 of daily peaks
WITH daily_peaks AS (
  SELECT
      DATE(period_start)              AS day,
      MAX(period_slot_ms) / 1000.0    AS peak_slots
  FROM   `region-us`.INFORMATION_SCHEMA.JOBS_TIMELINE_BY_PROJECT
  WHERE  period_start >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 30 DAY)
  GROUP  BY day
)
SELECT
    ROUND(APPROX_QUANTILES(peak_slots, 100)[OFFSET(50)], 0) AS commit_slots_p50,
    ROUND(APPROX_QUANTILES(peak_slots, 100)[OFFSET(90)], 0) AS burst_target_p90
FROM   daily_peaks;
Enter fullscreen mode Exit fullscreen mode

Step-by-step explanation.

  1. Query (a) surfaces the "we scanned 400 GB by accident" queries. total_bytes_billed is the billing atom; multiplying by $6.25/TB gives an estimated dollar cost per query. The top 10 usually accounts for 60–80% of the on-demand bill; fix those first.
  2. Query (b) uses JOBS_TIMELINE_BY_PROJECT, which has one row per second-per-project. period_slot_ms / 1000.0 is the slot-count active in that second. APPROX_QUANTILES gives the p95 concurrent slot usage per hour — the shape of the day. Look for a bimodal distribution (steady BI plus spiky ELT) as the strongest signal to split reservations.
  3. Query (c) aggregates the daily peak; taking the p50 of the daily peaks (not the max) sizes the commit for the typical day. Reserve the p50 as baseline; let the autoscaler cover from p50 to p90 at the on-demand slot rate.
  4. Read the outputs together: mean_avg_slots = 120, mean_p95_slots = 340, commit_slots_p50 = 380, burst_target_p90 = 900. Interpretation: commit at ~400 slots on Enterprise, set autoscaler max at 1000, keep on-demand for ad-hoc.
  5. The full loop: audit query → sizing math → reservation config → weekly re-audit. Every senior team runs this loop monthly and reprices as workloads evolve.

Output.

Query Purpose Sample output
(a) top 10 by cost Find expensive queries 5 TB queries dominate
(b) avg + p95 slots Shape the day avg 120, p95 340
(c) commit sizing Right-size reservation p50=380, p90=900

Rule of thumb. No BigQuery pricing decision should be made without a JOBS_BY_PROJECT and JOBS_TIMELINE_BY_PROJECT audit. Every senior team has a saved query in their _metrics dataset that runs these three snippets weekly.

Senior interview question on the pricing surface

A senior interviewer often opens with: "Walk me through the BigQuery pricing landscape in 2026. What are the atoms, what are the tiers, what are the commit options, and how do you decide which workload goes on which model? Be specific about numbers."

Solution Using the four-axis walk plus the workload-to-model map 

-- Full pricing map — one workload per row, one model per row
WITH workloads AS (
  SELECT 'nightly_elt' AS name, 'bytes' AS unit, 'high' AS burst, 'none'  AS commit_pref, 'on-demand'      AS model UNION ALL
  SELECT 'bi_looker',           'slot',           'steady', '1-yr',                       'Enterprise 200 slots' UNION ALL
  SELECT 'ad_hoc',              'bytes',          'high',   'none',                       'on-demand'         UNION ALL
  SELECT 'ml_training',         'slot',           'burst',  '1-yr',                       'Enterprise Plus 500 slots (CMEK req.)' UNION ALL
  SELECT 'cdc_streaming',       'slot',           'steady', '3-yr',                       'Standard 100 slots'
)
SELECT * FROM workloads;
Enter fullscreen mode Exit fullscreen mode 
The four-axis walk (verbal)
===========================

Atom 1 — bytes scanned
  Model: on-demand at $6.25/TB in most regions.
  Right for: unpredictable, low volume, ad-hoc, one-time ELTs.
  Wrong for: 24-hour steady dashboards.
  Ceiling: default 2000-slot per-project burst cap.

Atom 2 — slot-time
  Model: Editions (Standard/Enterprise/Enterprise Plus) on a reservation.
  Standard   ~$0.04/slot-hour  — vanilla SQL, streaming, no BI Engine.
  Enterprise ~$0.06/slot-hour  — BI Engine, ML, materialised views, external tables.
  Ent. Plus  ~$0.10/slot-hour  — CMEK, VPC-SC, cross-region replication, DR reservations.

Commit layer
  No commit         — 0% discount, pay slot-hour rate.
  1-year commit     — ~20% discount on the committed slot baseline.
  3-year commit     — ~40% discount on the committed slot baseline.
  Burst above baseline pays the on-demand slot rate.

Workload map (per project)
  nightly ELT       → on-demand
  BI Looker         → Enterprise reservation, 200-slot 1-yr commit, autoscaler max 1000
  Ad-hoc analysts   → on-demand + per-user maximum_bytes_billed cap
  ML training       → Enterprise Plus (CMEK) reservation, 500-slot 1-yr commit
  CDC streaming     → Standard reservation, 100-slot 3-yr commit
Enter fullscreen mode Exit fullscreen mode

Step-by-step trace.

Axis Question Answer for this deployment
Unit Bytes or slot-time? Both — mixed per workload
Elasticity Steady or bursty? Bursty ELT + ad-hoc, steady BI + CDC
Commit None / 1-yr / 3-yr? Mixed — 3-yr for CDC baseline, 1-yr for BI + ML
Workload mix One reservation or many? 3 reservations + 2 on-demand projects

After the map is drawn, the interviewer knows you (a) understand both billing atoms, (b) can name the three Editions and their price points, (c) size commits per workload rather than per account, and (d) reach for on-demand for burst rather than growing the commit. The follow-ups are usually specific — "walk me through the on-demand math" or "how do you size the Enterprise commit" — which the next four H2s answer.

Output:

Element Value
Distinct billing atoms 2 (bytes, slot-time)
Editions tiers 3 (Standard, Enterprise, Enterprise Plus)
Commit options 3 (none, 1-yr, 3-yr)
Reservations in this deployment 3
On-demand projects in this deployment 2
Estimated blended saving vs monolithic 3-yr commit ~55%

Why this works — concept by concept:

  • Bytes vs slot-time — naming both atoms is the senior signal. Every downstream decision (commit or not, which Edition, autoscaler shape) reduces to which atom the workload is billed on.
  • Editions as tiers, not choices — Standard / Enterprise / Enterprise Plus are ordered by feature surface (BI Engine at Enterprise, CMEK at Enterprise Plus). Never pick Enterprise Plus for cost reasons; only for compliance.
  • Commit as a layer — 1-year and 3-year commits sit on top of the slot rate. The commit discount applies to the baseline, not to burst. Burst above the commit pays on-demand rates — this is the safety valve that makes right-sizing safe.
  • Workload map — the senior answer never picks one model for the whole account. Reservations and assignments are how you keep the maps clean.
  • Cost — the four-axis walk is O(1) per workload — a 15-minute exercise. The saved cost is O(workload duty cycle) per month. A 5-workload account with an all-Enterprise 3-yr commit typically saves 40–60% by moving to the hybrid map.

SQL
Topic — sql
SQL cost-attribution and pricing problems

Practice →

Optimization Topic — optimization Optimization problems on BigQuery slot and bytes tuning

Practice →

2. On-demand — bytes scanned

bigquery on-demand bills you $6.25 per TB scanned — every column pruning, partition filter, and clustering choice is a line item

The mental model in one line: bigquery on-demand charges you a flat rate per byte the query engine reads from columnar storage, ignoring how many slots the query used and how long it took — so the entire optimisation surface for on-demand cost is reducing the bytes scanned, not tuning the query plan. The interviewer probes the on-demand model with a series of increasingly specific traps: "why does SELECT * on a partitioned table cost the same as without a WHERE clause?", "what's the on-demand cost of joining a 5 TB fact to a 100 GB dim?", "how does require_partition_filter protect against runaway cost?".

On-demand — bytes scanned = billing atom. Iconographic diagram showing a source table card with column strips (three highlighted as scanned, two dim as skipped by partition prune), a dollar meter with $6.25/TB, and a partitioned-query card with WHERE partition_date IN (…) on a light PipeCode card.

The four axes of the on-demand model.

  • Unit. Bytes read from storage, columnar. Only the columns referenced in SELECT, WHERE, GROUP BY, ORDER BY, JOIN ON, or window functions are billed — the rest are skipped by the columnar reader. This is why SELECT col1, col2 on a 100-column table costs 2% of SELECT *.
  • Elasticity. Implicit — BigQuery auto-provisions slots up to a per-project burst cap (default 2000 slots). You don't manage slots; the engine picks. The trade-off is that slot contention across concurrent queries can queue small queries behind a big one.
  • Commit. None. You pay per query. There is no discount for volume; the bytes-scanned rate is flat across the month regardless of usage.
  • Workload mix. On-demand is a per-project pricing setting. A single project is either on-demand or on a reservation — not both. Mixed workloads use separate projects that share datasets.

When on-demand is the right answer.

  • Unpredictable, low volume. A team of 10 analysts running maybe 100 queries a day, mostly cached in BI Engine. The peak concurrency is far below the burst cap; the total monthly spend is dominated by a handful of wide scans.
  • Ad-hoc ELTs. A one-time backfill, a manual data-science exploration, a bug-repro query — anything you cannot forecast. The commit break-even is very high for infrequent workloads.
  • Bursty jobs with <10% duty cycle. The nightly ELT that runs for 2 hours a day. Committing to a 24-hour reservation would waste 22 hours of slot-time; on-demand rewards the bursty shape.

When on-demand fails.

  • Heavy repeated queries. A dashboard that queries the same table 1000 times per hour — bytes are billed again on each cache miss. Move to Editions with BI Engine.
  • Hot slot needs. A query that regularly hits the 2000-slot per-project burst cap. On-demand queues; Editions with a higher max_slots runs concurrently.
  • Wide scans as the norm. Any dashboard or ELT that regularly scans multiple TB per query. Cost is linear in bytes; slot cost is bounded by commit + burst.
  • Cost predictability. Finance teams want monthly-flat spend. On-demand is inherently volatile (one bad query can blow the budget); Editions bill on hours, not queries.

The three "cost surprise" patterns on on-demand.

  • The SELECT * pattern. Every column is billed even if only two are used downstream. The fix is SELECT col1, col2 and, better, SELECT ... EXCEPT(col_never_needed).
  • The unfiltered partition pattern. A query on a partitioned table without a partition filter scans every partition — usually the whole table. The fix is require_partition_filter = TRUE at table creation, which errors out queries that don't filter.
  • The wide-join pattern. SELECT * FROM fact JOIN dim ON ... bills both tables' full columnar footprints. The fix is projecting each side down to only the columns needed.

The maximum_bytes_billed safety knob.

  • Query-level. SET @@job_config.maximum_bytes_billed = 10 * POW(1024, 4); caps a session at 10 TB per query. Any query estimated above the cap fails immediately.
  • Project-level. Set via bq CLI or Terraform. Applies to all queries in the project — the strongest per-project cost guardrail.
  • User-level. Set via bq and IAM binding. Different analysts get different caps.

Common interview probes on bigquery on-demand.

  • "How does BigQuery bill columnar storage?" — only referenced columns; no billing for unused columns.
  • "How does partition prune reduce cost?" — WHERE on the partition column narrows the file set; unread files are unbilled.
  • "What's the difference between total_bytes_processed and total_bytes_billed?" — billed rounds up to 10 MB minimum per query.
  • "How would you enforce a per-analyst cost cap?" — maximum_bytes_billed at project level + per-user role bindings.

Worked example — partitioned query, 5 TB table, 50 GB scanned

Detailed explanation. The classic on-demand math example: a 5 TB event table partitioned by event_date. Query the last 24 hours. Show the bytes-scanned reduction from partition prune and the resulting cost.

  • Table. 5 TB total, partitioned daily, 90 days of history → ~55 GB per partition.
  • Query. SELECT with WHERE event_date = DATE '2026-07-05'.
  • Bytes scanned. ~50 GB (one partition), not 5 TB (full scan).
  • Cost. 50 GB × $6.25/TB / 1024 = $0.30 per query.

Question. For a 5 TB event table partitioned by day, calculate the on-demand cost per query for (a) SELECT *, (b) SELECT event_id, user_id, event_type, and (c) the same with a WHERE event_date = DATE '2026-07-05' partition filter.

Input.

Parameter Value
Table size (uncompressed) 5 TB
Rows 10 billion
Columns 30
Partition column event_date
Partitions 90 (daily)
Bytes per partition ~55 GB
Bytes per target column (3 of 30) ~5.5 GB per partition
Rate $6.25 / TB

Code.

-- Set the safety cap for the session
SET @@dataset_project_id = 'analytics-prod';

-- Query (a) — SELECT * with no partition filter
SELECT *
FROM   `analytics-prod.events.events_raw`
WHERE  user_id = 42
LIMIT  100;
-- Dry run: 5 TB scanned. Cost = 5 * $6.25 = $31.25 per query.

-- Query (b) — column projection only, still no partition filter
SELECT event_id, user_id, event_type
FROM   `analytics-prod.events.events_raw`
WHERE  user_id = 42
LIMIT  100;
-- Dry run: 500 GB scanned (3/30 columns * 5 TB). Cost = 0.5 * $6.25 = $3.13 per query.

-- Query (c) — column projection + partition prune
SELECT event_id, user_id, event_type
FROM   `analytics-prod.events.events_raw`
WHERE  event_date = DATE '2026-07-05'
  AND  user_id = 42
LIMIT  100;
-- Dry run: 5.5 GB scanned (3/30 columns * 55 GB/partition * 1 partition).
-- Cost = 0.0055 * $6.25 = $0.03 per query.
Enter fullscreen mode Exit fullscreen mode 
# Dry-run the queries to preview bytes billed before spending
bq query --use_legacy_sql=false --dry_run \
  'SELECT event_id, user_id, event_type
   FROM `analytics-prod.events.events_raw`
   WHERE event_date = DATE "2026-07-05" AND user_id = 42
   LIMIT 100'
# Query successfully validated. Assuming the tables are not modified,
# running this query will process 5905580032 bytes of data.  ← 5.5 GB
Enter fullscreen mode Exit fullscreen mode

Step-by-step explanation.

  1. Query (a) SELECT * reads every column of every partition — 5 TB total. On-demand bills $31.25 per query. A dashboard running this every 5 minutes costs $9,000/day. The classic on-demand disaster.
  2. Query (b) SELECT event_id, user_id, event_type reads only 3 of 30 columns. Columnar storage means only 3/30 = 10% of the bytes are read. Cost drops to $3.13/query. This is column projection — the single biggest lever after partition prune.
  3. Query (c) adds WHERE event_date = DATE '2026-07-05'. The partition filter narrows the file set from 90 partitions to 1 — a 90x reduction. Cost drops to $0.03/query. This is partition prune — the largest single lever in on-demand tuning.
  4. Combined effect: $31.25 → $0.03 = 1000x cost reduction from column projection + partition prune. On a busy dashboard, that's the difference between a $9,000/day disaster and a $9/day rounding error.
  5. The always-preview rule: bq query --dry_run reports total_bytes_processed before you spend. Every ELT job runner and every analyst tool should dry-run before executing; the estimated bytes are the interview answer for "how would you catch cost surprises before they happen".

Output.

Query Bytes scanned Cost per query Cost per 1000 queries
SELECT *, no partition filter 5 TB $31.25 $31,250
SELECT 3 cols, no partition filter 500 GB $3.13 $3,125
SELECT 3 cols, WHERE partition = 1 day 5.5 GB $0.03 $34

Rule of thumb. On on-demand, the two levers that matter are column projection and partition prune. A 1000x cost reduction is a one-line query rewrite — always audit dashboards for SELECT * and unfiltered partitioned reads first.

Worked example — enforcing require_partition_filter on a fact table

Detailed explanation. The best defence against unfiltered partition reads is require_partition_filter = TRUE — a table-level setting that errors any query missing a partition filter. Ship it at CREATE TABLE time; retrofit it via ALTER TABLE with careful rollout. Walk through the two forms and the exception handling.

  • The setting. OPTIONS(require_partition_filter=TRUE) on the partitioned table.
  • The error. Queries against table X must include a partition filter that can be used for pruning.
  • The rollout risk. Existing queries break the moment the setting is applied.
  • The mitigation. Audit INFORMATION_SCHEMA.JOBS_BY_PROJECT for the last 30 days of queries against the table; fix any that would break before flipping the flag.

Question. Write the DDL to create a partitioned event table with require_partition_filter, then write the audit SQL to find any existing queries that would fail if the flag were enabled today.

Input.

Element Value
Table analytics-prod.events.events_raw
Partition column event_date
Retention 90 days
Audit window last 30 days

Code.

-- (1) Create the table with the guard rail from day 1
CREATE TABLE `analytics-prod.events.events_raw` (
  event_id     STRING NOT NULL,
  user_id      INT64,
  event_type   STRING,
  payload      JSON,
  event_date   DATE,
  event_ts     TIMESTAMP
)
PARTITION BY event_date
CLUSTER BY user_id, event_type
OPTIONS (
  require_partition_filter = TRUE,
  partition_expiration_days = 90,
  description = 'Raw event log — must include event_date filter'
);
Enter fullscreen mode Exit fullscreen mode 
-- (2) Retrofit onto an existing table
ALTER TABLE `analytics-prod.events.events_raw`
SET OPTIONS (require_partition_filter = TRUE);
Enter fullscreen mode Exit fullscreen mode 
-- (3) Audit — find queries against the table that don't filter on event_date
WITH recent_jobs AS (
  SELECT
      job_id,
      user_email,
      query,
      referenced_tables,
      creation_time
  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'
)
SELECT
    job_id,
    user_email,
    SUBSTR(query, 1, 200) AS query_prefix
FROM   recent_jobs
WHERE
    EXISTS (
      SELECT 1 FROM UNNEST(referenced_tables) AS t
      WHERE  t.table_id = 'events_raw'
    )
    AND NOT REGEXP_CONTAINS(LOWER(query), r'event_date\s*(=|>=|<=|<|>|between|in\s*\()')
ORDER BY creation_time DESC;
Enter fullscreen mode Exit fullscreen mode

Step-by-step explanation.

  1. The CREATE TABLE includes require_partition_filter = TRUE from day one. Queries against the table must include a WHERE clause on event_date (or a subquery on the partition column). If not, BigQuery rejects the query at plan time — no bytes scanned, no cost.
  2. The clustering (CLUSTER BY user_id, event_type) is a second cost lever — within a partition, blocks are sorted so a WHERE user_id = 42 further reduces scan. Clustering is free at write time and reduces bytes at read time.
  3. partition_expiration_days = 90 is the storage-cost lever: partitions older than 90 days are automatically deleted. Storage is charged separately (~$0.02/GB/month for active, ~$0.01 for long-term after 90 days), so retention policies also directly control the bill.
  4. The retrofit ALTER TABLE flips the flag on an existing table. This is safe only after the audit (query 3) confirms no existing queries would break. Roll out in a maintenance window; monitor query error rates for 24 hours.
  5. The audit query (3) inspects JOBS_BY_PROJECT. referenced_tables is an array of tables the query touched; the WHERE clause filters to jobs against events_raw. The regex ensures the query text mentions event_date with an operator. Queries missing this regex would fail under the new flag.

Output.

Step Purpose Result
CREATE TABLE with flag Day-1 guard rail New queries can't skip filter
ALTER TABLE retrofit Add flag to existing table Existing queries must be audited first
Audit query Find queries that would break 12 jobs from 3 users flagged

Rule of thumb. Every partitioned fact table in a senior BigQuery deployment ships with require_partition_filter = TRUE on day one. Retrofits are safe only after a JOBS_BY_PROJECT audit — never flip the flag blindly.

Worked example — a wide join that surprises the on-demand bill

Detailed explanation. The join-cost trap: SELECT * FROM fact JOIN dim ON fact.dim_id = dim.dim_id. Both tables' columnar footprints are billed for every column referenced. Fact is 5 TB with 50 columns; dim is 100 GB with 20 columns. The naive join scans all 5.1 TB. The fix is projecting each side.

  • Fact. 5 TB, 50 columns, only 3 needed.
  • Dim. 100 GB, 20 columns, only 2 needed.
  • Naive cost. 5.1 TB × $6.25/TB = $31.88 per query.
  • Optimised cost. (5 TB × 3/50) + (100 GB × 2/20) = 300 GB + 10 GB = 310 GB. Cost = $1.94.

Question. Rewrite the naive SELECT * join to project each side down to the columns actually needed, then walk through the bytes-billed math.

Input.

Table Size Columns Columns needed
fact_orders 5 TB 50 order_id, user_id, ts
dim_users 100 GB 20 user_id, region

Code.

-- Naive — SELECT *, both sides fully scanned (also no partition filter — double trap)
SELECT *
FROM   `analytics-prod.orders.fact_orders` f
JOIN   `analytics-prod.users.dim_users`    u
       ON f.user_id = u.user_id;
-- Dry run: 5.1 TB scanned. Cost $31.88/query.

-- Optimised — project each side + partition filter on the fact
SELECT
    f.order_id,
    f.user_id,
    f.ts,
    u.region
FROM   `analytics-prod.orders.fact_orders` f
JOIN   `analytics-prod.users.dim_users`    u
       ON f.user_id = u.user_id
WHERE  f.order_date = DATE '2026-07-05';
-- Dry run: 15.5 GB scanned (5 TB * 3/50 / 90 partitions + 100 GB * 2/20).
-- Cost $0.10/query. 320x cheaper than the naive version.
Enter fullscreen mode Exit fullscreen mode 
-- The dim-side projection can be pushed into a subquery
SELECT
    f.order_id,
    f.user_id,
    f.ts,
    u.region
FROM   `analytics-prod.orders.fact_orders` f
JOIN   (SELECT user_id, region FROM `analytics-prod.users.dim_users`) u
       ON f.user_id = u.user_id
WHERE  f.order_date = DATE '2026-07-05';
-- Bytes-billed identical; more explicit and safer if the dim gains a wide column later.
Enter fullscreen mode Exit fullscreen mode

Step-by-step explanation.

  1. The naive SELECT * scans the full fact + full dim. On-demand bills both — 5 TB + 0.1 TB = 5.1 TB = $31.88 per query. If a dashboard runs it 100 times per day, that's $3,188/day just on this one join.
  2. The optimised version explicitly names the 3 fact columns and 2 dim columns. BigQuery's columnar reader skips the other 45 fact columns and 18 dim columns. Bytes scanned drop to 300 GB + 10 GB = 310 GB — a 16x reduction on the join columns alone.
  3. Adding the partition filter on order_date reduces the fact scan by another 90x (one of 90 daily partitions). Combined effect: 320x cheaper than the naive version. Same result rows, same result table.
  4. The subquery-projected variant is a defensive pattern — if the dim table gains a wide payload JSON column later, the outer SELECT u.region might still pull it. Wrapping the dim in a subquery that lists exactly the needed columns is a cheap insurance policy.
  5. The senior interview answer: always project both sides of a join and always partition-filter the fact side. The dim side rarely needs partition filtering because dims are small; the fact side almost always dominates the bill.

Output.

Variant Bytes scanned Cost Speedup vs naive
Naive SELECT * 5.1 TB $31.88 1x
Column-projected 310 GB $1.94 16x
Column-projected + partition filter 15.5 GB $0.10 320x

Rule of thumb. Every join in an on-demand deployment gets both sides projected and the fact side partition-filtered. If a senior asks "what's the on-demand cost of your join," the answer is never "I don't know" — it's a dry-run number in bytes.

Senior interview question on bigquery on-demand

A senior interviewer might ask: "You inherit an on-demand-only project that's burning $80,000/month. Walk me through the audit, the top three fixes you'd apply, and the guard rails you'd ship so this doesn't happen again."

Solution Using audit + partition + projection + safety cap 

-- Step 1 — audit: top on-demand cost queries in the last 30 days
CREATE OR REPLACE TABLE `analytics-prod._metrics.on_demand_cost_audit` AS
WITH jobs AS (
  SELECT
      job_id,
      user_email,
      query,
      total_bytes_billed,
      total_bytes_billed / POW(1024, 4) * 6.25 AS estimated_usd,
      total_slot_ms,
      creation_time,
      referenced_tables
  FROM   `region-us`.INFORMATION_SCHEMA.JOBS_BY_PROJECT
  WHERE  creation_time >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 30 DAY)
    AND  job_type = 'QUERY'
    AND  reservation_id IS NULL
    AND  state = 'DONE'
)
SELECT * FROM jobs;

-- Attribute cost to the top offenders
SELECT
    user_email,
    ROUND(SUM(estimated_usd), 2)                          AS user_monthly_usd,
    ROUND(SUM(estimated_usd) / 80000.0 * 100, 1)          AS pct_of_bill,
    COUNT(*)                                              AS query_count
FROM   `analytics-prod._metrics.on_demand_cost_audit`
GROUP  BY user_email
ORDER  BY user_monthly_usd DESC
LIMIT  20;
Enter fullscreen mode Exit fullscreen mode 
-- Step 2 — the top 3 fixes surface from the audit

-- Fix (a) — enforce partition filters on the fact tables
ALTER TABLE `analytics-prod.orders.fact_orders`
SET OPTIONS (require_partition_filter = TRUE);

ALTER TABLE `analytics-prod.events.events_raw`
SET OPTIONS (require_partition_filter = TRUE);

-- Fix (b) — kill SELECT * dashboards; wrap dims in projected subqueries
CREATE OR REPLACE VIEW `analytics-prod.orders.fact_orders_projection` AS
SELECT order_id, user_id, ts, order_date, amount_usd
FROM   `analytics-prod.orders.fact_orders`;
-- Force dashboards to use the projection view; the underlying table is not directly accessed.

-- Fix (c) — set project-wide maximum_bytes_billed and per-user cost caps via IAM
Enter fullscreen mode Exit fullscreen mode 
# Step 3 — safety cap at project level
bq update --maximum_bytes_billed=1099511627776 analytics-prod
# 1 TB cap per query; anything larger fails before running.

# Per-user cap via custom-role binding (extract only the caps knob)
gcloud iam roles create bqOnDemandGuarded --project=analytics-prod \
  --title="BQ user with 100GB per-query cap" \
  --permissions=bigquery.jobs.create,bigquery.tables.getData

# Enforce quota via BigQuery custom quotas API — 100 GB per user per day
Enter fullscreen mode Exit fullscreen mode

Step-by-step trace.

Step Action Effect
1. Audit JOBS_BY_PROJECT top-20 by cost Identifies 4 users owning 80% of spend
2a. Partition filter require_partition_filter = TRUE on 2 facts Kills 90x-scan queries at plan time
2b. Projection views analytics-prod.orders.fact_orders_projection Blocks SELECT * on wide tables
2c. Safety cap maximum_bytes_billed = 1 TB Any single query > 1 TB fails
3. Per-user quota 100 GB/user/day Analyst experiments capped

After 30 days on the guard rails, the monthly on-demand bill drops from $80,000 to $12,000. The remaining $12,000 is the real workload cost; the $68,000 delta was SELECT * on partitioned facts, wide joins with no projection, and one analyst running a 40 TB backfill by mistake.

Output:

Metric Before After 30 days
Monthly on-demand cost $80,000 $12,000
Top query cost 40 TB ($250) 1 TB ($6.25)
Queries failing at plan time (guard rails) 0 ~5% (blocked wide scans)
Users over 100 GB/day quota ~40% <2%

Why this works — concept by concept:

  • Audit before act — JOBS_BY_PROJECT is the source of truth for on-demand cost. Never propose a fix without the audit that scopes it. Fix the queries responsible for 80% of the spend first; the long tail rarely justifies the effort.
  • require_partition_filter — the single most effective guard rail. A partition filter on the fact side is a 90x scan reduction on a 90-day partitioned table. Rejecting queries at plan time costs 0 bytes.
  • Projection views — a view that projects a wide fact to only the columns dashboards need. Analysts query the view; the underlying wide table is never touched by dashboards. Cheap defence in depth.
  • maximum_bytes_billed — the project-level circuit breaker. A misconfigured query estimated at 40 TB fails at $0. Set it to your worst-plausible legitimate query.
  • Per-user quotas — 100 GB/user/day via custom quotas. Individual analysts cannot blow the bill; heavy queries need approval or a project switch.
  • Cost — audit is O(days) once; guard rails are O(1) per table forever. The saved cost is O(bad-queries-per-month). A single ALTER TABLE + one view + one project-level cap typically saves 80% of the bill on an over-scanned on-demand deployment.

SQL
Topic — sql
SQL partition prune and columnar projection problems

Practice →

Optimization Topic — optimization Optimization problems on BigQuery bytes-scanned tuning

Practice →

3. Editions — Standard / Enterprise / Enterprise Plus

bigquery editions bills you for slot-time, not bytes — the tier picks your features, the autoscaler picks your slots

The mental model in one line: bigquery editions replace the bytes-scanned billing atom with a slot-time atom (one slot × one hour = one slot-hour), and split the surface across three feature tiers (Standard, Enterprise, Enterprise Plus) each with its own per-slot-hour rate — the tier decides what Postgres-level features you get, the reservation decides how many slots you rent, the autoscaler decides how much you burst. Every Editions interview question reduces to which tier picks up the features you need, at what committed baseline, with how much autoscaler headroom.

Editions — Standard · Enterprise · Enterprise Plus. Iconographic diagram showing three tiered cards side-by-side (Standard 200 baseline / Enterprise 500 baseline / Enterprise Plus 1000 baseline) with feature-icon strips and small autoscaler dials, on a light PipeCode card.

The three Editions tiers.

  • Standard Edition (~$0.04/slot-hour, US multi-region). Bare-bones: standard SQL, streaming ingest, batch load, materialised views (basic), scheduled queries. No BI Engine, no external tables to Iceberg/Delta, no BigQuery ML feature engineering, no CMEK, no VPC-SC. The right tier only when you're cost-sensitive on a workload that doesn't need any of those features.
  • Enterprise Edition (~$0.06/slot-hour). Adds BI Engine (memory-resident cache for dashboards), external tables (BigLake, Iceberg, Delta), BigQuery ML (train + serve), row-level security, column-level security, and Data QnA. This is the "default" tier for real analytics deployments.
  • Enterprise Plus Edition (~$0.10/slot-hour). Adds CMEK, VPC Service Controls, cross-region replication, extended time travel (up to 7 days), disaster-recovery reservations, and Data Editor confidentiality features. The right tier only when compliance, DR, or extended history is a hard requirement — not for cost or performance.

The bigquery autoscaler.

  • Baseline. The number of slots always available in the reservation (0 or more). Commit for 1-year or 3-year to discount the baseline.
  • Max. The upper bound on autoscaler slots — how far the reservation can burst. The autoscaler adds slots in increments of 100 (typical) when queued slot-time exceeds a threshold; it removes them when demand drops.
  • Scaling factor. How fast the autoscaler grows and shrinks. Growth is typically 5–10 seconds per 100 slots; shrink is slower to avoid thrashing.
  • The bill. Baseline slot-hours are billed at the committed rate (with commit discount). Autoscaler slot-hours above the baseline are billed at the un-committed slot-hour rate (no discount).

The commit layer.

  • No commit (0% discount). Pure autoscaler; you pay the slot-hour rate on every slot-hour used. The right shape for exploratory or non-critical workloads.
  • 1-year commit (~20% discount). Lock in a baseline slot count for 12 months. The commit rate applies only to the baseline; burst above baseline pays the un-committed rate.
  • 3-year commit (~40% discount). Same shape, deeper discount. Only worth it if the baseline is confidently stable for 3 years.

When to pick which Edition.

  • Standard. CDC streaming that terminates at a raw layer, batch loads, low-frequency scheduled queries. Small teams with no BI or ML needs.
  • Enterprise. Any workload with a dashboard on BigQuery data — BI Engine alone typically pays for the tier upgrade in latency reduction. Any BQ ML workload. Any external-table read (Iceberg / Delta / Hudi).
  • Enterprise Plus. Only when compliance (CMEK, VPC-SC) or DR (cross-region) is a hard requirement.

Common interview probes on Editions.

  • "Why is Enterprise more expensive than Standard?" — feature-set, specifically BI Engine + ML + external tables.
  • "How does the autoscaler bill above the baseline?" — at the un-committed slot-hour rate; no discount.
  • "When do you pick Enterprise Plus?" — compliance or DR; never for cost.
  • "Can you mix Standard and Enterprise reservations in the same project?" — yes, via separate reservations + assignments; different workloads on different tiers.

Worked example — Standard vs Enterprise for a nightly ELT

Detailed explanation. A nightly ELT runs 4 hours (23:00–03:00 UTC) at 500-slot peak. It reads external Iceberg tables and writes back to native BigQuery. Iceberg reads require Enterprise or Enterprise Plus. Show the cost comparison assuming Enterprise is required by the feature set.

  • Workload. 500 slots for 4 hours × 30 nights = 60,000 slot-hours per month.
  • Standard. Not possible — Iceberg reads require Enterprise.
  • Enterprise, no commit. 60,000 × $0.06 = $3,600/month.
  • Enterprise, 1-year commit for 200 slots baseline. 200 × 0.048 × 24 × 30 = $6,912 baseline, plus (300 × 4 × 30) = 36,000 burst slot-hours × $0.06 = $2,160. Total = $9,072. Worse than pure autoscaler.
  • Enterprise, autoscaler-only (baseline 0, max 1000). All 60,000 slot-hours billed at $0.06 = $3,600.

Question. For a 500-slot 4-hour nightly ELT that requires Iceberg reads, calculate the cost under (a) pure autoscaler, (b) 200-slot 1-year commit + autoscaler burst, and (c) 500-slot 1-year commit + no burst. Recommend one.

Input.

Parameter Value
Peak slots 500
Hours per night 4
Nights per month 30
Enterprise slot-hour rate $0.06
1-year commit discount 20%
Committed rate $0.048

Code.

-- (a) Pure autoscaler — Enterprise, baseline 0, max 1000, no commit
WITH scenario_a AS (
  SELECT 500 AS peak_slots, 4 AS hrs_night, 30 AS nights,
         0.06 AS rate
)
SELECT peak_slots * hrs_night * nights * rate AS monthly_cost_usd
FROM   scenario_a;
-- 3600.00

-- (b) 200-slot 1-year commit + burst
WITH scenario_b AS (
  SELECT 200 AS baseline_slots, 500 AS peak_slots,
         4 AS hrs_night, 30 AS nights,
         0.048 AS committed_rate, 0.06 AS burst_rate
)
SELECT
    baseline_slots * 24 * 30 * committed_rate           AS baseline_cost,
    (peak_slots - baseline_slots) * hrs_night * nights
                                   * burst_rate         AS burst_cost,
    baseline_slots * 24 * 30 * committed_rate
    + (peak_slots - baseline_slots) * hrs_night * nights * burst_rate
                                                        AS total_cost
FROM scenario_b;
-- baseline_cost = 6912.00
-- burst_cost    = 2160.00
-- total_cost    = 9072.00

-- (c) 500-slot 1-year commit, no burst needed
WITH scenario_c AS (
  SELECT 500 AS baseline_slots, 0.048 AS committed_rate
)
SELECT baseline_slots * 24 * 30 * committed_rate AS monthly_cost_usd
FROM   scenario_c;
-- 17,280.00
Enter fullscreen mode Exit fullscreen mode

Step-by-step explanation.

  1. Pure autoscaler wins at $3,600/month because the workload has a duty cycle of 4/24 = 17%. Committing to a baseline pays for 24 hours; the workload only needs 4. Every idle hour on a commit is a wasted dollar.
  2. Scenario (b) — 200-slot commit + 300-slot burst — costs $9,072, or 2.5x the pure autoscaler. The commit discount (20% off) does not offset the 22 idle hours per day. Commits pay off only when duty cycle > ~50%.
  3. Scenario (c) — 500-slot commit — costs $17,280, or 4.8x the pure autoscaler. Committing to the peak is the classic "burnt money" mistake in BigQuery cost tuning.
  4. The right answer for this workload is (a): Enterprise Edition (required for Iceberg), baseline 0, max 1000, no commit. Total cost $3,600/month.
  5. The general lesson: commits are for workloads with high duty cycle (BI dashboards, CDC streaming); autoscaler-only is for bursty workloads (ELTs, ad-hoc). Never commit to the peak; commit to the p50 of steady state if you commit at all.

Output.

Scenario Setup Monthly cost
(a) Pure autoscaler baseline 0, max 1000 $3,600
(b) Small commit + burst 200 committed + 300 burst $9,072
(c) Full-peak commit 500 committed $17,280

Rule of thumb. For workloads with duty cycle < 30%, pure autoscaler on Editions beats any commit. Commit only when the baseline is used ≥ 50% of the time; otherwise you pay for slots you don't need.

Worked example — baseline 200 + max 2000 for a BI + ad-hoc reservation

Detailed explanation. The other side: a workload where a commit clearly wins. A BI Looker dashboard serves ~200 slots steadily 09:00–21:00 UTC (12 hours × 200 slots = 2,400 slot-hours per day). Ad-hoc analyst queries add ~300 slots for 3 hours at midday (900 slot-hours). Nightly a Looker refresh spikes to 1500 slots for 30 minutes (750 slot-hours). Total ~4,050 slot-hours per day; monthly ~121,500. Peak concurrent slots: 1500.

  • Autoscaler-only. 121,500 × $0.06 = $7,290/month.
  • 200-slot 1-year commit + autoscaler. Baseline: 200 × 24 × 30 × $0.048 = $6,912. Above-baseline: (121,500 - 200×24×30) × $0.06 = (121,500 - 144,000) → the workload fits inside baseline slot-hours! Total = $6,912 flat.
  • Wait — 200 slots × 720 hours = 144,000 slot-hours = capacity of the commit. Workload consumes 121,500. All work fits within baseline. Actual bill = $6,912.

Question. Design the reservation for this BI + ad-hoc + refresh mix: choose baseline, max, and commit; calculate the cost.

Input.

Component Slots Hours/day Slot-hours/day
Steady BI 200 12 2,400
Midday ad-hoc 300 3 900
Nightly refresh (peak) 1500 0.5 750
Total daily slot-hours — — 4,050
Total monthly slot-hours — — 121,500

Code.

-- Reservation config (using bq CLI syntax for illustration)
-- Create a 200-slot Enterprise reservation with 1-year commit, autoscaler max 2000
Enter fullscreen mode Exit fullscreen mode 
# 1-year 200-slot commit at Enterprise
bq mk --project_id=analytics-prod \
      --location=us \
      --reservation \
      --edition=ENTERPRISE \
      --slots=200 \
      --autoscale_max_slots=2000 \
      --plan=ANNUAL \
      bi_ad_hoc_shared

# Assign the BI project + the analyst-project to this reservation
bq mk --project_id=analytics-prod \
      --location=us \
      --reservation_assignment \
      --reservation_id=bi_ad_hoc_shared \
      --job_type=QUERY \
      --assignee_id=analytics-prod \
      --assignee_type=PROJECT
Enter fullscreen mode Exit fullscreen mode 
-- Cost calculation
WITH cfg AS (
  SELECT 200   AS baseline,
         2000  AS max_slots,
         0.048 AS committed_rate,
         0.06  AS on_demand_slot_rate,
         121500 AS monthly_slot_hours
)
SELECT
    baseline * 24 * 30                             AS baseline_slot_hours,
    baseline * 24 * 30 * committed_rate            AS baseline_cost,
    GREATEST(monthly_slot_hours
             - baseline * 24 * 30, 0)              AS burst_slot_hours,
    GREATEST(monthly_slot_hours
             - baseline * 24 * 30, 0)
             * on_demand_slot_rate                 AS burst_cost,
    baseline * 24 * 30 * committed_rate
    + GREATEST(monthly_slot_hours
               - baseline * 24 * 30, 0)
             * on_demand_slot_rate                 AS total_cost
FROM cfg;
-- baseline_slot_hours = 144,000
-- baseline_cost       = 6,912.00
-- burst_slot_hours    = 0    (workload fits inside baseline)
-- burst_cost          = 0
-- total_cost          = 6,912.00
Enter fullscreen mode Exit fullscreen mode

Step-by-step explanation.

  1. The 200-slot commit gives you 200 × 720 = 144,000 slot-hours of baseline per month. The workload consumes 121,500. All work fits inside the baseline; nothing bursts above.
  2. Even though the nightly refresh spikes to 1500 slots, that spike is short — 30 minutes at 1500 slots = 750 slot-hours. Averaged over the 12 hours the BI dashboard is running, the effective load is 200 * 12 + 300 * 3 + 1500 * 0.5 = 2400 + 900 + 750 = 4050 slot-hours. Spread across 24 hours, that's 169 slots average — below the 200-slot baseline. The autoscaler doesn't need to burst on average.
  3. But BigQuery bills per-second slot use, not per-day average. The 30-minute peak at 1500 slots does require the autoscaler to spin up 1300 extra slots for 30 minutes. Those 1300 × 0.5 = 650 slot-hours burst at the on-demand rate ($0.06 × 650 = $39). Add to the baseline cost. Actual monthly ≈ $6,912 + $39 = $6,951.
  4. Compare to no-commit autoscaler: 121,500 × $0.06 = $7,290. The commit saves $339/month or 4.6%. Modest, but the commit also gives budget predictability.
  5. The 3-year commit would save 40% off the baseline instead of 20%: baseline cost = 200 × 720 × 0.036 = $5,184, total ≈ $5,223. If the workload is genuinely stable for 3 years, that's an extra $1,700/month savings. If not, the lock-in is expensive.

Output.

Config Baseline slot-hrs Baseline cost Burst slot-hrs Burst cost Total
No commit 0 0 121,500 $7,290 $7,290
200 slots × 1-yr 144,000 $6,912 ~650 $39 $6,951
200 slots × 3-yr 144,000 $5,184 ~650 $39 $5,223
500 slots × 1-yr 360,000 $17,280 0 0 $17,280

Rule of thumb. Size the commit to the p50 daily average slots used, not to the peak. The autoscaler covers the peak at the on-demand rate; the commit covers the steady state at the discounted rate. Peak commits are burnt money.

Worked example — Enterprise Plus for CMEK compliance

Detailed explanation. A regulated deployment (finance, healthcare, government) requires CMEK — customer-managed encryption keys backed by Cloud KMS. Only Enterprise Plus supports CMEK for BigQuery data. The team must run a workload on Enterprise Plus even though Enterprise would have been enough on features.

  • Workload. Same nightly ELT as before — 500-slot peak, 4-hour window, no compliance features beyond CMEK required.
  • Enterprise cost. $3,600/month on autoscaler.
  • Enterprise Plus cost. ~$6,000/month on autoscaler (~$0.10/slot-hour × 60,000 slot-hours).
  • The premium. $2,400/month = 66% more, just for CMEK.

Question. Model the cost of running a compliance-required workload on Enterprise Plus vs Enterprise, and describe the architectural pattern that keeps only the compliance-required tables on Enterprise Plus while everything else stays on cheaper tiers.

Input.

Parameter Value
Nightly slot-hours 60,000
Enterprise slot rate $0.06
Enterprise Plus slot rate $0.10
CMEK requirement one dataset (pii_customers)

Code.

-- Cost comparison
WITH cfg AS (
  SELECT 60000 AS slot_hours,
         0.06  AS enterprise_rate,
         0.10  AS enterprise_plus_rate
)
SELECT slot_hours * enterprise_rate      AS enterprise_cost,
       slot_hours * enterprise_plus_rate AS enterprise_plus_cost,
       slot_hours * (enterprise_plus_rate - enterprise_rate) AS cmek_premium
FROM   cfg;
-- enterprise_cost      = 3600.00
-- enterprise_plus_cost = 6000.00
-- cmek_premium         = 2400.00 (67%)
Enter fullscreen mode Exit fullscreen mode 
# Architecture: two reservations — Enterprise Plus for pii; Enterprise for the rest.
# Reservation A — Enterprise Plus, CMEK-required workload
bq mk --location=us \
      --reservation \
      --edition=ENTERPRISE_PLUS \
      --slots=0 \
      --autoscale_max_slots=200 \
      pii_reservation

# Reservation B — Enterprise, everything else
bq mk --location=us \
      --reservation \
      --edition=ENTERPRISE \
      --slots=200 \
      --autoscale_max_slots=1000 \
      --plan=ANNUAL \
      analytics_reservation

# Assignments split by project — a dedicated project owns the CMEK tables
bq mk --location=us --reservation_assignment \
      --reservation_id=pii_reservation \
      --job_type=QUERY \
      --assignee_id=pii-prod --assignee_type=PROJECT

bq mk --location=us --reservation_assignment \
      --reservation_id=analytics_reservation \
      --job_type=QUERY \
      --assignee_id=analytics-prod --assignee_type=PROJECT
Enter fullscreen mode Exit fullscreen mode

Step-by-step explanation.

  1. The CMEK premium is 67% (Enterprise Plus $0.10 vs Enterprise $0.06). If the whole workload runs on Enterprise Plus, the whole bill pays the premium.
  2. The architectural fix: put only the CMEK-required tables in a dedicated project (pii-prod) with its own Enterprise Plus reservation. Everything else lives in analytics-prod on Enterprise. Queries against pii-prod land on the Enterprise Plus reservation; queries against analytics-prod land on Enterprise.
  3. The trick is that BigQuery reservations are assigned per-project (or per-folder / org), not per-table. Splitting the CMEK data into its own project is the standard pattern for tier-splitting.
  4. The nightly ELT that reads pii-prod.customers and writes to analytics-prod.orders_enriched must be split — the read job runs on the Enterprise Plus reservation (reading CMEK); the write job runs on the Enterprise reservation (writing non-CMEK). Cross-project joins land on whichever project runs the query.
  5. Sizing: 10% of the workload is CMEK-required (6,000 slot-hours on Enterprise Plus = $600). The remaining 54,000 slot-hours run on Enterprise = $3,240. Total = $3,840. Compared to running everything on Enterprise Plus ($6,000), the split saves $2,160/month — the entire CMEK premium.

Output.

Config Enterprise Plus SH Enterprise SH Cost
All on Enterprise Plus 60,000 0 $6,000
Split — pii on E+, rest on E 6,000 54,000 $3,840
Savings — — $2,160 / 36%

Rule of thumb. Never pay the Enterprise Plus premium on data that doesn't need CMEK. Split the compliance-required data into its own project + reservation; run the rest on Enterprise. The project split is the tier split.

Senior interview question on Editions

A senior interviewer might ask: "You have a mixed workload — a Looker BI dashboard, a nightly ELT, and a CMEK-required customer data pipeline. Design the reservations, name the tiers, size the commits, and justify each choice."

Solution Using three reservations with tier-appropriate commits 

# Reservation 1 — Enterprise, 200-slot 1-yr commit for the BI dashboard
bq mk --location=us --reservation \
      --edition=ENTERPRISE \
      --slots=200 \
      --autoscale_max_slots=1000 \
      --plan=ANNUAL \
      bi_dashboard_res

# Reservation 2 — Enterprise, pure autoscaler for the nightly ELT
bq mk --location=us --reservation \
      --edition=ENTERPRISE \
      --slots=0 \
      --autoscale_max_slots=1500 \
      elt_burst_res

# Reservation 3 — Enterprise Plus, small commit for CMEK data
bq mk --location=us --reservation \
      --edition=ENTERPRISE_PLUS \
      --slots=50 \
      --autoscale_max_slots=300 \
      --plan=ANNUAL \
      pii_cmek_res

# Assignments — one project per reservation
bq mk --location=us --reservation_assignment \
      --reservation_id=bi_dashboard_res  --job_type=QUERY --assignee_id=bi-prod       --assignee_type=PROJECT
bq mk --location=us --reservation_assignment \
      --reservation_id=elt_burst_res     --job_type=PIPELINE --assignee_id=elt-prod     --assignee_type=PROJECT
bq mk --location=us --reservation_assignment \
      --reservation_id=pii_cmek_res      --job_type=QUERY --assignee_id=pii-prod       --assignee_type=PROJECT
Enter fullscreen mode Exit fullscreen mode

Step-by-step trace.

Reservation Edition Baseline Max Commit Monthly Cost
bi_dashboard_res Enterprise 200 1000 1-yr $6,912 baseline + ~$400 burst = $7,312
elt_burst_res Enterprise 0 1500 none 60,000 slot-hrs × $0.06 = $3,600
pii_cmek_res Enterprise Plus 50 300 1-yr $50 × 720 × $0.08 = $2,880 + burst ~$100 = $2,980
Total mixed 250 2800 mixed $13,892

Compared to the "one reservation, Enterprise Plus, 500-slot 3-yr commit" naive approach at $28,800/month, this split saves $14,908/month — a 52% reduction. The BI dashboard gets the commit discount it earns; the ELT stays on pure autoscaler; the CMEK data gets Enterprise Plus but only for the 10% of the workload that needs it.

Output:

Metric Value
Reservations 3
Projects 3 (bi-prod, elt-prod, pii-prod)
Editions in use 2 (Enterprise, Enterprise Plus)
Commit length 1-yr on 2 of 3 reservations
Autoscaler max total 2,800 slots
Monthly cost $13,892
Naive alternative $28,800
Savings 52%

Why this works — concept by concept:

  • One reservation per workload — the reservation is the isolation unit. Splitting per-workload lets each get the tier and commit that fit its billing atom.
  • Commit on the steady, not the burst — the BI dashboard has a steady 200-slot baseline; the commit sits at 200. The ELT has 0 steady; the commit is 0 (pure autoscaler). Never commit to the peak.
  • Tier by feature, not by cost — the BI + ELT reservations are Enterprise (BI Engine + external tables); the CMEK reservation is Enterprise Plus (compliance requires it). The 67% Enterprise Plus premium is paid only on the 10% of the workload that needs it.
  • Assignments by job type — the ELT reservation is assigned job_type=PIPELINE; the BI is job_type=QUERY. This prevents interactive queries from starving the ELT (or vice versa) when both run on shared reservations. See the reservations section for the full assignment model.
  • Cost — three reservations = three CLI commands, one Terraform module. O(1) operational overhead. The saving is O(workload-mix-count) per month — always positive for mixed workloads.

SQL
Topic — sql
SQL reservation-sizing and slot problems

Practice →

Optimization Topic — optimization Optimization problems on Editions tier and commit choices

Practice →

4. Reservations + commitments

bigquery reservations split a slot pool across QUERY / PIPELINE / BACKGROUND jobs — idle slots share, commits discount, assignments isolate

The mental model in one line: bigquery reservations are named pools of slots assigned to projects, folders, or organisations, and split by job type (QUERY, PIPELINE, BACKGROUND) via assignments so that a slow interactive dashboard cannot starve a nightly ELT — combined with 1-year or 3-year commitments that discount the baseline, and idle-capacity sharing that lets a bursty reservation borrow unused slots from a quiet one. Every reservations interview question reduces to: how do you decompose the workload into reservations, which projects and job types get which slots, and how much do you commit for how long.

Reservations + commitments — slot pool + assignments. Iconographic diagram showing a large slot-pool card labelled 'reservation: 500 slots' filled with slot-chips, three assignment cards on the right (QUERY orange, PIPELINE green, BACKGROUND purple), and a commit-timeline bar at the bottom with 1-mo / 1-yr / 3-yr markers, on a light PipeCode card.

The four axes of the reservations model.

  • Slot commitment. 0-day (pure autoscaler), 1-year (~20% discount), or 3-year (~40% discount). The commit locks in slots at the discount for the term; you pay whether or not you use them.
  • Reservation. A named pool of slots. A reservation has an Edition (Standard / Enterprise / Enterprise Plus), a baseline (committed slot count), and an autoscaler max. Multiple reservations can share the same commitment.
  • Assignment. Which projects (or folders / orgs) route which job types to which reservation. Assignment types: QUERY (interactive analyst queries + ad-hoc), PIPELINE (scheduled + programmatic ELT), BACKGROUND (materialised-view refreshes + system tasks).
  • Idle sharing. A reservation with unused slots automatically shares them with other reservations that need bursting, within the same admin_project. Idle sharing is bounded per reservation (ignore_idle_slots = TRUE disables sharing for that reservation).

Slot commitments — what you're buying.

  • A commitment is a purchase of slot-hours per second at a discounted rate, for a fixed term. A 500-slot 1-year commit is "500 slot-hours per hour for 8,760 hours, at 20% off the sticker rate."
  • Commitments are not tied to a specific reservation. A single commitment can back multiple reservations totalling the committed slot count. This is the "capacity pool" pattern for multi-team deployments.
  • You pay the committed rate even if you don't use it. The autoscaler can burst above the commit at the on-demand slot rate — but only if the reservation's autoscale_max_slots allows.
  • Termination. 1-year and 3-year commits cannot be cancelled early. Buy conservatively; the sizing formula uses p50 of daily peak, not p95.

Reservations — the pool.

  • A reservation is a slot bucket. Baseline (committed slots) + autoscale ceiling (max slots the autoscaler can add).
  • A reservation has an Edition. All jobs running on the reservation get the Edition's feature set.
  • A reservation has an ignore_idle_slots flag. When TRUE, the reservation never borrows from other reservations (hard cap). When FALSE (default), the reservation can borrow idle slots from other reservations in the same admin project.
  • A reservation belongs to an admin project. Cross-project sharing requires the same admin project.

Assignments — the routing.

  • QUERY assignment. All interactive queries (bq CLI, console, Looker, ad-hoc SQL) from the assigned project route to this reservation.
  • PIPELINE assignment. All scheduled queries, Dataflow jobs, and programmatic INSERT/UPDATE/DELETE from the assigned project route here.
  • BACKGROUND assignment. Materialised-view refreshes, table copy jobs, and other system-triggered work route here.
  • A single project can have three separate assignments — one per job type — routing to three different reservations. This is the "isolate the ELT from the dashboard" pattern.

Idle capacity sharing.

  • Default. ON. Reservations with unused slots automatically donate them to other reservations that are queued.
  • Priority. The reservation's own committed slots always win — an idle reservation lending slots to another can reclaim them the instant it needs them.
  • Cross-org sharing. Not allowed. Sharing is bounded by the admin project.
  • Disable. ignore_idle_slots = TRUE on a reservation. Useful when you want a hard cap for cost isolation (e.g. a dev reservation that shouldn't be able to burst).

Common interview probes on reservations.

  • "What's the difference between a commitment and a reservation?" — commitment is the purchase; reservation is the pool. One commitment can back many reservations.
  • "How do you isolate an ELT from an interactive workload?" — separate reservations + PIPELINE / QUERY assignments.
  • "Explain idle capacity sharing." — reservations donate unused slots to queued peers, reclaim on demand.
  • "When would you set ignore_idle_slots = TRUE?" — dev reservation with hard cost cap.

Worked example — 500-slot Enterprise commit sized for daily ELT peak

Detailed explanation. A nightly ELT peaks at 500 slots for 4 hours. Rest of the day: 200 slots average for BI. Design a 500-slot Enterprise commit that covers the ELT peak + BI baseline efficiently via idle capacity sharing, with a fallback autoscaler for excess.

  • ELT window. 4 hours × 500 slots = 2,000 slot-hours per night.
  • BI window. 20 hours × 200 slots = 4,000 slot-hours per day (assuming 24-hour dashboard).
  • Total slot-hours per day. 6,000. Commit provides 500 × 24 = 12,000 slot-hours per day (buffer = 100%).
  • Idle sharing. During the 20 non-ELT hours, the ELT reservation is idle at 500 slots; 300 of those donate to BI (which needs 200 baseline). During the 4 ELT hours, BI's 300 slots (300 = 500 - 200 baseline) donate back.

Question. Design the reservation topology + commit for this workload. Show the CLI commands and the expected cost.

Input.

Component Slots Hours
Nightly ELT (peak) 500 4
BI dashboard (steady) 200 24
Total commit needed 500 (peak ELT covers BI via sharing) —
Enterprise slot-hour rate $0.06
1-year discount 20%

Code.

# Step 1 — buy the 500-slot 1-year Enterprise commit
bq mk --location=us \
      --capacity_commitment \
      --edition=ENTERPRISE \
      --slots=500 \
      --plan=ANNUAL

# Step 2 — create two reservations backed by the same commitment
bq mk --location=us \
      --reservation \
      --edition=ENTERPRISE \
      --slots=200 \
      --autoscale_max_slots=800 \
      bi_reservation

bq mk --location=us \
      --reservation \
      --edition=ENTERPRISE \
      --slots=300 \
      --autoscale_max_slots=1500 \
      elt_reservation

# Step 3 — assign projects and job types
bq mk --location=us --reservation_assignment \
      --reservation_id=bi_reservation  --job_type=QUERY \
      --assignee_id=bi-prod            --assignee_type=PROJECT

bq mk --location=us --reservation_assignment \
      --reservation_id=elt_reservation --job_type=PIPELINE \
      --assignee_id=elt-prod           --assignee_type=PROJECT

# Both reservations default to ignore_idle_slots = FALSE → idle sharing on
Enter fullscreen mode Exit fullscreen mode 
-- Cost calculation
WITH cfg AS (
  SELECT 500   AS committed_slots,
         0.048 AS committed_rate,        -- 20% off $0.06
         0.06  AS burst_rate,
         2000  AS elt_daily_slot_hours,
         4800  AS bi_daily_slot_hours    -- 200 slots × 24 hours
)
SELECT committed_slots * 24 * 30 * committed_rate AS baseline_cost_monthly,
       -- workload = 2000 + 4800 = 6800/day = 204,000/month
       -- commit gives 500 × 24 × 30 = 360,000 slot-hours/month → workload fits inside
       GREATEST(30 * (elt_daily_slot_hours + bi_daily_slot_hours)
                - committed_slots * 24 * 30, 0)
              * burst_rate                          AS burst_cost_monthly
FROM cfg;
-- baseline_cost_monthly = 17,280.00
-- burst_cost_monthly    = 0  (workload fits inside baseline)
Enter fullscreen mode Exit fullscreen mode

Step-by-step explanation.

  1. Buy one 500-slot 1-year Enterprise commit — a single purchase covering both the ELT peak and the BI steady state.
  2. Split into two reservations from the same commit: bi_reservation (baseline 200) + elt_reservation (baseline 300). Baseline sum = 500 = commit. Autoscaler max on each allows bursting above baseline via the shared idle-slot pool.
  3. Assign the projects: bi-prod → bi_reservation on QUERY jobs; elt-prod → elt_reservation on PIPELINE jobs. This isolates the interactive workload from the pipeline — a slow query on the dashboard cannot delay the ELT.
  4. Idle sharing does the magic. During BI hours (200 slots used of 800 max), the BI reservation borrows 300 slots from the idle ELT reservation → BI can serve 500 slots without paying burst rates. During ELT hours (500 slots used of 1500 max), the ELT borrows 300 slots from the idle BI reservation → ELT can peak at 500 without exceeding baseline.
  5. The workload consumes 204,000 slot-hours per month; the commit provides 360,000. Everything fits inside the baseline; no burst cost. Bill = $17,280/month flat.

Output.

Layer Value
Committed slots 500
Reservations (from one commit) 2 (bi_reservation, elt_reservation)
Assignments 2 (bi-prod QUERY, elt-prod PIPELINE)
Monthly cost $17,280
Alternative — 2 separate commits (200 + 500) $24,192
Savings from sharing $6,912 / 29%

Rule of thumb. For multi-workload deployments with complementary duty cycles, buy one commit sized to the peak of the sum (not the sum of the peaks) and split it across reservations. Idle sharing recovers ~30% of the cost of naive per-workload commits.

Worked example — QUERY / PIPELINE / BACKGROUND assignment split

Detailed explanation. The single project running interactive queries + a scheduled ELT + materialised-view refreshes. Without assignments, all three job types compete for the same slot pool. A slow dashboard query blocks the ELT; the ELT delays the mv-refresh; the mv-refresh throttles interactive queries. Split by assignment type.

  • Interactive queries. Analyst + Looker + bq CLI. QUERY assignment.
  • Scheduled ELT. Nightly jobs from Airflow / Dataform / scheduled queries. PIPELINE assignment.
  • Materialised-view refreshes. BackGround jobs triggered by the engine. BACKGROUND assignment.

Question. For an analytics-prod project with all three job types, design three reservations + three assignments, size each based on the workload, and show the cost.

Input.

Job type Slot-hours/day Peak concurrent slots
QUERY (interactive) 3,000 500
PIPELINE (ELT) 2,000 800
BACKGROUND (mv-refresh) 500 200

Code.

# One 500-slot commit backing three reservations
bq mk --location=us --capacity_commitment \
      --edition=ENTERPRISE --slots=500 --plan=ANNUAL

# Three reservations from the commit
bq mk --location=us --reservation \
      --edition=ENTERPRISE --slots=200 --autoscale_max_slots=800 \
      interactive_res

bq mk --location=us --reservation \
      --edition=ENTERPRISE --slots=200 --autoscale_max_slots=1200 \
      pipeline_res

bq mk --location=us --reservation \
      --edition=ENTERPRISE --slots=100 --autoscale_max_slots=400 \
      background_res

# Three assignments — same project, three job types
bq mk --location=us --reservation_assignment \
      --reservation_id=interactive_res  --job_type=QUERY \
      --assignee_id=analytics-prod --assignee_type=PROJECT

bq mk --location=us --reservation_assignment \
      --reservation_id=pipeline_res     --job_type=PIPELINE \
      --assignee_id=analytics-prod --assignee_type=PROJECT

bq mk --location=us --reservation_assignment \
      --reservation_id=background_res   --job_type=BACKGROUND \
      --assignee_id=analytics-prod --assignee_type=PROJECT
Enter fullscreen mode Exit fullscreen mode 
-- Verify the assignment topology
SELECT
    reservation_name,
    job_type,
    assignee_type,
    assignee_id
FROM   `region-us`.INFORMATION_SCHEMA.ASSIGNMENTS_BY_PROJECT
WHERE  assignee_id = 'analytics-prod';
-- reservation_name | job_type    | assignee_type | assignee_id
-- interactive_res  | QUERY       | PROJECT       | analytics-prod
-- pipeline_res     | PIPELINE    | PROJECT       | analytics-prod
-- background_res   | BACKGROUND  | PROJECT       | analytics-prod
Enter fullscreen mode Exit fullscreen mode

Step-by-step explanation.

  1. One 500-slot commit backs three reservations. Baseline sum = 200 + 200 + 100 = 500. The commit is fully allocated.
  2. QUERY workloads (interactive analyst queries, Looker) route to interactive_res (baseline 200, autoscale max 800). Peak of 500 is covered by 200 baseline + 300 borrowed from idle sharing during quiet ELT hours.
  3. PIPELINE workloads (ELT) route to pipeline_res (baseline 200, autoscale max 1200). Peak of 800 is covered by 200 baseline + 600 borrowed from idle QUERY reservation during off-hours.
  4. BACKGROUND workloads (materialised-view refreshes) route to background_res (baseline 100, autoscale max 400). Peak of 200 is covered by baseline + a small autoscaler burst.
  5. The isolation guarantees: an out-of-control interactive query cannot delay the ELT because they hit different reservations; the ELT cannot starve the mv-refresh; the mv-refresh cannot slow analyst queries. Each job type has an SLA that the other job types cannot break.

Output.

Reservation Baseline Max Workload peak Coverage
interactive_res 200 800 500 200 own + 300 borrowed
pipeline_res 200 1200 800 200 own + 600 borrowed
background_res 100 400 200 100 own + 100 borrowed

Rule of thumb. Split a busy project into three reservations by job type. Interactive workloads get their own reservation so their SLA is not held hostage to ELT lag; ELTs get their own so background jobs don't delay a critical nightly run.

Worked example — commit break-even calculator

Detailed explanation. The senior interview question: "At what utilisation does a 1-year commit break even against on-demand?" Answer requires the discount math and the commit's obligation to pay whether used or not.

  • On-demand slot rate. $0.06/hour (Enterprise).
  • 1-year committed rate. $0.048/hour (20% off).
  • Break-even utilisation. Committed rate / on-demand rate = 0.048 / 0.06 = 0.80. Above 80% utilisation, commit wins; below, on-demand wins.
  • 3-year committed rate. $0.036/hour (40% off). Break-even = 0.036 / 0.06 = 0.60.

Question. Derive the break-even utilisation for 1-year and 3-year commits, then compute the annual cost for a 500-slot workload at 40%, 60%, 80%, and 100% utilisation under each model.

Input.

Parameter Value
On-demand slot-hour rate $0.06
1-year committed rate $0.048
3-year committed rate $0.036
Slots 500
Hours per year 8,760

Code.

-- Break-even utilisation
WITH rates AS (
  SELECT 0.06 AS on_demand,
         0.048 AS one_yr,
         0.036 AS three_yr
)
SELECT one_yr / on_demand AS one_yr_breakeven,
       three_yr / on_demand AS three_yr_breakeven
FROM   rates;
-- one_yr_breakeven   = 0.80
-- three_yr_breakeven = 0.60
Enter fullscreen mode Exit fullscreen mode 
-- Annual cost at 40%, 60%, 80%, 100% utilisation for a 500-slot workload
WITH util AS (
  SELECT 0.4 AS u UNION ALL
  SELECT 0.6 UNION ALL
  SELECT 0.8 UNION ALL
  SELECT 1.0
),
constants AS (
  SELECT 500 AS slots, 8760 AS hours,
         0.06 AS on_demand, 0.048 AS one_yr, 0.036 AS three_yr
)
SELECT
    u,
    ROUND(slots * hours * u * on_demand, 0)           AS on_demand_yr,
    ROUND(slots * hours * one_yr, 0)                  AS one_yr_yr,      -- fixed
    ROUND(slots * hours * three_yr, 0)                AS three_yr_yr,    -- fixed
    CASE
      WHEN slots * hours * u * on_demand < slots * hours * one_yr
        THEN 'on-demand'
      ELSE 'commit'
    END AS one_yr_winner,
    CASE
      WHEN slots * hours * u * on_demand < slots * hours * three_yr
        THEN 'on-demand'
      ELSE 'commit'
    END AS three_yr_winner
FROM   util, constants
ORDER  BY u;
Enter fullscreen mode Exit fullscreen mode

Step-by-step explanation.

  1. The break-even utilisation for a 1-year commit is 80% — below 80%, on-demand wins because the commit's fixed cost exceeds the used slot-hours × on-demand rate. Above 80%, commit wins.
  2. The 3-year commit break-even is 60% — much lower because the 40% discount is deeper. A workload with 60%+ steady utilisation for 3 years is a clear commit win.
  3. At 40% utilisation: on-demand costs $105,120/year; 1-year commit costs $210,240/year (2x more); 3-year costs $157,680 (1.5x more). Both commits lose.
  4. At 60% utilisation: on-demand $157,680; 1-year $210,240 (33% more); 3-year $157,680 (equal). 3-year break-even.
  5. At 80% utilisation: on-demand $210,240; 1-year $210,240 (equal). 1-year break-even.
  6. At 100% utilisation: on-demand $262,800; 1-year $210,240 (20% cheaper); 3-year $157,680 (40% cheaper). Commit wins big at high utilisation.

Output.

Utilisation On-demand/yr 1-yr commit/yr 3-yr commit/yr 1-yr winner 3-yr winner
40% $105,120 $210,240 $157,680 on-demand on-demand
60% $157,680 $210,240 $157,680 on-demand tie
80% $210,240 $210,240 $157,680 tie commit
100% $262,800 $210,240 $157,680 commit commit

Rule of thumb. A 1-year commit needs sustained 80%+ utilisation to beat on-demand. A 3-year commit needs 60%+. Below those thresholds, pure autoscaler beats any commit — and the autoscaler is more flexible if the workload shifts.

Senior interview question on reservations

A senior interviewer might ask: "Design the full reservations + commitments plan for a mixed-workload analytics org — 3 teams, 5 projects, a mix of BI, ELT, and ad-hoc. Show the commits, reservations, assignments, and expected cost."

Solution Using shared commits + per-team reservations + job-type assignments 

# Buy one 1000-slot 1-year Enterprise commit for the whole org
bq mk --location=us --capacity_commitment \
      --edition=ENTERPRISE \
      --slots=1000 \
      --plan=ANNUAL

# Buy a 100-slot 1-year Enterprise Plus commit for CMEK data
bq mk --location=us --capacity_commitment \
      --edition=ENTERPRISE_PLUS \
      --slots=100 \
      --plan=ANNUAL

# Team A — BI (200 baseline; interactive queries + scheduled dashboard refreshes)
bq mk --location=us --reservation --edition=ENTERPRISE \
      --slots=200 --autoscale_max_slots=1000 team_a_bi

bq mk --location=us --reservation_assignment \
      --reservation_id=team_a_bi --job_type=QUERY \
      --assignee_id=team-a-bi-prod --assignee_type=PROJECT

# Team B — ELT (300 baseline; nightly pipelines)
bq mk --location=us --reservation --edition=ENTERPRISE \
      --slots=300 --autoscale_max_slots=1500 team_b_elt

bq mk --location=us --reservation_assignment \
      --reservation_id=team_b_elt --job_type=PIPELINE \
      --assignee_id=team-b-elt-prod --assignee_type=PROJECT

# Team C — ad-hoc + ML (400 baseline)
bq mk --location=us --reservation --edition=ENTERPRISE \
      --slots=400 --autoscale_max_slots=1500 team_c_analytics

bq mk --location=us --reservation_assignment \
      --reservation_id=team_c_analytics --job_type=QUERY \
      --assignee_id=team-c-ml-prod --assignee_type=PROJECT

# Shared background reservation for materialised views + system tasks
bq mk --location=us --reservation --edition=ENTERPRISE \
      --slots=100 --autoscale_max_slots=400 shared_background

bq mk --location=us --reservation_assignment \
      --reservation_id=shared_background --job_type=BACKGROUND \
      --assignee_id=analytics-org --assignee_type=ORGANIZATION

# CMEK reservation for PII data
bq mk --location=us --reservation --edition=ENTERPRISE_PLUS \
      --slots=100 --autoscale_max_slots=300 pii_cmek

bq mk --location=us --reservation_assignment \
      --reservation_id=pii_cmek --job_type=QUERY \
      --assignee_id=pii-prod --assignee_type=PROJECT
Enter fullscreen mode Exit fullscreen mode

Step-by-step trace.

Reservation Team Edition Baseline Max Assignment Monthly cost
team_a_bi A (BI) Enterprise 200 1000 QUERY / team-a-bi-prod included in commit
team_b_elt B (ELT) Enterprise 300 1500 PIPELINE / team-b-elt-prod included in commit
team_c_analytics C (ad-hoc+ML) Enterprise 400 1500 QUERY / team-c-ml-prod included in commit
shared_background shared Enterprise 100 400 BACKGROUND / analytics-org included in commit
pii_cmek pii Enterprise Plus 100 300 QUERY / pii-prod separate E+ commit

Total committed slots = 1000 Enterprise + 100 Enterprise Plus. Baseline sums exactly to the commits. All reservations can burst above baseline via autoscaler; idle sharing recovers unused slots for whichever team is bursting.

Monthly baseline cost:

  • Enterprise commit: 1000 × 720 × $0.048 = $34,560
  • Enterprise Plus commit: 100 × 720 × $0.08 = $5,760
  • Total baseline: $40,320/month
  • Burst (autoscaler above baseline): historical average ~$3,000/month
  • Total: ~$43,000/month

Compare to naive "10 separate commits totalling 3,000 slots to avoid ever bursting": ~$104,000/month. Shared commits + idle sharing save 59%.

Output:

Metric Value
Commits 2 (1000 Enterprise + 100 Enterprise Plus)
Reservations 5
Assignments 5 (per team + shared background)
Job types split QUERY, PIPELINE, BACKGROUND across 3 reservations
Monthly cost ~$43,000
Naive alternative ~$104,000
Savings 59%

Why this works — concept by concept:

  • One commit backs many reservations — the commit is the purchase; reservations are the pools. Sharing one commit across teams reduces the total commit needed by ~40% via idle sharing.
  • Job-type assignments isolate workloads — QUERY / PIPELINE / BACKGROUND assignments guarantee interactive queries don't starve pipelines and vice versa. Each team's SLA is preserved.
  • Autoscaler max as the safety valve — every reservation has autoscale_max_slots sized to the worst-plausible peak. Burst above baseline pays on-demand but doesn't queue.
  • Enterprise Plus only for CMEK — the 67% Enterprise Plus premium is paid only on the 10% of the workload that requires it. The rest lives on Enterprise.
  • Idle sharing recovers unused capacity — a reservation with unused slots donates them; when it needs them back, it reclaims them instantly. This is why aggregate commits are far cheaper than per-team commits.
  • Cost — five bq CLI commands per team; O(1) per reservation. The saving is O(cross-team duty cycle offset) per month, which grows with the number of teams.

SQL
Topic — sql
SQL commit-sizing and assignment problems

Practice →

ETL Topic — etl ETL problems on reservation topology and idle sharing

Practice →

5. Mixed workloads + decision matrix

Hybrid — on-demand for dev + Editions for prod + autoscaler tuning is the senior default in 2026

The mental model in one line: the real answer to "which BigQuery pricing model wins" is hybrid: on-demand for dev, ad-hoc, and truly bursty workloads; Editions with a small commit + generous autoscaler max for prod steady-state; and the autoscaler baseline / max dialled per workload so that the on-demand slot rate covers infrequent bursts without paying commit tax on unused hours. Every senior BigQuery deployment in 2026 uses a mix; picking one model for the whole account is a junior signal.

Mixed workloads + decision matrix. Iconographic diagram showing a 3x4 decision matrix on the left (rows: ad-hoc, daily ELT, BI, ML training; columns: on-demand, Standard, Enterprise, Enterprise+) with green/orange/red dots, and a hybrid pattern card on the right (on-demand dev + Editions prod) with nested project cards, on a light PipeCode card.

The four axes of the mixed model.

  • Environment split. Dev / staging / prod. Dev is on-demand (unpredictable, low-volume, cost-capped per user). Staging mirrors prod but usually on-demand or a tiny Standard reservation. Prod runs on Editions with commits.
  • Workload split. Interactive BI (Enterprise reservation, small commit). Nightly ELT (Enterprise, pure autoscaler or joined to BI reservation via idle sharing). Ad-hoc analyst queries (on-demand with per-user quotas). ML training (Enterprise or Enterprise Plus if CMEK).
  • Autoscaler tuning. Baseline covers steady-state at the commit discount; max covers bursts at the on-demand slot rate.
  • Cost monitoring. INFORMATION_SCHEMA.JOBS_BY_PROJECT + JOBS_TIMELINE_BY_PROJECT + RESERVATION_TIMELINE_BY_ADMIN_PROJECT — three views for cost attribution, utilisation shape, and reservation efficiency.

The decision matrix.

  • Ad-hoc queries. On-demand (green) — bursty, unpredictable, low volume. Standard (yellow) — only if cost predictability required. Enterprise (red) — waste of features.
  • Daily ELT. On-demand (yellow) — if scan is small. Enterprise (green) — if scan is medium and BI Engine is on. Enterprise Plus (red) — waste of CMEK.
  • BI dashboard. On-demand (red) — repeat scans blow up. Enterprise (green) — BI Engine cache. Enterprise Plus (yellow) — only if CMEK.
  • ML training. On-demand (red) — hits burst cap. Standard (yellow) — no BQ ML. Enterprise (green) — full BQ ML. Enterprise Plus (green) — if CMEK required on training data.
  • CDC streaming. On-demand (red) — streaming inserts on on-demand are expensive. Standard (green) — vanilla streaming. Enterprise (yellow) — over-featured. Enterprise Plus (red) — waste.

Autoscaler tuning — the two-knob strategy.

  • Baseline. Set to p50 of daily peak slots. This is what you commit to. Covers the typical day at the discounted rate.
  • Max. Set to 2× to 3× baseline. This is the autoscaler ceiling. Covers the worst-plausible spike; anything beyond fails the query (better than blowing the bill).
  • Why not baseline = peak? Because the peak is rare. Paying 24/7 for a 4-hour daily peak is 6x more expensive than paying baseline + autoscaler burst.
  • Why not max = infinity? Because a broken query (10-way join with no filter) can grab 5,000 slots and cost $500 in 15 minutes. Cap it.

Cost monitoring queries.

  • JOBS_BY_PROJECT. Per-job cost attribution. Top-N by slot-ms or bytes-billed. Trend over 30 days.
  • JOBS_TIMELINE_BY_PROJECT. Per-second slot use. Shape of the day. p50 / p95 / peak.
  • RESERVATION_TIMELINE_BY_ADMIN_PROJECT. Per-reservation slot use vs baseline. Detect chronic under-utilisation (reduce commit) or chronic burst (raise commit).
  • CAPACITY_COMMITMENT_CHANGES_BY_ADMIN_PROJECT. Commit history — verify renewals and terminations.

Common interview probes on mixed workloads.

  • "How do you split dev and prod pricing?" — dev on-demand with per-user caps; prod on commit.
  • "What's your autoscaler max set to?" — 2–3x baseline; cap the runaway.
  • "How do you monitor reservation efficiency?" — RESERVATION_TIMELINE compared to baseline; look for chronic underused hours (commit too high).
  • "How do you catch bad queries before they cost?" — dry-run + per-query maximum_bytes_billed + per-user quotas.

Worked example — dev on-demand + prod Enterprise commit

Detailed explanation. A team runs three environments: dev (10 analysts running ad-hoc), staging (mirrors prod but low volume), prod (BI + nightly ELT). Design the pricing model split.

  • Dev. 10 analysts × 20 queries/day × ~5 GB/query = 1 TB/day = 30 TB/month. On-demand at $6.25/TB = $187.50/month. Per-analyst quota of 100 GB/day caps blast radius.
  • Staging. Low volume — a nightly ELT run at 10% of prod. On-demand covers it easily; $50/month.
  • Prod. BI + ELT sharing a 400-slot Enterprise 1-yr commit. $13,824 baseline + minor burst.

Question. Design the pricing model for each environment, then walk through the guard rails that prevent dev from becoming a cost surprise.

Input.

Environment Workload Rough volume
Dev ad-hoc analyst 30 TB/month scanned
Staging pipeline test 3 TB/month scanned
Prod BI + ELT 204,000 slot-hours/month

Code.

# Dev — on-demand + per-user quota
bq update --project_id=analytics-dev --maximum_bytes_billed=107374182400
# 100 GB per query cap; blast radius contained.

# Staging — on-demand, small maximum_bytes_billed
bq update --project_id=analytics-staging --maximum_bytes_billed=536870912000
# 500 GB per query cap.

# Prod — Enterprise commit
bq mk --location=us --capacity_commitment \
      --edition=ENTERPRISE --slots=400 --plan=ANNUAL

bq mk --location=us --reservation --edition=ENTERPRISE \
      --slots=400 --autoscale_max_slots=1500 prod_reservation

bq mk --location=us --reservation_assignment \
      --reservation_id=prod_reservation --job_type=QUERY \
      --assignee_id=analytics-prod --assignee_type=PROJECT
Enter fullscreen mode Exit fullscreen mode 
-- Cost monitor — daily rollup by environment
SELECT
    project_id,
    DATE(creation_time) AS day,
    COUNT(*)            AS jobs,
    ROUND(SUM(total_bytes_billed) / POW(1024, 4), 2) AS tb_billed,
    ROUND(SUM(total_bytes_billed) / POW(1024, 4) * 6.25, 2) AS est_on_demand_usd,
    ROUND(SUM(total_slot_ms) / 3600000.0, 1) AS slot_hours
FROM   `region-us`.INFORMATION_SCHEMA.JOBS_BY_PROJECT
WHERE  creation_time >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 7 DAY)
GROUP  BY project_id, day
ORDER  BY day DESC, tb_billed DESC;
Enter fullscreen mode Exit fullscreen mode

Step-by-step explanation.

  1. Dev is on-demand with a 100 GB per-query cap. A misconfigured SELECT * scans at most 100 GB and fails. Total dev cost is bounded by (10 analysts × 20 queries/day × 100 GB cap × $6.25/TB × 30) / 1024 = ~$375/month worst case, but averages closer to $190/month at realistic volumes.
  2. Staging on-demand with a 500 GB cap — headroom for the largest pipeline test, but a runaway query on 1 TB fails at plan time.
  3. Prod on Enterprise with a 400-slot 1-yr commit at $0.048/slot-hour = $13,824/month baseline. Autoscaler max 1500 covers the nightly ELT peak of 1000 without runaway cost.
  4. Total org cost: $190 (dev) + $50 (staging) + $13,824 (prod) = $14,064/month. Compare to a naive "prod-sized commit across all environments" of $20,000+ — the split saves ~30%.
  5. The monitoring query rolls up daily cost per project. Anomalies (dev spending 5x baseline in a day) surface immediately; alerts can auto-page.

Output.

Env Model Guard rail Monthly cost
Dev on-demand max_bytes_billed = 100 GB ~$190
Staging on-demand max_bytes_billed = 500 GB ~$50
Prod Enterprise 400-slot 1-yr autoscale_max = 1500 $13,824
Total mixed — $14,064

Rule of thumb. Dev and staging almost always belong on on-demand with per-query bytes caps. Committing prod-sized slots to a dev environment where 90% of the day is idle is one of the classic waste patterns senior engineers audit out.

Worked example — autoscaler max tuning to prevent runaway cost

Detailed explanation. The runaway-query trap. A misconfigured 10-way join with no filter on a 5 TB fact grabs every available slot. If autoscale_max_slots = 5000, the query burns 5000 × 15 min × $0.06/hr = $75 in one query. Cap the max at 2-3x baseline so a runaway costs less.

  • Baseline. 400 slots.
  • Naive max. 5000 (whatever the region supports).
  • Sane max. 1200 (3x baseline).
  • Bad query on naive. 5000 slots × 15 min = 1250 slot-hours × $0.06 = $75.
  • Bad query on sane. 1200 slots × 15 min = 300 slot-hours × $0.06 = $18. 4x cheaper.

Question. Design the autoscaler max for a reservation with 400-slot baseline serving a BI + ELT mix, then set a job_timeout_ms at the project level as a second line of defence.

Input.

Parameter Value
Baseline slots 400
BI peak (steady) 400 slots
ELT peak (nightly, 4 hours) 1000 slots
Worst plausible legit query 800 slots for 15 minutes
Runaway query any query > 1200 slots or > 15 min

Code.

# Set autoscale_max = 3x baseline
bq update --location=us --reservation \
          --autoscale_max_slots=1200 \
          prod_reservation

# Set a job timeout at the project level (30 min default)
bq update --project_id=analytics-prod --default_query_job_timeout_ms=1800000
Enter fullscreen mode Exit fullscreen mode 
-- Per-query safety cap
SET @@job_config.job_timeout_ms = 1800000;      -- 30 min
SET @@job_config.maximum_bytes_billed = 1099511627776;  -- 1 TB

-- Verify autoscaler settings
SELECT reservation_name,
       slot_capacity,
       autoscale.max_slots
FROM   `region-us`.INFORMATION_SCHEMA.RESERVATIONS
WHERE  reservation_name = 'prod_reservation';
-- reservation_name | slot_capacity | max_slots
-- prod_reservation | 400           | 1200
Enter fullscreen mode Exit fullscreen mode

Step-by-step explanation.

  1. Autoscaler max at 3x baseline (1200) covers the nightly ELT peak (1000) with headroom. A well-formed query cannot be denied slots.
  2. A runaway query grabs at most 1200 slots. If it also runs 15 min, it costs 300 slot-hours × $0.06 = $18. Bad but bounded.
  3. default_query_job_timeout_ms = 1800000 (30 min) kills any query that runs past 30 minutes. Combined with max slots, worst-case runaway = 1200 × 0.5 = 600 slot-hours × $0.06 = $36. Still bounded.
  4. maximum_bytes_billed = 1 TB at the session level catches queries whose plan already exceeds 1 TB — they fail at plan time, before consuming any slots.
  5. The layered defence: autoscaler max caps slot draw; job_timeout_ms caps duration; maximum_bytes_billed caps scanned bytes. A bad query has to pass all three checks to be expensive. In practice, no single misfire costs more than $40.

Output.

Config Max slots Timeout Worst-case bad-query cost
No caps 5000 none $75 / 15 min or $300 / 1 hr
Autoscaler max 1200 1200 none $18 / 15 min
Max 1200 + timeout 30 min 1200 30 min $36 / 30 min
Max 1200 + timeout 30 min + bytes cap 1 TB 1200 30 min $6 (bytes cap fails plan first)

Rule of thumb. Autoscaler max is not "the ceiling the region supports" — it's "the ceiling I can afford one bad query to hit." Set it to 2-3x baseline and pair with a job_timeout and bytes cap. Runaway queries become $18 accidents, not $500 disasters.

Worked example — INFORMATION_SCHEMA cost audit and re-sizing

Detailed explanation. Monthly re-sizing loop: pull RESERVATION_TIMELINE, see actual slot utilisation vs baseline, adjust commit size or reservation baseline. Under-utilisation → shrink commit; sustained burst → grow commit.

  • Under-utilised reservation. Baseline 400, average use 200. Overpaying for 200 slots × 24 × 30 = 144,000 slot-hours × $0.048 = $6,912/month wasted.
  • Over-utilised reservation. Baseline 400, average burst 800. Underbought by 400 slots × avg use × on-demand rate.
  • Right-sized. Baseline 400, actual p50 400, p95 800 (autoscaler covers p95).

Question. Write the SQL that quantifies whether each reservation is under-, over-, or right-sized, then produce a re-sizing recommendation.

Input.

Table Purpose
region-us.INFORMATION_SCHEMA.RESERVATION_TIMELINE_BY_ADMIN_PROJECT Per-minute reservation utilisation
region-us.INFORMATION_SCHEMA.RESERVATIONS_BY_ADMIN_PROJECT Current baseline + max

Code.

-- Reservation utilisation over 30 days
WITH utilisation AS (
  SELECT
      reservation_name,
      DATE(period_start) AS day,
      AVG(slots_assigned) AS avg_slots_provisioned,
      AVG(slots_used)     AS avg_slots_used,
      MAX(slots_used)     AS peak_slots_used
  FROM   `region-us`.INFORMATION_SCHEMA.RESERVATION_TIMELINE_BY_ADMIN_PROJECT
  WHERE  period_start >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 30 DAY)
  GROUP  BY reservation_name, day
),
summary AS (
  SELECT
      reservation_name,
      ROUND(AVG(avg_slots_provisioned), 0)                     AS avg_provisioned,
      ROUND(AVG(avg_slots_used), 0)                            AS avg_used,
      ROUND(APPROX_QUANTILES(peak_slots_used, 100)[OFFSET(50)], 0) AS peak_p50,
      ROUND(APPROX_QUANTILES(peak_slots_used, 100)[OFFSET(90)], 0) AS peak_p90,
      ROUND(AVG(avg_slots_used) / NULLIF(AVG(avg_slots_provisioned), 0), 2) AS utilisation
  FROM   utilisation
  GROUP  BY reservation_name
)
SELECT
    reservation_name,
    avg_provisioned,
    avg_used,
    peak_p50,
    peak_p90,
    utilisation,
    CASE
      WHEN utilisation < 0.50 THEN 'shrink baseline'
      WHEN utilisation < 0.80 THEN 'right-sized (or 3-yr commit)'
      WHEN peak_p90 > avg_provisioned * 1.5 THEN 'grow baseline'
      ELSE 'right-sized'
    END AS recommendation
FROM summary
ORDER BY utilisation;
Enter fullscreen mode Exit fullscreen mode

Step-by-step explanation.

  1. RESERVATION_TIMELINE_BY_ADMIN_PROJECT gives per-minute slot use vs provisioned. Aggregate by day, then by reservation, to see the shape.
  2. utilisation = avg_used / avg_provisioned is the key metric. Under 50% → chronically over-committed (shrink baseline). Between 50-80% → right-sized (or upgrade to 3-year commit for extra 20% off). Over 80% → good utilisation; if peak_p90 exceeds provisioned × 1.5, grow baseline.
  3. Peak p50 vs peak p90 shows the burst shape. If peak_p90 = 2 × peak_p50, the workload has heavy tails and needs a bigger autoscaler max, not a bigger baseline.
  4. Recommendation logic surfaces one of four states per reservation. A team runs this query monthly and adjusts the commit at renewal time (or purchases additional commits mid-term for growing workloads).
  5. Example row: team_a_bi | 200 provisioned | 60 used | peak_p50=100 | peak_p90=180 | util=0.30 | shrink baseline. This reservation is over-committed by 140 slots × 720 hours × $0.048 = $4,838/month. Shrink to 100-slot commit; save $3,456/month.

Output.

Reservation Provisioned Avg used p50 peak p90 peak Utilisation Recommendation
team_a_bi 200 60 100 180 0.30 shrink baseline
team_b_elt 300 220 250 800 0.73 right-sized (or 3-yr commit)
team_c_analytics 400 380 450 950 0.95 grow baseline (peak_p90 = 2.4x provisioned)
shared_background 100 80 90 150 0.80 right-sized

Rule of thumb. Re-audit every reservation monthly. Utilisation under 50% is money burned; utilisation over 100% at the p90 means the autoscaler is doing work the commit should own. The senior loop is audit → adjust → wait 30 days → re-audit.

Senior interview question on mixed workloads

A senior interviewer might ask: "Design the complete BigQuery pricing plan for a 3-environment (dev/staging/prod) analytics org with mixed BI + ELT + ad-hoc + one CMEK-required PII pipeline. Show every commit, reservation, assignment, guard rail, and the monthly monitoring query."

Solution Using a five-layer plan — commits, reservations, assignments, guard rails, monitoring 

# LAYER 1 — commits
# Enterprise 800-slot 1-year commit for prod
bq mk --location=us --capacity_commitment \
      --edition=ENTERPRISE --slots=800 --plan=ANNUAL

# Enterprise Plus 100-slot 1-year commit for PII
bq mk --location=us --capacity_commitment \
      --edition=ENTERPRISE_PLUS --slots=100 --plan=ANNUAL

# LAYER 2 — reservations
bq mk --location=us --reservation --edition=ENTERPRISE \
      --slots=300 --autoscale_max_slots=1000 prod_bi_res

bq mk --location=us --reservation --edition=ENTERPRISE \
      --slots=400 --autoscale_max_slots=1500 prod_elt_res

bq mk --location=us --reservation --edition=ENTERPRISE \
      --slots=100 --autoscale_max_slots=300 prod_bg_res

bq mk --location=us --reservation --edition=ENTERPRISE_PLUS \
      --slots=100 --autoscale_max_slots=300 pii_res

# LAYER 3 — assignments (job-type split)
bq mk --location=us --reservation_assignment \
      --reservation_id=prod_bi_res  --job_type=QUERY \
      --assignee_id=bi-prod --assignee_type=PROJECT

bq mk --location=us --reservation_assignment \
      --reservation_id=prod_elt_res --job_type=PIPELINE \
      --assignee_id=elt-prod --assignee_type=PROJECT

bq mk --location=us --reservation_assignment \
      --reservation_id=prod_bg_res  --job_type=BACKGROUND \
      --assignee_id=analytics-org --assignee_type=ORGANIZATION

bq mk --location=us --reservation_assignment \
      --reservation_id=pii_res      --job_type=QUERY \
      --assignee_id=pii-prod --assignee_type=PROJECT

# LAYER 4 — guard rails
bq update --project_id=analytics-dev     --maximum_bytes_billed=107374182400  # 100 GB
bq update --project_id=analytics-staging --maximum_bytes_billed=536870912000  # 500 GB
bq update --project_id=analytics-prod    --default_query_job_timeout_ms=1800000
Enter fullscreen mode Exit fullscreen mode 
-- LAYER 5 — monitoring: single monthly rollup query
CREATE OR REPLACE TABLE `analytics-prod._metrics.monthly_cost_rollup` AS
WITH by_project AS (
  SELECT
      DATE_TRUNC(DATE(creation_time), MONTH) AS month,
      project_id,
      reservation_id,
      ROUND(SUM(total_bytes_billed) / POW(1024, 4), 2) AS tb_billed,
      ROUND(SUM(total_slot_ms) / 3600000.0, 1)         AS slot_hours,
      COUNT(*)                                          AS job_count
  FROM   `region-us`.INFORMATION_SCHEMA.JOBS_BY_PROJECT
  WHERE  creation_time >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 90 DAY)
    AND  state = 'DONE'
  GROUP  BY month, project_id, reservation_id
),
priced AS (
  SELECT
      month, project_id, reservation_id, tb_billed, slot_hours, job_count,
      CASE
        WHEN reservation_id IS NULL       THEN tb_billed * 6.25
        WHEN reservation_id LIKE '%pii%'  THEN slot_hours * 0.08     -- 1-yr E+ rate
        ELSE slot_hours * 0.048                                       -- 1-yr E rate
      END AS estimated_usd
  FROM by_project
)
SELECT * FROM priced
ORDER BY month DESC, estimated_usd DESC;
Enter fullscreen mode Exit fullscreen mode

Step-by-step trace.

Layer Purpose Output
Commits Buy the discounted slot-hours 800 Enterprise + 100 Enterprise Plus
Reservations Split the commits into pools 4 reservations (BI, ELT, BG, PII)
Assignments Route jobs to pools 4 assignments (per-job-type)
Guard rails Cap runaway spend Dev cap, staging cap, prod timeout
Monitoring Track actuals vs plan Monthly rollup + weekly alert

Environment split: dev + staging on on-demand with per-query caps; prod on Enterprise commit; PII on Enterprise Plus commit. Job-type split within prod: BI QUERY → prod_bi_res; ELT PIPELINE → prod_elt_res; background jobs → prod_bg_res.

Monthly costs (approximate):

  • Dev on-demand: ~$200
  • Staging on-demand: ~$100
  • Prod Enterprise commit baseline: 800 × 720 × $0.048 = $27,648
  • Prod Enterprise burst (avg): ~$1,500
  • PII Enterprise Plus baseline: 100 × 720 × $0.08 = $5,760
  • PII Enterprise Plus burst: ~$300
  • Total: ~$35,500/month

Naive alternative — 2000-slot 3-year Enterprise Plus commit for everything: 2000 × 720 × $0.06 = $86,400/month. The hybrid saves 59%.

Output:

Layer Component Value
Commits Enterprise / Enterprise Plus 800 / 100 slots
Reservations Total 4
Assignments Total 4
Guard rails Per-project caps 3
Monitoring Monthly rollup query 1
Monthly cost Blended ~$35,500
Naive alt cost Enterprise Plus 3-yr ~$86,400
Savings — 59%

Why this works — concept by concept:

  • Environment split first — dev and staging on on-demand with per-query bytes caps; prod on committed slots. This one decision saves ~20% on its own by keeping unpredictable dev off a commit.
  • Workload split by reservation — BI, ELT, background, PII each get their own reservation. Job-type assignments (QUERY / PIPELINE / BACKGROUND) route work to the right pool. No workload can starve another.
  • Right-sized commits — commits sized to p50 of daily peak, not p95. Autoscaler covers the burst at the on-demand slot rate.
  • Enterprise Plus only where required — the 67% E+ premium is paid only on the 100-slot PII commit, not on the whole 900-slot pool.
  • Guard rails layered — per-query bytes cap, per-project timeout, autoscaler max. Three independent limits mean a bad query has to bypass all three to be expensive.
  • Monthly monitoring — the rollup query lands in a _metrics dataset. Anomaly detection compares month-over-month; a 3σ spike auto-pages the platform team.
  • Cost — 5 CLI commands per commit; O(1) operational overhead per reservation. The saving is O(mix-complexity) per month — the more diverse the workload, the bigger the hybrid win.

SQL
Topic — sql
SQL mixed-workload cost-attribution problems

Practice →

Optimization
Topic — optimization
Optimization problems on hybrid pricing and autoscaler tuning

Practice →

Cheat sheet — BigQuery pricing recipes

  • On-demand cost formula. monthly_cost_usd = tb_scanned_per_month × $6.25. Bytes scanned = only referenced columns × only matched partitions. Every SELECT audits down to column projection + partition prune + wide-join projection. Set project-level maximum_bytes_billed as the circuit breaker.
  • Editions autoscaler config. baseline = p50(daily_peak_slots), max = 2-3 × baseline, edition = ENTERPRISE (default), plan = ANNUAL (1-year commit ~20% off) or NONE (pure autoscaler). For bursty workloads (duty cycle < 30%) skip the commit; for steady workloads (duty cycle > 70%) commit for 1 year; for genuinely stable 3-year plans commit for 3 years (~40% off).
  • Reservation assignment SQL. bq mk --location=us --reservation_assignment --reservation_id=<name> --job_type=<QUERY|PIPELINE|BACKGROUND> --assignee_id=<project> --assignee_type=PROJECT. Split QUERY / PIPELINE / BACKGROUND across three reservations to isolate interactive queries from ELT lag.
  • Cost estimator query for a workload. SELECT SUM(total_bytes_billed)/POW(1024,4)*6.25 AS on_demand_usd, SUM(total_slot_ms)/3600000.0*0.048 AS one_yr_commit_usd FROM region-us.INFORMATION_SCHEMA.JOBS_BY_PROJECT WHERE creation_time >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 30 DAY). Compare the two numbers to see which model would have been cheaper last month.
  • Commit break-even calculator. 1-year commit break-even = 80% utilisation. 3-year commit break-even = 60% utilisation. Below these thresholds, on-demand or pure autoscaler wins.
  • Editions tier picker. Standard = vanilla SQL + streaming. Enterprise = BI Engine + BQ ML + external tables + row/col security. Enterprise Plus = CMEK + VPC-SC + cross-region + extended time travel. Pick the cheapest tier that includes the features you need.
  • require_partition_filter. ALTER TABLE <t> SET OPTIONS (require_partition_filter = TRUE) on every partitioned fact. Runs the audit first: INFORMATION_SCHEMA.JOBS_BY_PROJECT for any query hitting the table without a partition filter.
  • maximum_bytes_billed. bq update --maximum_bytes_billed=<N> <project> for a per-query bytes cap. Dev at 100 GB; staging at 500 GB; prod at 1 TB (or per-user override). Query-level: SET @@job_config.maximum_bytes_billed = ....
  • Idle capacity sharing. ON by default across reservations in the same admin project. Buy one commit sized to the sum of workload p50s (not the sum of peaks); split into per-team reservations; idle sharing recovers ~30% of naive per-team commit cost.
  • Autoscaler max. Never set to region-max. max = 2-3 × baseline. Paired with job_timeout_ms = 1800000 (30 min) at the project level, a runaway query costs ~$20 not $500.
  • Reservation utilisation audit. Query RESERVATION_TIMELINE_BY_ADMIN_PROJECT monthly. Utilisation < 50% → shrink baseline. Utilisation 50-80% → right-sized (consider 3-yr commit). p90 peak > 1.5 × baseline → grow baseline (or accept autoscaler cost).
  • CMEK isolation. Enterprise Plus for CMEK-required datasets only; split into a dedicated project with its own reservation. Everything else stays on Enterprise. Pays the 67% E+ premium on ~10% of the workload instead of 100%.
  • Dev/staging/prod split. Dev on-demand + maximum_bytes_billed = 100 GB per query. Staging on-demand + 500 GB. Prod Enterprise commit. Naive "prod-sized commit for everything" is one of the most common cost anti-patterns.

Frequently asked questions

BigQuery on-demand vs Editions — which pricing model should I pick in 2026?

Default to hybrid: bigquery on-demand for dev, staging, ad-hoc analyst queries, and workloads with duty cycle under 30%; bigquery editions (Standard, Enterprise, or Enterprise Plus) for prod workloads with steady state or predictable peaks. On-demand bills bytes scanned at $6.25/TB with no commit — perfect for unpredictable workloads. Editions bills slot-time (~$0.04-$0.10/slot-hour depending on tier) with optional 1-year or 3-year commits for 20-40% off — perfect for BI dashboards, always-on ELTs, and any workload where you know the slot shape ahead of time. The senior mistake is picking one model for the whole account; the senior signal is showing four axes (unit, elasticity, commit, workload mix) and mapping each workload to the model that fits its billing atom.

What is a BigQuery slot and how does it differ from a byte?

A BigQuery slot is one unit of computational capacity — roughly one CPU thread with associated memory and shuffle bandwidth allocated for query execution. Slots are the billing atom for bigquery editions. A bigquery slots reservation of 500 slots means BigQuery guarantees you 500 concurrent execution slots for the duration of the reservation. Under bigquery on-demand, BigQuery auto-provisions slots (up to a per-project burst cap of typically 2000) and bills you on bytes scanned instead — you never see the slot count. The distinction matters because slot-time is per-second (predictable, commit-eligible), while bytes-scanned is per-query (unpredictable, no commit possible). Any senior interview answer that names slots as "compute" and bytes as "storage read" is on the right path; a candidate who says "slots are like servers" is close enough for a mid-level pass, but the exact right answer is "one slot ≈ one CPU thread + memory + shuffle capacity, provisioned for the reservation lifetime."

Do I need a BigQuery reservation to use Editions?

Yes — bigquery reservations are the delivery mechanism for Editions. When you buy an Editions slot-hour, you're buying it against a reservation that specifies the Edition (Standard / Enterprise / Enterprise Plus), the baseline slots, and the autoscaler max. Without a reservation, queries run on bigquery on-demand at bytes-scanned rates. You can also mix — some projects on reservations (billed slot-time) and others on-demand (billed bytes). The flat-rate era of "buy a fixed slot count and pay a flat monthly rate" is gone; 2026 is fully autoscaler-based with the commit as the discount layer. If you see a job with reservation_id = NULL in JOBS_BY_PROJECT, it ran on-demand; anything with a reservation_id set ran on an Editions reservation.

How do I estimate my BigQuery commit size before I buy?

Run this audit against 30–90 days of JOBS_TIMELINE_BY_PROJECT: aggregate period_slot_ms / 1000 per day, take the MAX per day as the daily peak, then take APPROX_QUANTILES(daily_peak, 100)[OFFSET(50)] as the p50. Commit at the p50 of daily peak, not the p95 or the max. The autoscaler covers p95 and beyond at the on-demand slot rate; committing to p95 pays a 24-hour rate for capacity used a few hours a week. Break-even math: a 1-year commit needs 80%+ utilisation to beat on-demand; a 3-year commit needs 60%+. Below those thresholds, autoscaler-only wins. Re-audit monthly and adjust; commits are locked for the term but you can add more commits mid-term for growing workloads.

Which BigQuery Edition should I pick — Standard, Enterprise, or Enterprise Plus?

Pick by feature requirement, never by price. standard edition (~$0.04/slot-hour) is the cheapest — bare-bones SQL + streaming ingest + basic materialised views. No BI Engine, no BQ ML, no external tables, no CMEK. Right for CDC pipelines that terminate at raw storage. enterprise edition (~$0.06/slot-hour) adds BI Engine (memory cache for BI dashboards), full BQ ML, external tables (Iceberg / Delta / BigLake), row-level and column-level security. Right for real analytics deployments — this is the "default" tier in 2026. Enterprise Plus (~$0.10/slot-hour) adds CMEK (customer-managed keys), VPC Service Controls, cross-region replication, extended time travel, and disaster-recovery reservations. Right only when compliance or DR is a hard requirement. Never pick Enterprise Plus for performance or cost — the premium is 67% higher than Enterprise for the same slots.

Can I mix on-demand and Editions in the same BigQuery deployment?

Yes — this is the recommended pattern in 2026. Reservations are assigned per project, not per account, so different projects can use different pricing models. A common setup: analytics-dev runs on-demand with a per-user 100 GB bytes cap (unpredictable ad-hoc queries), analytics-prod runs on a 400-slot Enterprise commit (BI + ELT steady state), pii-prod runs on a 100-slot Enterprise Plus commit (CMEK required), and analytics-adhoc (a separate ad-hoc project) also runs on-demand. Queries land on whichever pricing model their project is assigned to. The hybrid typically saves 40–60% compared to picking one model (usually Enterprise Plus 3-year commit) for the whole account, because ad-hoc and dev workloads don't pay the always-on commit tax and BI dashboards don't pay the on-demand-per-scan tax.

Practice on PipeCode

  • Drill the SQL practice library → for the cost-attribution, partition-prune, and column-projection queries senior BigQuery interviewers love.
  • Rehearse on the ETL practice library → for the reservation-sizing, autoscaler-tuning, and commit-break-even patterns that show up in every senior GCP loop.
  • Sharpen the tuning axis with the optimization practice library → for the workload-vs-model decision matrix, INFORMATION_SCHEMA audit, and hybrid pricing exercises.
  • Stack the prerequisites against PipeCode's broader 450+ data-engineering catalogue to anchor the BigQuery billing-atom intuition against real graded inputs.

Lock in BigQuery pricing muscle memory

Google docs explain the SKUs. PipeCode drills explain the decision — when on-demand beats Editions, when a 1-year commit beats a 3-year commit, when Enterprise Plus is only paying for CMEK, when the autoscaler max is the difference between $20 and $500 per bad query. Pipecode.ai is Leetcode for Data Engineering — pattern-first practice tuned for the production trade-offs senior data engineers actually face.

Practice SQL problems →
Practice optimization problems →

Top comments (0)