skip to content

When would you move a BigQuery workload from on-demand pricing to slot reservations?

level: seniorimportance: should knowfreq 58%

answer

  1. one model bills reads, the other bills time
  2. the answer starts with a measurement
  3. slot-milliseconds tell you the size
  4. idle capacity is the risk on one side
  5. assignments are per project, so mix freely

basics

~20 s

Switch when spend is high and predictable: measure actual slot demand from INFORMATION_SCHEMA total_slot_ms, compare a reservation sized to that demand against your per-byte spend, and move when capacity is cheaper or you need predictable cost and performance.

solid answer

~50 s

It is a measurement, not a preference. Query `INFORMATION_SCHEMA.JOBS` over a representative month for `SUM(total_bytes_billed)` — your on-demand spend — and `SUM(total_slot_ms)`, which converted to average and peak concurrent slots tells you how big a reservation would have to be. If a reservation sized to real demand costs less than the byte bill, capacity wins on price; commitments discount that further for the stable floor. Capacity also changes the failure mode: a careless `SELECT *` costs slot time and queueing rather than dollars, which is worth a lot for organizations with many analysts. On-demand stays right for spiky, low-volume or unpredictable workloads, where a reservation would sit idle, and it gives clean per-query cost attribution. The two are not exclusive — reservation **assignments** are per project or folder, so pipelines can run on committed slots while exploratory projects stay on-demand.

code

sql · 9 lines
sql
SELECT
  TIMESTAMP_TRUNC(creation_time, HOUR) AS hr,
  SUM(total_slot_ms) / (60 * 60 * 1000) AS avg_concurrent_slots,
  SUM(total_bytes_billed) / POW(1024, 4) AS tb_billed
FROM `region-us`.INFORMATION_SCHEMA.JOBS_BY_PROJECT
WHERE creation_time >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 30 DAY)
  AND job_type = 'QUERY'
GROUP BY hr
ORDER BY hr;

go deeper

for a junior

Know that BigQuery offers two compute billing models — per byte scanned, or paying for slots over time — and that the second suits steady heavy usage.

for a middle

Explain what a slot reservation is, that assignments bind projects to it, and that under capacity pricing a big scan costs slot time and queueing rather than bytes.

for a senior

Demonstrate the measurement: bytes billed and total_slot_ms from INFORMATION_SCHEMA, hourly concurrency to size a baseline, then an incremental per-project migration you can watch and reverse.

for a principal

Own the commitment strategy and its risk: what floor you lock for one or three years, how much you leave to autoscaling, what stays on-demand, and how compute cost is charged back to teams.

## The two models BigQuery bills compute one of two ways. **On-demand** charges per byte processed by each query, with no capacity to manage. **Capacity pricing** (BigQuery editions — Standard, Enterprise, Enterprise Plus) charges for **slots**, units of compute, over time: you create a reservation with a baseline number of slots and optionally an autoscaling maximum, and queries assigned to it use those slots for free at the byte level. Legacy flat-rate has been replaced by the editions model, so a current answer should talk about editions, reservations and autoscaling rather than fixed flat-rate slot bundles. ## Do the measurement first A candidate who answers "switch when you spend a lot" has not answered. The switch is decided from two numbers over a representative period, both in `INFORMATION_SCHEMA.JOBS`: ```sql SELECT DATE(creation_time) AS day, SUM(total_bytes_billed) / POW(1024, 4) AS tb_billed, SUM(total_slot_ms) / 1000 / 3600 AS slot_hours FROM `region-us`.INFORMATION_SCHEMA.JOBS_BY_PROJECT WHERE creation_time >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 30 DAY) AND job_type = 'QUERY' GROUP BY day ORDER BY day; ``` `tb_billed` times the on-demand rate is what you pay today. `total_slot_ms` is what the work actually consumed: dividing by the wall-clock milliseconds of a window gives the **average concurrent slots** used in that window. Do it hourly rather than daily, because the average over a day hides the shape — a workload that needs 2,000 slots for two hours and 50 slots the rest of the day averages low and behaves nothing like a flat 150-slot reservation. ## What the shape tells you **Flat and heavy** — steady ELT and dashboards through the working day. This is the ideal capacity candidate: a baseline reservation is busy nearly all the time, commitments buy the floor at a discount, and autoscaling absorbs the peaks. **Spiky and light** — a few heavy days a quarter, near-idle otherwise. On-demand fits: you pay nothing for idle time, whereas a reservation sized to the peak is wasted most of the month and one sized to the average makes the peak crawl. **Heavy but unpredictable** — an autoscaling reservation with a small baseline is the middle path, though autoscaled slots cost more per slot than committed ones, so the economics only work if the peaks are genuinely intermittent. ## What changes besides the price **Failure mode.** Under on-demand, a careless full-table scan is a surprise on the invoice. Under capacity, the same query consumes slot time and delays other queries — a performance incident, not a financial one. For an organization with many analysts and weak query hygiene, converting unbounded cost risk into bounded contention is often the real motivation. **Predictability.** Capacity gives a known monthly compute number, which finance likes, and removes the per-query cost coupling to table growth. It also introduces a new operational job: watching for queueing, sizing reservations, and isolating workloads that must not starve each other. **Attribution.** On-demand attributes cost cleanly per query and per user through `total_bytes_billed`. Under capacity you attribute by reservation, by labels, or by `total_slot_ms` share — a chargeback model you have to build rather than one you get for free. ## It is not all-or-nothing Reservations take effect through **assignments** that bind an organization, folder or project to a reservation for a given job type. So the common production layout is mixed: production ELT and BI projects assigned to committed reservations, sandbox and ad-hoc projects left on on-demand where their spiky usage costs nothing when idle. Projects with no assignment simply fall back to on-demand billing. Migration can therefore be incremental — assign one project, watch queueing and cost for a week, then assign the next. ## Commitments and their risk One-year and three-year capacity commitments discount the baseline in exchange for locking spend. Size a commitment to the floor you are confident about — the capacity you would be embarrassed to be without — and let autoscaling handle everything above it. Committing to the peak is the classic overspend, and it is worse than on-demand because the waste is invisible: idle slots do not appear on any query's bill. ## The honest summary Move to capacity when the workload is large enough and steady enough that a reservation stays busy, when predictable spend matters more than per-query elasticity, or when you want careless queries to cost time rather than money. Stay on-demand when usage is spiky, small, or hard to forecast — and expect to end up running both.

  • How do you convert total_slot_ms into a reservation size?
    Sum `total_slot_ms` per hour and divide by the hour's wall-clock milliseconds — that gives average concurrent slots for the hour. Chart it across a representative month and read the sustained level as your baseline and the peaks as the autoscaling maximum. Averaging over a day instead of an hour hides the shape and undersizes the reservation.
  • Under capacity pricing, what happens when demand exceeds the reservation's slots?
    Queries do not fail; they queue and take longer, sharing the available slots. That is the point of the model — cost is bounded, latency is the pressure valve. Autoscaling raises the ceiling up to a configured maximum, and idle-slot sharing lets a reservation borrow unused capacity from others in the same admin project.
  • Can some projects stay on on-demand while others use a reservation?
    Yes. Reservations apply through assignments that bind an organization, folder or project to a reservation for a job type. Anything without an applicable assignment falls back to on-demand billing, so a common layout puts production ELT and BI on committed slots and leaves sandbox projects on-demand.

On-demand is taking taxis: you pay per trip and nothing when you stay home. Capacity is leasing a fleet: cheaper once the vehicles are on the road most of the day, wasteful when they sit in the garage.

saying these in an interview costs you the question

  • Recommends switching on gut feel without measuring slot usage
  • Sizes a commitment to the observed peak
  • Thinks reservations make expensive queries fast automatically
  • Assumes the choice is all-or-nothing for the whole organization
  • Believes queries fail when a reservation runs out of slots

context

resolving links…