skip to content

Under BigQuery capacity pricing, do batch load jobs consume your reserved slots?

level: middleimportance: nice to knowfreq 28%

answer

  1. a binding names more than a project
  2. queries and loads are classified apart
  3. the default keeps loads off your capacity
  4. opting in buys predictability, costs contention
  5. streaming ingest is a separate meter entirely

basics

~10 s

Only if an assignment with job type PIPELINE routes them to a reservation. Otherwise load and export jobs keep running on BigQuery's shared pool, exactly as they do under on-demand pricing.

solid answer

~40 s

Reservation **assignments** are per job type, not just per project. A QUERY assignment routes queries; batch load and export jobs are covered by a **PIPELINE** assignment. If you create only a QUERY assignment, load jobs keep using BigQuery's shared slot pool and do not touch your reservation. Adding a PIPELINE assignment is a deliberate trade: your ingest gets predictable, dedicated throughput instead of competing in a shared pool, but it now consumes slots that queries wanted, so the reservation must be sized for both. Note what this does **not** cover — streaming ingestion through the Storage Write API is billed by bytes ingested regardless of pricing model, so it is unaffected by either choice. Other job types exist for assignments too, including ML_EXTERNAL for externally-trained BigQuery ML models.

code

sql · 6 lines
sql
-- Did loads run in a reservation or on the shared pool?
SELECT job_type, reservation_id, COUNT(*) AS jobs
FROM `region-us`.INFORMATION_SCHEMA.JOBS_BY_PROJECT
WHERE creation_time >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 1 DAY)
GROUP BY job_type, reservation_id
ORDER BY jobs DESC;

go deeper

for a junior

Know that a reservation assignment names a job type, and that queries and batch loads are treated as different kinds of work.

for a middle

Explain the default — loads run on BigQuery's shared pool — and what a PIPELINE assignment changes, including that it puts ingest in competition with queries.

for a senior

Show the operational consequence: size the reservation for the combined shape or split ingest into its own, and verify with job_type and reservation fields in INFORMATION_SCHEMA.JOBS.

for a principal

Own whether ingest deserves guaranteed capacity at all, and model the full bill — query compute, streaming ingestion and storage are three meters that no single pricing decision covers.

## Assignments are typed When a project is bound to a reservation, the binding names a **job type**. The two that matter day to day are `QUERY`, covering SQL query jobs, and `PIPELINE`, covering batch load and export jobs. There are others, including `ML_EXTERNAL` for BigQuery ML models trained outside BigQuery. The typing exists precisely so that ingest and analytics can be given different capacity treatment, and missing it produces a classic surprise: a team migrates to capacity pricing, creates a QUERY assignment, and is puzzled that their loads are unaffected. ## The default: loads run outside your reservation Batch load jobs — ingesting Parquet, Avro, CSV or JSON from object storage — and export jobs have historically run on a shared pool of slots that BigQuery manages, with no compute charge attached. Under on-demand pricing that is all there is. Under capacity pricing it is still the default: without a PIPELINE assignment, loads continue to use that shared pool and your reserved slots are untouched. The practical consequence is that load throughput is best-effort. Most of the time it is fine. When it is not — a large backfill, a tight ingestion SLA, contention you cannot see or control — you have no lever, because the pool is not yours. ## Adding a PIPELINE assignment Creating a PIPELINE assignment for a project routes its load and export jobs into your reservation. Now they run on capacity you control and can size, which is what you want when ingest latency is part of a contract. The cost is real: those slots are the same slots your queries use. A large nightly load will compete with dashboards unless the reservation is sized for the combined shape, or unless ingest gets its own reservation. That second option is the usual production answer for teams with strict ingest SLAs: a separate reservation for the pipeline workload, with the ETL project assigned to it for both PIPELINE and QUERY job types, so the whole ingest-plus-transform chain has a floor of guaranteed capacity that interactive work cannot take. ## What this does not cover **Streaming ingestion is a different meter.** Rows written through the Storage Write API are billed by bytes ingested, independent of whether the project uses on-demand or capacity pricing. Moving to slots does not make streaming free, and no assignment redirects it. A team modelling their BigQuery bill needs streaming ingestion as its own line item alongside query compute and storage. **Transformations written in SQL are queries, not pipelines.** A `CREATE TABLE ... AS SELECT` or a `MERGE` that loads a table is a query job and is covered by the QUERY assignment, not the PIPELINE one, even though a human would call it part of the pipeline. The job type is a technical classification, not a description of intent — this trips people up when they assign PIPELINE to their ELT project and find the transform steps still running on the query reservation. **Copies and metadata operations** are their own job types and generally do not consume reserved query capacity. ## How to check what actually happened `INFORMATION_SCHEMA.JOBS` records `job_type` (`QUERY`, `LOAD`, `EXPORT`, `COPY`) and, for jobs that ran in a reservation, the reservation identifier. Grouping recent jobs by type and reservation shows immediately whether your loads went where you thought they did, and whether they are eating capacity the query workload needs. ## Why this is asked It is a differentiator question rather than a screener. It separates candidates who have read the pricing page from candidates who have actually configured reservations, because the typed-assignment detail only bites once you are operating the model. The answer an interviewer wants: loads default to the shared pool, PIPELINE assignment opts them into your slots, that is a capacity decision with a contention cost, and streaming ingestion is billed separately either way.

  • Is a CREATE TABLE AS SELECT covered by a PIPELINE assignment?
    No — it is a query job, so it runs under the QUERY assignment even though the team thinks of it as pipeline work. Only load and export jobs are PIPELINE. This surprises teams that assign PIPELINE to their ELT project and find the transformation steps still consuming query capacity.
  • Does moving to capacity pricing make streaming ingestion cheaper?
    No. Rows written through the Storage Write API are billed by bytes ingested regardless of whether the project is on on-demand or capacity pricing, and no reservation assignment redirects them. Model streaming ingestion as its own line item alongside query compute and storage.

saying these in an interview costs you the question

  • Assumes every job in a project uses the reservation
  • Thinks a PIPELINE assignment covers SQL transformations
  • Expects slots to make streaming ingestion free
  • Sizes a reservation for queries then adds loads to it
  • Believes loads have no shared-pool capacity by default

context

resolving links…