Claude Code with BigQuery: SQL at Scale Without the Surprise Bills
Why BigQuery without CLAUDE.md ships queries that cost more than the engineer
BigQuery's pricing model is the feature that makes it possible to run analytics on petabytes from a Jupyter notebook, and it is the feature that makes a single careless query cost more than a week of engineering time. The on-demand pricing is per terabyte scanned. A query that scans 10 TB and returns one row costs the same as a query that scans 10 TB and returns ten million rows. The cost is in the scan, not the result. Claude Code does not know this by default. It generates SELECT * FROM project.dataset.events and Cloud Logging records a 4 TB scan that the finance team noticed before you did.
The most common Claude defaults that drive BigQuery bills up: SELECT * on tables with hundreds of columns and years of history, queries without a WHERE clause on the partition column (no pruning, full table scan), tables created without partitioning or clustering, ad-hoc queries against production datasets without a dry run, string concatenation of user input into SQL (cost and security), and scheduled queries running daily that nobody owns. On top of those, Claude makes a consistent client-level mistake: it writes client = bigquery.Client() with no project argument, which picks up whatever the local gcloud config points at, then writes to the wrong project in production.
This guide covers the CLAUDE.md configuration that locks Claude Code into BigQuery's cost-aware model: partition columns declared on every CREATE TABLE, queries that filter on the partition before any other condition, dry runs before any aggregate query, parameterised queries via the SDK, and scheduled queries owned by a named service account. For the Python ETL pipelines that feed BigQuery, Claude Code with Python covers the project layout that pairs cleanly with BigQuery's Python SDK.
The BigQuery CLAUDE.md template
The CLAUDE.md at your project root is read at the start of every Claude Code session. For a BigQuery integration it needs to declare: the SDK version, the project structure, the dataset organisation, the partition and clustering policy, the query patterns that protect against runaway costs, the dry-run discipline, the scheduled query ownership, and the hard rules that block the mistakes Claude makes most often.
# BigQuery rules
## Stack
- google-cloud-bigquery ^3.x (Python) or @google-cloud/bigquery ^7.x (Node.js)
- Authentication via Application Default Credentials (ADC) or service account
- GOOGLE_CLOUD_PROJECT env var pins the project for ALL clients
- DATASET_PROD and DATASET_STAGING env vars pin dataset names per environment
## Project structure
- sql/ , versioned .sql files for ad-hoc + scheduled queries
- sql/scheduled/ , queries that run on a schedule, each with owner comment
- sql/views/ , view definitions (CREATE OR REPLACE VIEW)
- sql/tables/ , table DDL (CREATE TABLE with partition + cluster)
- pipelines/ , Python scripts that orchestrate queries
- tests/ , dry-run tests for query cost guardrails
## Client initialisation (MANDATORY)
- ALWAYS pass project= explicitly to bigquery.Client()
- NEVER rely on ADC default project (it picks up local gcloud config)
- Singleton client per process, NOT per query
## Table creation (MANDATORY for every CREATE TABLE)
- Partition column: DATE or TIMESTAMP, named consistently ('event_date' or 'created_at')
- Partition type: time-unit (DAY default, HOUR for high-volume)
- Cluster columns: 1-4 columns that appear in WHERE filters
- Require partition filter: ON for tables larger than 1 GB
ALTER TABLE ... SET OPTIONS (require_partition_filter = true)
## Query patterns (MANDATORY)
- ALWAYS select named columns, NEVER SELECT *
- ALWAYS filter on the partition column FIRST in the WHERE clause
- ALWAYS use parameterised queries via SDK, NEVER string concat user input
- LIMIT for exploration queries, but LIMIT does NOT reduce scan cost
- Aggregate with WHERE first, then GROUP BY (filter pushdown matters)
## Dry runs (MANDATORY before expensive operations)
- ALWAYS run client.query(sql, job_config=QueryJobConfig(dry_run=True))
before any query against a partitioned table without a partition filter
- ALWAYS check job.total_bytes_processed and decide whether to proceed
- Bytes-to-cost approximation: 1 TB on-demand ~= $6.25 (verify in your billing region)
## Scheduled queries
- Owner comment at top of every scheduled query SQL file:
-- Owner: team@yourorg.com
-- Purpose: ...
-- Cost budget: <N> GB/run, <N> TB/month
- Service account: bigquery-scheduler@<project>.iam.gserviceaccount.com
- NEVER schedule queries from a personal account
## Hard rules
- NEVER SELECT * from a production table without an explicit comment justifying it
- NEVER omit the partition filter on a table with require_partition_filter
- NEVER run an untested query that scans more than 100 GB without a dry run
- NEVER concatenate user input into SQL strings (cost + injection)
- NEVER schedule a query without an owner comment and cost budget
- NEVER use a deprecated dataset region (multi-region 'US' is fine, single-region pinning preferred)
- ALWAYS set project= on bigquery.Client(project=os.environ['GOOGLE_CLOUD_PROJECT'])
Four rules here prevent the majority of cost incidents Claude generates without them.
The SELECT * rule is the single biggest cost lever. BigQuery is a columnar warehouse. A SELECT * reads every column. A SELECT id, created_at, user_id reads three columns. On a table with 200 columns the latter is 60-80x cheaper. Claude defaults to SELECT * because it is the simplest SQL and the example output is small. In a notebook against a 100 GB table that becomes a 100 GB scan. The CLAUDE.md rule "always select named columns" forces Claude to pick the columns the application actually needs.
The partition filter rule prevents the most expensive class of mistake. A partitioned table scanned without a partition filter reads every partition. A query like WHERE user_id = 'u_123' on a billion-row events table that is partitioned by event_date reads three years of data. The same query with WHERE event_date >= '2026-05-01' AND user_id = 'u_123' reads one month. The cost ratio is 36x. The rule "always filter on partition first" makes the partition filter a habit, not an afterthought.
The dry-run rule is the cost insurance. The BigQuery dry-run mode returns the byte estimate without running the query. Adding dry_run=True to the job config takes milliseconds and tells you exactly how many bytes the query will scan. A 1 TB scan costs $6.25 on-demand (verify in your billing region). A 100 TB scan costs $625. The dry run is what makes the difference visible before the query runs.
The parameterised query rule prevents both injection and the per-character cost of building SQL strings. Claude defaults to f-string SQL f"SELECT * FROM events WHERE user_id = '{user_id}'" because that is how Python SQL is written in most tutorials. BigQuery's parameterised query API takes @user_id placeholders and QueryParameter objects that the SDK binds safely.
Install and client setup
Install the Python SDK:
pip install google-cloud-bigquery
Set the project and authenticate via Application Default Credentials:
gcloud auth application-default login
export GOOGLE_CLOUD_PROJECT=your-project-id
Or use a service account in production:
export GOOGLE_APPLICATION_CREDENTIALS=/path/to/service-account.json
export GOOGLE_CLOUD_PROJECT=your-project-id
Create the singleton client:
# src/bigquery_client.py
import os
from google.cloud import bigquery
if 'GOOGLE_CLOUD_PROJECT' not in os.environ:
raise RuntimeError('GOOGLE_CLOUD_PROJECT must be set')
client = bigquery.Client(project=os.environ['GOOGLE_CLOUD_PROJECT'])
The explicit project= argument is what the CLAUDE.md rule enforces. Without it, the client falls back to the local gcloud config, which is fine in development but disastrous in CI where the default project might be a sandbox.
Creating tables with partitioning and clustering
A table created without partitioning works fine until it grows past a few gigabytes. After that, every query scans the entire table because there is no pruning mechanism. Partitioning physically segments the data by a column value, usually a date. Clustering sorts data within each partition by additional columns, which speeds up queries that filter on those columns.
A correct CREATE TABLE for an events table:
-- sql/tables/events.sql
CREATE TABLE IF NOT EXISTS `project.analytics.events` (
event_id STRING NOT NULL,
user_id STRING NOT NULL,
event_type STRING NOT NULL,
event_date DATE NOT NULL,
created_at TIMESTAMP NOT NULL,
properties JSON,
session_id STRING,
source STRING
)
PARTITION BY event_date
CLUSTER BY user_id, event_type
OPTIONS (
description = 'User event stream, partitioned by event_date for cost control',
require_partition_filter = true,
partition_expiration_days = 730
);
Four design decisions in this DDL. PARTITION BY event_date makes the table prunable by date. CLUSTER BY user_id, event_type orders data within each daily partition so queries that filter on user or event type read fewer storage blocks. require_partition_filter = true makes the partition filter mandatory, the query fails if you forget it. partition_expiration_days = 730 automatically deletes partitions older than two years, which controls storage cost over time.
Add a CREATE TABLE template to CLAUDE.md:
## CREATE TABLE template (CONCRETE)
CREATE TABLE IF NOT EXISTS `<project>.<dataset>.<table>` (
<columns>
)
PARTITION BY <date_or_timestamp_column>
CLUSTER BY <up to 4 filter columns>
OPTIONS (
description = '<purpose>',
require_partition_filter = true,
partition_expiration_days = <retention>
);
ALWAYS: partition column, cluster columns, require_partition_filter, partition_expiration_days
NEVER: no partition, no cluster, no description, unbounded retention on event-stream tables
Writing cost-aware queries
The same data with two different queries can differ in cost by 100x. The difference is in the partition filter, the column list, and the aggregation order.
A query Claude generates by default:
SELECT *
FROM `project.analytics.events`
WHERE user_id = 'u_123';
This scans every partition for every column. On a 100 GB table this is a 100 GB scan.
The correct version:
SELECT event_id, event_type, created_at, properties
FROM `project.analytics.events`
WHERE event_date >= DATE_SUB(CURRENT_DATE(), INTERVAL 30 DAY)
AND user_id = 'u_123'
ORDER BY created_at DESC
LIMIT 100;
Five columns instead of all columns. Last 30 days instead of all history. The user_id filter benefits from clustering. The result is at most 100 rows. On the same 100 GB table this query scans roughly 800 MB. The cost ratio is 125x.
Add a query rewrite checklist to CLAUDE.md:
## Query rewrite checklist (apply to EVERY query against a > 1 GB table)
1. Replace SELECT * with the columns the application actually needs
2. Add WHERE filter on the partition column FIRST (date range, not all history)
3. Add filters on cluster columns next
4. Add other filters last
5. Use LIMIT for exploration but remember LIMIT does NOT reduce scan
6. Aggregate with WHERE first, GROUP BY second
7. Run dry-run BEFORE running the query if it might exceed 10 GB
Parameterised queries from Python
The Python SDK supports parameterised queries via QueryParameter objects. Use them for any value that comes from outside the SQL file, user input, environment variables, function arguments, dates computed at runtime.
# pipelines/user_events_report.py
from datetime import date, timedelta
from google.cloud import bigquery
from src.bigquery_client import client
def fetch_user_events(user_id: str, days: int = 30) -> list[dict]:
sql = """
SELECT event_id, event_type, created_at, properties
FROM `project.analytics.events`
WHERE event_date >= @start_date
AND user_id = @user_id
ORDER BY created_at DESC
LIMIT 1000
"""
start_date = date.today() - timedelta(days=days)
job_config = bigquery.QueryJobConfig(
query_parameters=[
bigquery.ScalarQueryParameter('user_id', 'STRING', user_id),
bigquery.ScalarQueryParameter('start_date', 'DATE', start_date),
],
)
query_job = client.query(sql, job_config=job_config)
return [dict(row) for row in query_job.result()]
The @user_id and @start_date placeholders are bound at query time. The SDK escapes the values, BigQuery parses them with type awareness, and the cost is identical to the equivalent literal query.
Add a parameterised query rule to CLAUDE.md:
## Parameterised queries (MANDATORY for any non-literal value)
- Use @name placeholders in the SQL string
- Bind with bigquery.ScalarQueryParameter or ArrayQueryParameter
- Pass via job_config = QueryJobConfig(query_parameters=[...])
- NEVER f-string user input into SQL
- NEVER concatenate dates as strings, use ScalarQueryParameter('day', 'DATE', date_obj)
Dry runs in code
A dry run returns the bytes-scanned estimate without executing the query. Use it as a guardrail before running any query that might scan more than a few gigabytes.
def estimate_query_cost(sql: str, job_config: bigquery.QueryJobConfig | None = None) -> int:
dry_config = bigquery.QueryJobConfig(dry_run=True, use_query_cache=False)
if job_config and job_config.query_parameters:
dry_config.query_parameters = job_config.query_parameters
job = client.query(sql, job_config=dry_config)
return job.total_bytes_processed
def run_with_budget(sql: str, max_gb: float, job_config=None):
bytes_to_scan = estimate_query_cost(sql, job_config)
gb = bytes_to_scan / (1024 ** 3)
if gb > max_gb:
raise RuntimeError(
f'Query would scan {gb:.2f} GB, exceeds budget of {max_gb:.2f} GB'
)
return client.query(sql, job_config=job_config).result()
Wrapping client.query() with run_with_budget() gives every query a cost budget. If the estimate exceeds the budget, the function raises before the query runs. This is the right pattern for ad-hoc analyses where one wrong WHERE clause can cost hundreds of dollars.
Add the dry-run wrapper pattern to CLAUDE.md:
## Dry-run wrapper (CONCRETE)
For ad-hoc and ETL queries, wrap client.query() with a cost guardrail:
run_with_budget(sql, max_gb=10.0, job_config=...)
The wrapper:
1. Calls client.query with dry_run=True, gets total_bytes_processed
2. Compares against max_gb argument
3. Raises if estimate exceeds budget
4. Runs the real query if within budget
NEVER skip the wrapper for queries against tables larger than 100 GB.
Scheduled queries with ownership
Scheduled queries are the cron jobs of BigQuery. They run on a schedule, write results to a destination table, and accrue cost forever unless someone notices. The mistake is scheduling a query under a personal account, then leaving the company. The query runs, the bill grows, and nobody knows who owns it.
The right pattern: scheduled queries run under a dedicated service account, the SQL file includes an owner comment, and the cost budget is documented.
-- sql/scheduled/daily_user_metrics.sql
-- Owner: data-platform@yourorg.com
-- Purpose: Daily roll-up of user activity for the metrics dashboard
-- Schedule: 03:00 UTC daily
-- Cost budget: 50 GB per run, 1.5 TB per month
-- Destination: project.analytics.daily_user_metrics
-- Write disposition: WRITE_TRUNCATE on the partition for the run date
INSERT INTO `project.analytics.daily_user_metrics`
SELECT
CURRENT_DATE() AS report_date,
user_id,
COUNT(*) AS event_count,
COUNT(DISTINCT session_id) AS session_count,
MIN(created_at) AS first_event_at,
MAX(created_at) AS last_event_at
FROM `project.analytics.events`
WHERE event_date = DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY)
GROUP BY user_id;
Create the scheduled query via the BigQuery Data Transfer Service, specifying the service account:
bq mk \
--transfer_config \
--display_name='Daily user metrics' \
--target_dataset=analytics \
--params='{"query":"...","destination_table_name_template":"daily_user_metrics","write_disposition":"WRITE_APPEND"}' \
--schedule='every 24 hours' \
--service_account_name=bigquery-scheduler@project.iam.gserviceaccount.com \
--data_source=scheduled_query
Add a scheduled query rule to CLAUDE.md:
## Scheduled queries
- Every scheduled query SQL file starts with owner / purpose / schedule / cost budget comment
- ALWAYS use a service account, NEVER a personal account
- Destination table MUST be partitioned (write to the partition for the run date)
- Write disposition: WRITE_TRUNCATE for idempotent recompute, WRITE_APPEND for incremental
- Review the list quarterly: bq ls --transfer_config
Common Claude Code mistakes with BigQuery
Six patterns Claude generates incorrectly without CLAUDE.md constraints.
1. SELECT * against production tables
Claude generates: SELECT * FROM project.analytics.events LIMIT 10 to inspect a table.
Correct pattern: use bq show or query the INFORMATION_SCHEMA.COLUMNS view. If you need sample rows, SELECT col1, col2, col3 FROM t WHERE partition_col = CURRENT_DATE() LIMIT 10.
2. Missing partition filter
Claude generates: SELECT user_id, COUNT(*) FROM events WHERE event_type = 'purchase' GROUP BY user_id.
Correct pattern: add AND event_date >= DATE_SUB(CURRENT_DATE(), INTERVAL 90 DAY) as the first filter.
3. f-string SQL with user input
Claude generates: f"SELECT * FROM users WHERE email = '{email}'".
Correct pattern: WHERE email = @email with a ScalarQueryParameter.
4. Client without project argument
Claude generates: client = bigquery.Client().
Correct pattern: client = bigquery.Client(project=os.environ['GOOGLE_CLOUD_PROJECT']).
5. CREATE TABLE without partition
Claude generates: CREATE TABLE events (event_id STRING, ...) with no partition clause.
Correct pattern: PARTITION BY event_date CLUSTER BY user_id, event_type OPTIONS (require_partition_filter = true).
6. Scheduled query under personal account
Claude generates: schedule via Cloud Console with the current user, no service account.
Correct pattern: create the scheduled query via bq mk --transfer_config with --service_account_name=....
Add this list to CLAUDE.md with the corrected form for each.
Permission hooks for BigQuery CLI
A BigQuery project accumulates CLI invocations: dataset creation, table mutation, scheduled query management, query execution. Some are read-only. Some change production data. Permission hooks gate the destructive ones.
In .claude/settings.local.json:
{
"permissions": {
"allow": [
"Bash(bq ls*)",
"Bash(bq show*)",
"Bash(bq head*)",
"Bash(bq query --dry_run*)"
],
"deny": [
"Bash(bq rm*)",
"Bash(bq mk --transfer_config*)",
"Bash(bq query --destination_table*)",
"Bash(bq cp*)"
]
}
}
Listing datasets, showing schemas, previewing rows, and running dry-run queries are safe and cheap. Removing tables, creating scheduled queries, writing to destination tables, or copying datasets need explicit confirmation. The deny list forces Claude to surface those operations as prompts.
Building BigQuery code that does not surprise the finance team
The BigQuery CLAUDE.md in this guide produces SQL and Python where every CREATE TABLE has partition and cluster clauses, every query filters on the partition column first, queries use parameter binding instead of string concatenation, the client is pinned to the project via environment variable, dry runs guard against runaway costs, and scheduled queries have named owners and cost budgets.
The underlying principle is the same as any cloud cost surface with Claude Code. BigQuery without a CLAUDE.md produces code that runs correctly and costs ten times what it should: SELECT * queries that scan terabytes for one column of output, missing partition filters that force full-table scans, scheduled queries that nobody owns, and ad-hoc analyses that the engineer wrote in a notebook and forgot to budget.
For the data engineering orchestration layer that schedules and monitors BigQuery jobs, Claude Code with Python covers the project layout that pairs cleanly with BigQuery's Python SDK. Claudify includes a BigQuery-specific CLAUDE.md template with the CREATE TABLE pattern, query rewrite checklist, parameterised query examples, dry-run wrapper, scheduled query ownership rules, and all six common-mistake rules pre-configured.
Get Claudify. Ship BigQuery code that respects the bill.
More like this
Ready to upgrade your Claude Code setup?
Get Claudify
