Ad-hoc queries and BigQuery reservation contention

BigQuery jobs run as INTERACTIVE or BATCH. INTERACTIVE starts now and counts against the reservation's concurrent slot budget as a first-class citizen; BATCH can wait in queue. Humans in the console, Connected Sheets refreshes, and I-will-just-run-this-once notebooks default to INTERACTIVE, and so do a surprising number of dbt runs that nobody set to BATCH.

If those jobs concentrate in local business hours, they pin the same reservation the BI warehouse is using to render the Monday forecast. Contention is less a mystery than a calendar problem: the analyst pasting a SELECT * at 10:14 and the Looker explore the CFO has open are not different products to the slot scheduler. They are two INTERACTIVE jobs.

-- dbt Cloud / Airflow: set job priority
-- BigQuery jobs API
job_config = bigquery.QueryJobConfig(
    priority=bigquery.QueryPriority.BATCH,
    reservation="projects/proj/locations/US/reservations/etl",
)

On-demand projects feel this as bytes and as the fair-share slowdown when many huge scans overlap. Capacity projects feel it as PENDING, dashboards that spin, and a reflex to buy more slots, which is expensive. The cause is often that INTERACTIVE became the default personality of the project instead of a latency SLA for a named workload.

BATCH is not a punishment; it is a way to tell BigQuery that a 40-minute backfill may start when slots exist. Humans hate BATCH because the console spinner sits there, and that hatred is why the console stays INTERACTIVE, which is also why the console should not share a reservation with the SLA dashboard. Priority is a placement decision, not a moral one.

# Backfill / dbt run — may queue
bq query --use_legacy_sql=false --batch \
  --project_id=proj \
  'SELECT …'

# Dashboard / console SLA — default INTERACTIVE
bq query --use_legacy_sql=false \
  --project_id=proj \
  'SELECT …'

The rest of this post splits three problems that get mashed into one Slack thread, explains why SlashPeak will not print a dollar figure for moving a query to 7pm, and outlines a reservation split you can actually ship this week without buying capacity.

Peak-hour ad-hoc asks: are human INTERACTIVE jobs a large share of billed cost during local business hours? Slot contention asks: is this project's demand sitting on the reservation ceiling hour after hour? Query queuing asks: are jobs in PENDING before they start? You can have peak-hour waste with no PENDING, and PENDING with no humans. Treating them as one slider is how you buy slots for a notebook problem.

SlashPeak keeps contention as an advisory with no monthly dollar figure so it is not mixed into the waste total, and puts no dollar figure on queuing or peak-hour spend either. Moving a query off-peak or to BATCH does not always reduce bytes billed; it reduces contention on the pool you already pay for. Printing a fake savings number would train finance to expect a line-item drop the invoice may not show.

Peak hours are local: weekdays, a business-hours window, in the timezone people actually work in. A 2am UTC spike that is 9am in the office is peak and a Saturday warehouse dump is not. If your company is not a 9-to-5 US shop, measure in your own timezone, not America/New_York by accident. Ad-hoc means INTERACTIVE plus a human principal; treat service accounts and BATCH jobs as scheduled even if they run at 10am, since the 10am dbt run is a workload placement problem, not a human-pasting-SQL problem. If dbt runs as a user account in the console, it will look human, a mis-attribution you fix by running dbt as a service account.

Read all three signals together when they coexist. Peak-hour plus contention plus PENDING is the classic shared-reservation story: humans and ETL and BI on one pool. Peak-hour alone is often on-demand, where there is no ceiling but there is still a 10am bytes spike you could move. Contention without humans is ETL overlap. PENDING without contention on this project can mean another project shares the reservation. Split the diagnosis before you split the credit card.

A chart that says peak-hour INTERACTIVE is 62 percent of slot-ms is a slide. A list that names the three users, the Connected Sheets job, and the Looker explore is a conversation. Workload source and principal type are on query findings for that reason. Without names, the ticket sits in a platform queue and dies.

classifyJobSource is how those names appear. Job-id prefixes catch Connected Sheets and scheduled queries. SQL comment headers catch dbt, Looker and Dataform. Airflow's own job labels and job-id prefix catch Airflow. Console jobs often look like bquxjob. Humans versus service accounts come from the email domain. None of that requires reading table contents. It requires the metadata you already have in JOBS.

Once you can say the Sales explore and Priya's notebook are the 10am spike, you can place them: Priya gets a sandbox reservation and BATCH for anything that scans more than a few GB, while the Sales explore stays INTERACTIVE on the BI reservation but should hit a mart, not events. Those are different fixes, and attribution is what stops you from applying one fix to both. Top users by bytes billed in the peak window are the invite list for the review; do not start with a global policy email, start with the three principals who are the spike. Show them total_bytes_billed and the reservation graph. People stop pasting SELECT * when they can see they queued the CFO; they do not stop when a wiki says please use BATCH.

SlashPeak attaches this attribution block to query findings generally. Filter the backlog to console and Sheets during the hours the dashboard is on fire: that filtered list is the sprint, and the unfiltered waste total is the scoreboard.

One reservation for the whole company is a cost-optimization anti-pattern that looks like simplicity. ETL, BI, and humans have different latency SLAs and different willingness to queue. Putting them on one pool means the strictest SLA sets the size, and everyone else donates noise into that size. You buy 2000 slots so a dashboard can render while a MERGE rewrites 4 TB and a notebook scans last year.

Three assignments is the usual split: etl, bi, and sandbox. etl gets BATCH, a baseline sized to the nightly p95, maybe autoscale for backfills. bi gets INTERACTIVE, a baseline sized to dashboard concurrency, no notebooks. sandbox gets a hard autoscale max that is allowed to queue and allowed to fail when someone SELECTs the heap. Autoscale max is the guardrail humans actually feel.

-- Sketch: create in the admin project, assign in the workload projects
CREATE RESERVATION `admin.region-us.etl`
OPTIONS (slot_capacity = 400, ignore_idle_slots = FALSE);

CREATE RESERVATION `admin.region-us.bi`
OPTIONS (slot_capacity = 200);

CREATE RESERVATION `admin.region-us.sandbox`
OPTIONS (slot_capacity = 50, autoscale_max_slots = 200);

-- Assign dbt SA → etl, Looker SA → bi, users → sandbox
-- (use the Reservations UI or bq mk --reservation_assignment)

Assignments are by project, user, or job. Put the dbt service account on etl. Put the Looker service account on bi. Put human users on sandbox, or on bi only if you have a small team and a culture that will not paste 4 TB scans. Connected Sheets often runs as the user; it will follow the human assignment. That is a reason to point Sheets at marts, not a reason to put humans on the BI pool.

Buying more slots on the shared reservation makes the graph look healthier and the invoice worse. It is the correct move only after you have split workloads and still sit on the ceiling of the SLA pool. SlashPeak's underutilized-reservation finding is the opposite smell: a baseline you already overbought. Do not add capacity while that finding exists on the same reservation. You would be paying for idle and for contention at the same time, which is a special kind of nonsense.

Document the split like an SLA. etl may be 30 minutes late. bi may not. sandbox has no promise. If you cannot write those sentences, you are not ready to size the pools, and you are not ready to buy.

BATCH jobs yield to INTERACTIVE. That is the feature. A 2-hour MERGE set to BATCH will wait while the dashboard runs, then take leftover slots. If everything is BATCH, you have only a queue, no SLA. If everything is INTERACTIVE, you have only a fight. The mix is the design.

dbt defaulting to INTERACTIVE is a common accident. The run finishes faster in a quiet night and steals the morning when someone kicks a model at 9:30. Set the BigQuery job priority in the profile or the Airflow operator to BATCH for scheduled runs, and keep INTERACTIVE for the developer who is iterating on a tiny dev dataset on the sandbox reservation.

BigQueryInsertJobOperator(
    task_id="events_incremental",
    configuration={
        "query": {
            "query": sql,
            "useLegacySql": False,
            "priority": "BATCH",
        }
    },
    location="US",
)

BATCH does not reduce bytes billed on-demand. A 4 TB MERGE costs 4 TB whether it waits or starts now. If you are on-demand, BATCH is about not overlapping the CFO's explore with your backfill; the invoice still wants the MERGE scoped. Do not tell finance you saved money by setting priority if the meter is bytes. Tell them you saved the dashboard. Then go fix the MERGE.

On capacity, BATCH can reduce the baseline you need, because you no longer size for ETL and BI peaks at the same hour. That is real list-price money, and it shows up on reservation underutilization after you split, not as a peak-hour line. The dollar finding, if any, is the reservation you can downsize after the split proves out.

Queueing is the feedback. If sandbox BATCH jobs sit in PENDING for hours, the autoscale max is too low or the queries are too wide. If bi INTERACTIVE jobs sit in PENDING, humans leaked onto bi or etl was not actually moved. The Query queuing chart on the Usage page shows the PENDING share. Use it as a health check after the split, not as a reason to skip the split.

On-demand billing does not care whether a job ran at 10am or 10pm. Bytes are bytes. Printing estimated monthly savings for peak-hour ad-hoc would be a lie on that meter. The honest statement is: these human INTERACTIVE jobs are a large share of peak-window cost and they collide with work that has an SLA.

Capacity billing cares about slots you purchased, not about when a particular query ran, except insofar as timing changes how many slots you must buy. Moving notebooks to 7pm might let you lower baseline. It might not, if ETL already fills the night. SlashPeak will not pretend to know which future reservation graph you will choose. It shows the collision.

That is why SLOT_CONTENTION sits in the set of findings excluded from total estimated monthly waste, and why queuing and peak-hour spend get no dollar line. Contention is still in the backlog and still high severity when the share is ugly; it just does not inflate the hero number on the dashboard with a made-up line item. The dollar findings you might see alongside are reservation underutilization (you already bought too many slots) and the scan-waste rules (those bytes should not have been read); fix bytes first when both exist. A notebook that scans 4 TB at 10am is a full-table-scan ticket and a peak-hour problem: the first one prints dollars, the second tells you why the reservation melted while you were reading the first.

If a vendor shows you a big savings number for move queries off peak, ask which meter they used. If they used on-demand list price times bytes, they priced a time-shift as a byte-shift. SlashPeak refuses that conversion on purpose.

Scheduled dbt should be a service account, BATCH, etl reservation. Developer dbt should be a slim dev dataset, sandbox reservation, whatever priority keeps them moving without touching prod slots. The failure mode is a human running dbt against prod in the console at noon because the Cloud job was red. That run is INTERACTIVE, human, peak, and often a full refresh. It hits every one of these problems.

-- JOBS.user_email for scheduled dbt should look like:
-- [email protected]
-- statement_type QUERY, priority BATCH, job_source dbt
--
-- If you see:
-- [email protected] + INTERACTIVE + dbt comment header
-- that run is peak-hour ad-hoc running as a transform job.

Notebooks are INTERACTIVE by default in most BigQuery clients. Teach TABLESAMPLE, require partition filters, and a 10 GB personal quota if you can. A notebook that needs the 4 TB table needs a mart or a BATCH job, not a cell that ran while the owner went to lunch. Colab and Jupyter do not know your reservation SLA.

Connected Sheets is a human INTERACTIVE refresh that looks like BI; it belongs in the next post as a scan problem and in this post as a peak-hour problem, since a spreadsheet opened in a meeting can fire the same 1.2 TB job as last time, during the meeting. Point it at a scheduled extract, and do not give Sheets the BI reservation until it reads a mart. Looker is INTERACTIVE on purpose, and that is the SLA; the fix is not BATCH for explores but a mart so each INTERACTIVE job is 2 GB, on a reservation that is not shared with dbt. If Looker shows up in peak-hour spend as a human, check whether explores run as the viewer: service-account Looker drops out of ad-hoc and stays in contention if the scans are still huge. Both signals are useful, but they are not the same.

Make the default boring. New user: sandbox assignment. New service account: named workload assignment, never the shared leftover pool. New scheduled query: BATCH. INTERACTIVE should require a reason, like production code requires a reason to skip tests. SlashPeak will keep counting what actually ran, not what the wiki hoped.

Day one: list peak-window INTERACTIVE human jobs by bytes and by user. Confirm timezone. Do not change reservations yet. You are building the invite list and the before graph.

Day two: create sandbox with a low autoscale max. Assign a volunteer cohort of the top humans. Watch whether they can work and whether bi PENDING drops. If they cannot work, the max is too low or their queries need marts, not more slots.

Day three: set scheduled dbt and Airflow to BATCH and assign the dbt service account to etl. Confirm job_source and principal type flipped. A remaining human dbt run is a training issue, not a config miss, if the service account jobs look right.

Day four: point the worst Connected Sheets and the worst Looker explore at a mart if those are also SELECT * or full scans. Isolation without scan fixes just moves a 4 TB job to another pool. The pool will melt too, only with a clearer name.

Day five: re-run the day-one list and re-scan in SlashPeak. Peak-hour share should fall. Contention and the PENDING chart should tell you whether bi is healthy. If underutilization appears on the old shared reservation, downsize. If peak-hour is still high, you isolated the wrong principals: open the attribution on the query findings and repeat with the people who are still on the graph. The week is a loop, not a ceremony.