BigQuery Reservations and Workload Management: Designing Slot Pools for Multiple Teams

Most BigQuery cost and performance complaints are not really query problems. They are workload management problems: the finance ELT job and an analyst's exploratory SELECT * are competing for the same slots, nobody can tell which team burned the capacity, and the fix everyone reaches for is "buy more slots."

This tutorial walks through designing reservations and assignments for a multi-team estate: how the scheduler actually shares slots, how to carve capacity by team without stranding it, how to mix on-demand and reserved compute, and how to prove after the fact that the design is working. It assumes you already know the difference between on-demand and Editions pricing — if not, read BigQuery Editions Explained first.

The three objects you are actually configuring

Workload management in BigQuery is three nested objects, and almost every misconfiguration comes from confusing them.

1. Capacity commitment — a purchase of slot capacity for a region, either on a discounted one- or three-year term, or as pay-as-you-go capacity you can cancel. Commitments buy slots; they do not route any query anywhere.
2. Reservation — a named pool carved out of the capacity available in that region, with a baseline (slots always provisioned, always billed) and a max (the autoscaling ceiling). This is the unit you name after a team or a workload: elt, bi, adhoc, ml.
3. Assignment — a mapping from an organization, folder, or project to a reservation, for a given job_type (QUERY, PIPELINE, ML_EXTERNAL, BACKGROUND). This is what actually routes work.

Assignments resolve most-specific-first: a project-level assignment beats a folder-level one, which beats an organization-level one. Anything with no resolvable assignment falls back to on-demand billing — which is a feature, not a bug, and we will use it deliberately later.

Step 1: measure before you carve

Do not design pools from an org chart. Design them from INFORMATION_SCHEMA. Start with slot consumption per project, per hour, over a representative month:

SELECT
  project_id,
  TIMESTAMP_TRUNC(creation_time, HOUR) AS hr,
  SUM(total_slot_ms) / (1000 * 60 * 60) AS slot_hours,
  COUNT(*) AS jobs
FROM `region-us`.INFORMATION_SCHEMA.JOBS_BY_ORGANIZATION
WHERE creation_time >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 30 DAY)
  AND job_type = 'QUERY'
  AND statement_type != 'SCRIPT'
GROUP BY project_id, hr
ORDER BY slot_hours DESC;

total_slot_ms / elapsed_ms for a single job gives you its average slot usage, which is the number that matters for sizing. Two workloads that each average 300 slots but peak at opposite ends of the clock can share one pool happily; two that both peak at 06:00 cannot.

Then find out how much time is already being lost to queuing, because that is the symptom a reservation redesign is supposed to cure:

SELECT
  project_id,
  COUNTIF(job_creation_reason.code IS NOT NULL) AS jobs,
  APPROX_QUANTILES(TIMESTAMP_DIFF(start_time, creation_time, SECOND), 100)[OFFSET(50)] AS p50_queue_s,
  APPROX_QUANTILES(TIMESTAMP_DIFF(start_time, creation_time, SECOND), 100)[OFFSET(95)] AS p95_queue_s
FROM `region-us`.INFORMATION_SCHEMA.JOBS_BY_ORGANIZATION
WHERE creation_time >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 7 DAY)
  AND job_type = 'QUERY'
GROUP BY project_id
HAVING p95_queue_s > 5
ORDER BY p95_queue_s DESC;

If p95 queue time is under a second or two everywhere, your problem is query design, not capacity allocation. Stop here and go tune partitioning instead.

Step 2: carve pools by behaviour, not by team

A good reservation boundary separates workloads with different tolerance for latency. Three or four pools is usually right; a dozen is a sign you are modelling the org chart.

Pool	Baseline	Max	Idle sharing	Rationale
elt	500	1,500	ignore idle	Scheduled, deadline-bound, predictable shape
bi	200	800	use idle	Dashboard concurrency, must stay interactive
adhoc	0	600	use idle	Bursty, latency-tolerant, cheapest when quiet
ml	0	400	use idle	Training spikes, tolerant of a few minutes' wait

Two knobs do most of the work here:

• baseline is billed continuously whether or not it is used. Set it to roughly the steady-state floor of the workload, not the peak. A baseline of 0 with a healthy max is the correct setting for anything spiky.
• ignore_idle_slots controls whether the pool may borrow idle slots from other reservations in the same admin project (false = borrowing allowed). Leave borrowing on for interactive pools so they can absorb spikes for free. Turn it off for the pool whose runtime you want to be predictable — usually elt — because borrowed slots can be reclaimed mid-query and make the same job take 20 minutes one night and 55 the next.

Create the pool and route work to it:

# A pay-as-you-go Enterprise commitment in the US multi-region
bq mk --capacity_commitment \
  --project_id=my-admin-project \
  --location=US \
  --edition=ENTERPRISE \
  --plan=NONE \
  --slots=500

# A reservation with a 200-slot floor that can autoscale to 800
bq mk --reservation \
  --project_id=my-admin-project \
  --location=US \
  --edition=ENTERPRISE \
  --slots=200 \
  --autoscale_max_slots=800 \
  --ignore_idle_slots=false \
  bi

# Route one project's interactive queries into it
bq mk --reservation_assignment \
  --project_id=my-admin-project \
  --location=US \
  --reservation_id=my-admin-project:US.bi \
  --assignee_type=PROJECT \
  --assignee_id=analytics-prod \
  --job_type=QUERY

A few field-tested details:

• Autoscaling scales up in increments of 50 slots and bills a one-minute minimum per scaled slot. A pool that flaps between 100 and 300 slots every 45 seconds costs noticeably more than its average usage suggests. Batch your scheduled jobs rather than dribbling them out one per minute.
• Scale-down is gradual and driven by the last minute of demand. Do not expect an idle pool to bill zero instantly.
• job_type=PIPELINE covers load and export jobs, and BACKGROUND covers internal maintenance work like Iceberg/BigLake table optimization. If you only assign QUERY, those other job types quietly fall back to on-demand or to a broader assignment. Decide that deliberately.

Step 3: give priority workloads their own admin project

Idle-slot sharing only happens within a single administration project. This is the most useful and least-known lever in the whole system: if you need hard isolation — a regulated workload, or a tenant you bill separately — put its commitments and reservations in a different admin project. Nothing outside can borrow its slots and it can borrow nothing from outside.

Conversely, if you want maximum efficiency, keep everything in one admin project so unused elt capacity flows to adhoc at 03:00 for free.

Step 4: keep a deliberate on-demand escape hatch

Reserved capacity and on-demand are not either/or. A pattern that works well:

• Production ELT, BI, and ML → reservations, so cost is a predictable slot-hour bill.
• Sandbox and data-science exploration projects → no assignment at all, so they run on-demand and are billed by bytes scanned. Then cap the damage with a custom quota rather than a reservation ceiling:

# Per-user daily query bytes cap on a sandbox project
gcloud alpha services quota update \
  --service=bigquery.googleapis.com \
  --consumer=projects/sandbox-project \
  --metric=bigquery.googleapis.com/quota/query/usage \
  --unit=1/d/{project}/{user} \
  --value=2000  # GiB per user per day

This gives exploratory users instant, unqueued capacity without letting one SELECT * on a 900 TiB table become a line item. A reservation would have made them wait; a quota makes them careful.

To move a project from reservations to on-demand, delete its assignment — there is no separate "on-demand mode" to enable. And remember bq query --maximum_bytes_billed (or the maximumBytesBilled job property) as a per-query seatbelt for anything on-demand.

Step 5: prove it worked

Reservation design is only credible with an after-the-fact ledger. Two views are enough.

Are the pools sized right? Compare assigned capacity to actual consumption:

SELECT
  reservation_name,
  TIMESTAMP_TRUNC(period_start, HOUR) AS hr,
  SUM(slots_assigned)  AS slots_assigned,
  SUM(slots_max_assigned) AS slots_max,
  SUM(period_slot_ms) / (1000 * 60 * 60) AS slot_hours_used
FROM `region-us`.INFORMATION_SCHEMA.RESERVATION_TIMELINE_BY_PROJECT
WHERE period_start >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 14 DAY)
GROUP BY reservation_name, hr
ORDER BY hr DESC, reservation_name;

A pool that never approaches its max has a max that is decoration. A pool pinned at max for hours every day is a pool that is silently adding queue time — cross-check against the queue query from Step 1.

Who consumed what? Attribute slot-hours back to teams for showback:

SELECT
  reservation_id,
  project_id,
  SUM(total_slot_ms) / (1000 * 60 * 60) AS slot_hours,
  SUM(total_bytes_billed) / POW(1024, 4) AS tib_billed,
  COUNT(*) AS jobs
FROM `region-us`.INFORMATION_SCHEMA.JOBS_BY_ORGANIZATION
WHERE creation_time >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 30 DAY)
  AND job_type = 'QUERY'
GROUP BY reservation_id, project_id
ORDER BY slot_hours DESC;

Note that reservation_id is NULL for on-demand jobs, and total_bytes_billed is what you care about for those while slot_hours is what you care about for reserved ones. One table, two cost models — label the columns clearly before you show this to finance.

Step 6: manage it as code

Reservations drift badly when they are edited in the console during incidents. Put them in Terraform alongside your datasets:

resource "google_bigquery_capacity_commitment" "enterprise" {
  capacity_commitment_id = "enterprise-500"
  location               = "US"
  slot_count             = 500
  plan                   = "THREE_YEAR"
  edition                = "ENTERPRISE"
}

resource "google_bigquery_reservation" "elt" {
  name              = "elt"
  location          = "US"
  slot_capacity     = 500
  ignore_idle_slots = true # predictable runtimes, no borrowing

  autoscale {
    max_slots = 1000
  }
}

resource "google_bigquery_reservation_assignment" "elt_pipelines" {
  reservation = google_bigquery_reservation.elt.id
  assignee    = "projects/elt-prod"
  job_type    = "PIPELINE"
}

One caution: long-term capacity commitments are not casually destroyable, so guard them with lifecycle { prevent_destroy = true } and review plans that touch them by hand. See Managing BigQuery with Terraform for the wider repo layout.

A short checklist

• Size baselines from the steady-state floor, not the peak; let autoscaling handle peaks.
• ignore_idle_slots = true on exactly one pool — the one that must finish on time.
• Separate admin projects only when you need hard isolation; otherwise share idle slots.
• Assign PIPELINE and BACKGROUND job types explicitly, not just QUERY.
• Leave sandboxes on-demand and control them with custom quotas plus maximum_bytes_billed.
• Review RESERVATION_TIMELINE_BY_PROJECT and p95 queue time monthly; re-carve when the shape of the workload changes, not when someone complains once.

Where this usually goes wrong

The failure mode we see most often in client estates is a single 2,000-slot reservation assigned at the organization level, with every project inheriting it. It looks tidy, it behaves like a shared bathtub: one bad backfill drains the dashboards, and because everything shares one reservation_id, there is no data to argue with. Splitting that into three behaviour-based pools with honest baselines routinely cuts both the bill and the p95 dashboard latency at the same time — no query was rewritten.

If you want a second pair of eyes on a slot allocation, or you are staring at a queue-time chart you can't explain, our team does this as BigQuery Cost Optimization & Editions Advisory and performance optimization work. Get in touch with a month of INFORMATION_SCHEMA numbers and we can usually tell you in an hour whether you have a capacity problem or a SQL problem.