BigQuery Cost & Slot Optimization Prompt
Cut BigQuery spend by reasoning from INFORMATION_SCHEMA job history — bytes scanned, slot contention, and partition/cluster gaps — instead of blindly buying reservations.
- Target user
- Data platform engineers, analytics leads, and FinOps owners of a BigQuery bill
- Difficulty
- Intermediate
- Tools
- Claude, ChatGPT, Cursor
The prompt
You are a senior data platform engineer who has cut BigQuery bills without slowing down analysts. You reason from the actual job history in INFORMATION_SCHEMA — bytes scanned, slot-ms consumed, and query shapes — never from a generic "just buy a reservation" recommendation. You know the two pricing models (on-demand bytes-scanned vs. capacity slots) and you decide between them from the data, not by default. I will provide: - Job history: [RESULT OF A `region-REGION.INFORMATION_SCHEMA.JOBS_BY_PROJECT` QUERY — total_bytes_billed, total_slot_ms, query text or job_type, by user/day] - The current pricing model: [ON-DEMAND or EDITIONS/CAPACITY with N baseline slots] - The worst offenders: [TOP QUERIES BY BYTES OR SLOT-MS] and the relevant table schemas (partitioning/clustering, row counts) - Any constraints: SLAs on dashboard freshness, regions that must stay, teams that own the queries Your job: 1. **Locate the spend** — rank queries and users by bytes billed and by slot-ms. Separate the few queries that dominate cost from the long tail. Note whether cost is driven by scan volume (on-demand) or by sustained concurrency (slots). 2. **Pruning wins** — for each top query, identify whether a partition filter, clustering key, or column pruning (`SELECT` only needed columns) would cut bytes scanned, and estimate the reduction. Flag `SELECT *` and full-table scans on partitioned tables that omit the partition filter. 3. **Materialization** — call out repeated expensive aggregations that should become materialized views or scheduled summary tables, and which downstream queries would benefit. 4. **Pricing model fit** — based on the usage shape (spiky ad-hoc vs. steady high-concurrency), recommend whether on-demand or a capacity/Editions reservation is cheaper, and if reservations, size the baseline to steady usage with autoscaling for peaks — not to the peak itself. 5. **Sequence the actions** — order by savings-per-effort and reversibility: query rewrites and partition filters first (reversible, no lock-in), reservation changes last. Output: (a) a ranked savings table (action, est. bytes/slot reduction, risk, reversibility), (b) per-query rewrite suggestions, (c) partition/cluster recommendations with the DDL, (d) a pricing-model recommendation with sizing rationale, (e) an action sequence. Bias toward query and schema fixes before any pricing change. Do not recommend altering or repartitioning a production table without flagging the rebuild cost and asking me to confirm the table isn't feeding a live SLA.
Why this prompt works
BigQuery cost advice is usually a reflex — “buy a reservation” or “add clustering” — offered without ever looking at what the queries actually do. This prompt forces the analysis to start from INFORMATION_SCHEMA.JOBS, which is the ground truth of bytes billed and slot-ms consumed per query and per user. That means the recommendations reflect this project’s real workload shape instead of a template, and it lets the model distinguish a scan-volume problem (fix with partition filters) from a concurrency problem (fix with slots) — two issues with completely different solutions.
The step ordering encodes how an experienced data engineer actually saves money. It finds the handful of queries that dominate the bill first, because optimizing the long tail is wasted effort. It then reaches for the cheapest, most reversible fixes — column pruning, partition filters, materialized views — before touching the pricing model, because those changes carry no lock-in and often eliminate the need for a bigger reservation entirely. Sizing reservations to baseline-with-autoscaling rather than to peak is the single most common capacity mistake, and the prompt bakes that judgment in.
The guardrails protect against the two ways this optimization backfires. Repartitioning a large table rewrites the whole thing and can break a live dashboard SLA, so the prompt requires confirmation before any table DDL. And reservations are a commitment, so the model is told to size conservatively and surface the lock-in. The result is a prioritized, reviewable plan where every irreversible action waits for the engineer’s sign-off.