Maintaining and automating data workloads · Topic 3 of 5
BigQuery Editions, Reservations and Query Priority
BigQuery capacity: on-demand or editions, baseline and autoscaling slots, commitments, reservations, assignments, idle slots, interactive or batch jobs.
- 5 min
- On-demand bills bytes; editions bill slot-hours.
- Standard, Enterprise and Enterprise Plus differ in features; commitments need Enterprise or higher.
- Baseline slots are always on; autoscaling adds slots up to a maximum when needed.
What it is
BigQuery runs queries on slots, units of compute. Capacity management decides how you pay for slots (per byte on demand, or per slot with an edition) and how slots are divided between workloads so that critical jobs are never starved. Query priority decides whether a job runs now or waits in a queue.
How it works
On-demand or capacity pricing
| Model | You pay for | Fits |
|---|---|---|
| On-demand | Bytes processed per query | Variable or small usage; no capacity planning |
| Capacity (editions) | Slot-hours, whatever the bytes | High, steady usage; predictable cost; workload isolation |
On-demand projects share a pool of slots with a per-project limit. Heavy concurrent usage can therefore queue, and cost grows with every byte scanned.
BigQuery editions
| Edition | Positioning |
|---|---|
| Standard | Entry level: autoscaling slots, no capacity commitments, a reduced feature set |
| Enterprise | Commitments and baseline slots, plus the broad set of security, governance and analytics features most enterprises need |
| Enterprise Plus | Enterprise features plus managed disaster recovery and compliance controls through Assured Workloads |
Editions are chosen per reservation, so one organization can run Standard for development and Enterprise for production.
Reservations, baseline and autoscaling
| Term | Meaning |
|---|---|
| Reservation | A named pool of slots in one location, created in an administration project |
| Baseline slots | Always allocated to the reservation and billed continuously |
| Autoscaling | Adds slots up to a maximum when demand rises; billed only while scaled up |
| Commitment | One-year or three-year purchase of slots at a discount (Enterprise and Enterprise Plus) |
| Assignment | Connects a project, folder or organization to a reservation for a job type such as QUERY or PIPELINE |
| Idle slots | Unused baseline slots that other reservations in the same administration project can borrow, unless that is turned off |
CREATE RESERVATION `bq-admin.region-eu.finance`
OPTIONS (edition = 'ENTERPRISE', slot_capacity = 200, autoscale_max_slots = 400);
CREATE ASSIGNMENT `bq-admin.region-eu.finance.financeetl`
OPTIONS (assignee = 'projects/finance-etl', job_type = 'QUERY');Organizing workloads by business need
| Workload | Typical setup |
|---|---|
| Business-critical ETL with a deadline | Dedicated reservation with baseline slots |
| BI dashboards with many small queries | Separate reservation sized for concurrency, often with BI Engine |
| Ad hoc data science | Autoscaling-only reservation or on-demand with custom quotas |
| Development projects | Standard edition reservation, or on-demand with low quotas |
Separating workloads stops a burst of ad hoc queries from slowing the finance close. Idle slot sharing still lets one workload borrow capacity the others are not using.
"The nightly ETL misses its deadline when analysts run heavy queries" is solved by separate reservations with guaranteed baseline slots for ETL, not by more on-demand capacity.
Interactive or batch query jobs
| Priority | Behaviour | Use it for |
|---|---|---|
| Interactive (default) | Runs as soon as possible | Dashboards, analysts waiting for results |
| Batch | Queued and started when idle resources are available | Reports and back-office jobs that can wait |
Queued queries that have not started time out: by default after 24 hours for batch and 6 hours for interactive. On-demand pricing is the same for both priorities.
bq query --batch --use_legacy_sql=false \
'SELECT region, SUM(amount) FROM sales.order_facts GROUP BY region'Managing jobs, queries and capacity
- Cancel runaway jobs with
bq cancel JOB_IDor from the console. - Move a project to another reservation by changing its assignment, with no downtime.
- Raise or lower baseline slots and the autoscaling maximum as demand changes.
- Use
INFORMATION_SCHEMA.JOBSand the reservation views to see slot use per workload.
A worked example
A retailer on on-demand pricing pays a high, unpredictable bill, and the 05:00 finance job is late when analysts query at the same time. The engineer creates an Enterprise reservation with baseline slots for the finance project and an autoscaling-only reservation for analysts.
Monthly reports switch to batch priority. Slot usage charts show the finance job now finishes by 04:30, and cost is steadier.
How the exam asks about this
- "Guarantee capacity for a critical pipeline while other teams share what is left." A reservation with baseline slots assigned to the pipeline's project; idle slots can be shared.
- "Costs vary wildly month to month with steady heavy usage; finance wants predictability." Move to an edition with reservations, and a commitment if usage is stable.
- "Reports can run any time in the next few hours and should not compete with dashboards." Run them as batch priority queries.
What to remember for the exam
- On-demand bills bytes; editions bill slot-hours.
- Standard, Enterprise and Enterprise Plus differ in features; commitments need Enterprise or higher.
- Baseline slots are always on; autoscaling adds slots up to a maximum when needed.
- Assignments map projects, folders or the organization to reservations by job type.
- Batch priority queues jobs; by default a batch job still unstarted after 24 hours times out.
Assigning a project to a reservation moves all its matching jobs off on-demand pricing. Size the reservation first, or the project's queries will queue for slots.
Common mistakes
Putting every workload in one reservation. Ad hoc spikes then delay critical jobs.
Buying a commitment for spiky, uncertain usage. Autoscaling or on-demand fits better until usage is steady.
Expecting batch priority to cost less on demand. The price per byte is the same; batch only changes when the query starts.
Check yourself
What is the difference between baseline and autoscaling slots?
Baseline slots are always allocated and billed; autoscaling slots are added up to a maximum only when demand needs them and billed only while used.
What connects a project to a reservation?
An assignment, created for a job type such as QUERY, on a project, folder or organization.
What happens to a batch-priority query that has not started after 24 hours?
With the default queue timeout it times out and does not run; the timeout can be configured.
Topic 3 of 5 in Maintaining and automating data workloads
Test yourself on Maintaining and automating data workloads
The full Google Cloud Professional Data Engineer question bank has 733 questions, with timed practice exams and an explanation for every answer.
This study guide is independent and is not affiliated with, endorsed by, or sponsored by Google Cloud. Vendor names, certification names and exam codes are trademarks of their respective owners and are used for identification only.