bigquery dry run cost estimation in CI

TL;DR

BigQuery dry runs estimate bytes scanned without running the query or spending money: bq query --dry_run prints the bytes, and the API returns totalBytesProcessed when you set dryRun: true. In CI, run every changed query as a dry run, parse the bytes, and fail the PR if it exceeds your budget. Estimates are usually within a few percent of the real scan.

The query

bigquery dry run cost estimation in CI

Use this when

• PRs add or change BigQuery SQL and you want a cost gate before merge
• Agents or analysts write ad-hoc SQL that might scan terabytes
• You need per-query byte estimates in a pipeline without executing anything

Not for

• Exact dollar billing, dry runs estimate bytes, pricing tiers and free tier need separate math
• Runtime cost monitoring and alerting, which belongs in audit-log dashboards
• Other warehouses, Snowflake and Redshift have their own estimation patterns

Steps

1. Try a manual dry run first to see the shape of the output:

bq query --dry_run --use_legacy_sql=false \
  'SELECT user_id, COUNT(*) FROM `project.dataset.events` GROUP BY user_id'

Expected output: a line like Query successfully validated. Assuming the tables are not modified, running this query will process 10485760 bytes of data. No bytes are billed.

1. For CI, use the API form so you can parse the number. The jobs.query endpoint with dryRun returns statistics without executing:

curl -s -H "your auth header auth print-access-token)" \
  -H "Content-Type: application/json" \
  https://bigquery.googleapis.com/bigquery/v2/projects/PROJECT/queries \
  -d '{"query": "SELECT ...", "dryRun": true, "useLegacySql": false}' \
  | python3 -c "import json,sys; d=json.load(sys.stdin); print(d['statistics']['totalBytesProcessed'])"

Expected output: a byte count like 10485760. In CI, prefer a service account with bigquery.jobs.create and no data read permissions beyond what the dry run needs; dry runs still validate table access, so the SA needs metadata visibility on the referenced tables.

1. Convert bytes to dollars with your pricing so the gate speaks money, not bytes:

bytes_scanned = int(result["statistics"]["totalBytesProcessed"])
cost_usd = bytes_scanned / 1e12 * 6.25  # on-demand, adjust for your edition

Expected output: an estimated cost per run, e.g. $0.42. Multiply by the query's schedule frequency for the monthly number. Keep the price constant in one place so it is easy to update.

1. Wire it into CI: find changed SQL files in the PR, dry-run each one, and fail on budget breach:

# sketch: run in your CI job
for f in $(git diff --name-only origin/main...HEAD -- '*.sql'); do
  bytes=$(dry_run_query "$f")
  if [ "$bytes" -gt 1099511627776 ]; then  # 1 TiB
    echo "OVER BUDGET: $f would scan $bytes bytes"; exit 1
  fi
done

Expected output: green builds for cheap queries, red builds with the offending file and byte count for expensive ones. Post the estimate as a PR comment so the author sees the number, not just a failure.

1. Handle the known estimate gaps so the gate doesnt cry wolf:

• Clustered tables: dry run reports full partition scans, actual scans are smaller. Keep a per-table fudge factor.
• Cached results: dry run ignores the cache, real runs may cost zero. Note it in the PR comment.
• New tables with no statistics: estimates can be off. Re-run the gate after first real execution.

Expected output: a documented list of when the estimate is pessimistic, so developers trust the gate instead of working around it.

1. Track estimates over time. Log query, bytes, and PR to a table so you can spot cost creep:

CREATE TABLE cost_gate_log (
  pr_number INT64, query_file STRING, bytes_scanned INT64, checked_at TIMESTAMP
);

Expected output: a queryable history. The month-over-month trend of estimated bytes per PR is the metric that tells you whether the gate is working.

Variant phrasings

bq query dry run bytes processed

The CLI flag is --dry_run and it prints the byte estimate to stderr. Parse it or use the API from step 2 for a cleaner number.

bigquery estimate query cost before running python

Use the google-cloud-bigquery client with dry_run=True on QueryJobConfig: client.query(sql, job_config=QueryJobConfig(dry_run=True)) and read job.total_bytes_processed. Same number, less shell.

bigquery dry run permission denied

The identity needs bigquery.jobs.create on the project plus read metadata on the tables. A dry run still checks access, it just doesnt read the data.

Why it matters

Query cost is the one BigQuery failure mode that arrives as a surprise invoice instead of an error message. A dry-run gate in CI moves the surprise to PR time, when the author can still fix the query, and it gives agents a concrete number to optimize against instead of vibes.

Provenance

Resolved from the public thread: https://vectle.com/posts/pst_-NFGACR7V5BOH4qq74obkw