Google BigQuery skill for Claude Code

BigQuery work is done when large tables are partitioned and clustered on the columns queries filter by, every heavy query is dry-run and capped, the top cost drivers in INFORMATION_SCHEMA are known and addressed, and access is granted at dataset or view level to groups, not individuals.

Ground rules

• Dry-run before running anything that might scan a large table. On-demand billing is per byte scanned (US multi-region list price $6.25 per TiB after the first free TiB each month; check current regional pricing), with a 10 MB minimum per table referenced.
• Set maximum_bytes_billed on ad-hoc and scheduled queries, and custom daily quotas per project or user so one mistake cannot burn the month.
• LIMIT does not reduce bytes billed on unclustered tables. SELECT * reads every column. Preview data with bq head or the table preview, which are free.
• DROP TABLE, DELETE/TRUNCATE without a partition filter, bq rm -r on a dataset, and changing partition expiration on existing tables destroy data; require explicit confirmation. Time travel (up to 7 days) is the safety net; confirm the window first.
• Grant roles to groups/service accounts on datasets; avoid project-wide bigquery.admin/dataEditor.

Workflow

- [ ] 1. Find the cost and latency drivers (INFORMATION_SCHEMA.JOBS)
- [ ] 2. Fix table design: partitioning, clustering, expiration
- [ ] 3. Rewrite the worst queries; dry-run before and after
- [ ] 4. Choose pricing model and set guardrails (quotas, max bytes)
- [ ] 5. Incremental pipelines: MERGE, scheduled queries, materialized views
- [ ] 6. Access: dataset IAM, authorized views, row/column security
- [ ] 7. Verify bytes and slot time dropped; report

1. Find the drivers

-- Top 20 query patterns by bytes billed, last 30 days (adjust region qualifier)
SELECT
  user_email,
  query_info.query_hashes.normalized_literals AS query_hash,
  ANY_VALUE(LEFT(query, 200)) AS sample_query,
  COUNT(*) AS runs,
  ROUND(SUM(total_bytes_billed) / POW(1024, 4), 2) AS tib_billed,
  ROUND(SUM(total_slot_ms) / 1000 / 3600, 1) AS slot_hours
FROM `region-us`.INFORMATION_SCHEMA.JOBS_BY_PROJECT
WHERE creation_time >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 30 DAY)
  AND job_type = 'QUERY' AND state = 'DONE'
GROUP BY user_email, query_hash
ORDER BY tib_billed DESC
LIMIT 20;

More audit queries (storage by table, scheduled query costs, partition skew, slot contention): read references/cost-queries.md when doing a full cost review.

bq query --use_legacy_sql=false --dry_run 'SELECT ...'      # prints bytes that would be processed
bq show --format=prettyjson project:dataset.table | jq '{partitioning: .timePartitioning, clustering: .clustering, rows: .numRows, bytes: .numBytes}'

2. Table design

Table shape	Partition	Cluster
Events/logs with timestamp, queried by date range	PARTITION BY DATE(event_ts) (daily; hourly only for very high volume short retention)	Top 1-4 filter/join columns: customer_id, event_name
Dimension tables < 1-10 GB	None	Optional on lookup key
Snapshot tables by load date	_PARTITIONDATE ingestion time or explicit snapshot_date	Business key
Integer-keyed data without time	Integer range partitioning	Secondary filters

CREATE TABLE analytics.events
(
  event_ts TIMESTAMP NOT NULL,
  customer_id STRING,
  event_name STRING,
  properties JSON
)
PARTITION BY DATE(event_ts)
CLUSTER BY customer_id, event_name
OPTIONS (
  require_partition_filter = TRUE,
  partition_expiration_days = 400,
  description = 'Raw product events, one row per event'
);

Rules:

• Partitions should hold at least ~1 GB on average; tiny daily partitions add overhead without pruning benefit (cluster instead, or use monthly partitions). A table can have up to 10,000 partitions.
• Clustering column order matters: most frequently filtered first.
• Do not shard tables by date suffix (events_20260301); use partitioning. Wildcard queries over shards are slower and limited.
• Storage pricing: long-term rate applies automatically to partitions unmodified for 90 days. Consider physical (compressed) storage billing per dataset when compression ratio is high; time travel and fail-safe bytes are billed under physical billing.

3. Query optimization

Problem	Fix
Partition not pruned	Filter directly on the partition column with constants or parameters; avoid wrapping it in functions or filtering through a join/subquery result
SELECT * on wide tables	Select needed columns; SELECT * EXCEPT(...) still reads the rest
Repeated scans of the same base	Materialize a filtered intermediate table or use a materialized view
Large joins slow	Filter before joining; join on INT64 or clustered keys; put the largest table first and let BigQuery broadcast small ones
COUNT(DISTINCT) on huge sets	APPROX_COUNT_DISTINCT or HLL sketches when +-1% is acceptable
Window functions over everything	PARTITION BY a key in the window; QUALIFY to filter instead of subquery
Dedup latest row per key	QUALIFY ROW_NUMBER() OVER (PARTITION BY id ORDER BY updated_at DESC) = 1
Querying JSON strings	Use the JSON type or extract to typed columns at load
Needle-in-haystack lookups on text	Search indexes + SEARCH()

Read the execution graph (console Execution details or INFORMATION_SCHEMA.JOBS job_stages) for: stages with high “wait” (slot contention), skew (max vs avg worker time), and large shuffle bytes.

-- Parameterized, pruned, capped
DECLARE start_date DATE DEFAULT DATE_SUB(CURRENT_DATE(), INTERVAL 7 DAY);
SELECT event_name, COUNT(*) AS n
FROM analytics.events
WHERE DATE(event_ts) >= start_date          -- prunes partitions
  AND customer_id = 'c_123'                 -- uses clustering
GROUP BY event_name;

bq query --use_legacy_sql=false --maximum_bytes_billed=50000000000 < query.sql   # fail if > 50 GB

4. Pricing model and guardrails

Situation	Model
Spiky, modest usage (< a few hundred TiB/month scanned)	On-demand with max bytes and custom quotas
Steady heavy usage, many scheduled pipelines, predictable budget	Editions (Standard/Enterprise/Enterprise Plus) with a reservation, baseline + autoscaling slots
Mixed	Reservation for pipelines project; on-demand for ad-hoc project (assignments per project)

Compare using slot-hours from INFORMATION_SCHEMA (SUM(total_slot_ms)/3.6e6) multiplied by the edition slot-hour price versus on-demand TiB billed. Editions bill autoscaled slots in increments and with a minimum window, so measured slot-hours understate cost slightly; leave margin.

Guardrails:

• Custom quota “Query usage per day” at project and per-user level (Cloud console Quotas, BigQuery API).
• Budget alerts on the billing account with notifications.
• Default maximum_bytes_billed in BI tools, dbt profiles (maximum_bytes_billed), and scheduled queries.

5. Incremental pipelines

MERGE analytics.customers T
USING (
  SELECT * FROM staging.customers_changes
  WHERE _PARTITIONDATE = CURRENT_DATE()
  QUALIFY ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY updated_at DESC) = 1
) S
ON T.customer_id = S.customer_id
WHEN MATCHED AND S.updated_at > T.updated_at THEN UPDATE SET name = S.name, plan = S.plan, updated_at = S.updated_at
WHEN NOT MATCHED THEN INSERT (customer_id, name, plan, updated_at) VALUES (S.customer_id, S.name, S.plan, S.updated_at);

• MERGE into a partitioned target: add a partition filter on T in the ON clause when possible so only recent partitions are scanned.
• Scheduled queries (Data Transfer Service) for simple SQL jobs; use dbt (see dbt-models) or an orchestrator once there are dependencies between jobs.
• Materialized views for repeated aggregations on append-mostly tables; BigQuery rewrites matching queries to use them automatically.
• Loading: batch loads from GCS are free; streaming uses the Storage Write API (cheaper and exactly-once with committed streams) rather than legacy insertAll.

bq mk --transfer_config --data_source=scheduled_query --target_dataset=analytics \
  --display_name="daily customers merge" --schedule="every day 03:00" \
  --params='{"query":"<SQL here>"}' [email protected]

6. Access

Need	Mechanism
Analysts read a dataset	roles/bigquery.dataViewer on dataset + roles/bigquery.jobUser on the billing project
Share a subset without exposing base tables	Authorized view (or authorized dataset)
Per-row restriction (tenant, region)	Row-level access policy
Sensitive columns (PII)	Policy tags with column-level access control, data masking
Pipelines	Dedicated service account with dataEditor on target datasets only

CREATE ROW ACCESS POLICY eu_only ON analytics.orders
GRANT TO ('group:[email protected]')
FILTER USING (region = 'EU');

7. Verify

• Dry-run the rewritten query: bytes processed drop (record before/after).
• Re-run the JOBS audit after a week: top patterns’ TiB billed and slot-hours fell.
• INFORMATION_SCHEMA.PARTITIONS shows expected partition sizes; queries without partition filters now fail fast on tables with require_partition_filter.

Gotchas

• Partition pruning does not happen when the filter value comes from a subquery in many cases; compute it into a variable (DECLARE/SET) first.
• Clustering benefits shrink as new data arrives unsorted; automatic reclustering is free but not instant.
• CREATE OR REPLACE TABLE drops table options and policies not restated (row access policies, descriptions); prefer ALTER or restate everything.
• BI tools issue many small queries; each referencing a table is billed at least 10 MB. Use BI Engine or extracts for dashboards with heavy concurrency.
• Region matters: datasets in different locations cannot be joined; INFORMATION_SCHEMA queries need the right region-xx qualifier.
• Cross-project queries bill the project running the job, not the data owner.

Output

**Top drivers (30 d):** dashboard refresh query 41 TiB (no partition filter); dbt full refresh 18 TiB
**Changes:** events partitioned by DATE(event_ts), clustered customer_id,event_name, require_partition_filter;
  dashboard query rewritten to 7-day window; dbt model incremental (MERGE)
**Dry run:** 2.3 TiB -> 38 GiB per dashboard refresh
**Guardrails:** max bytes 100 GB on scheduled queries; per-user daily quota 2 TiB; budget alert 80%
**Access:** analysts via authorized views; PII columns policy-tagged