BigQuery Optimization skill for Codex

Done well means: large tables are partitioned and clustered on the columns queries filter by, queries read only the partitions and columns they need, every cost claim comes from a dry run or INFORMATION_SCHEMA.JOBS, and guardrails stop accidental full scans.

Before changing anything

1. Read AGENTS.md; find how SQL runs (dbt with type: bigquery, bq CLI, Python client, Dataform). Queries and dry runs need network and credentials; in a sandbox ask for approval or ask the user to run them.
2. Learn the billing model: on-demand (billed per TiB scanned) or capacity (Editions reservations, billed per slot-hour). The fix differs:

   Model	What costs money	Optimise for
   On-demand	Bytes billed per query	Fewer bytes: partitions, clustering, fewer columns
   Editions / reservations	Slot-hours (baseline + autoscale)	Less slot time: less shuffle, fewer repeated scans, right-sized baseline

3. Know the region (region-us, region-eu, …); INFORMATION_SCHEMA views are regional.
4. Altering or recreating tables, changing reservations, and deleting data need explicit user confirmation.

Measure before and after

bq query --use_legacy_sql=false --dry_run 'select ... from `proj.ds.events` where ...'
# prints the bytes the query would process; costs nothing

-- top cost drivers in the last 7 days (on-demand view)
select user_email, query, total_bytes_billed / pow(1024, 4) as tib_billed,
       total_slot_ms / 1000 / 3600 as slot_hours
from `region-us`.INFORMATION_SCHEMA.JOBS_BY_PROJECT
where creation_time >= timestamp_sub(current_timestamp(), interval 7 day)
  and job_type = 'QUERY' and statement_type != 'SCRIPT'
order by total_bytes_billed desc
limit 20;

More attribution queries (by query hash, by label, by table, reservation usage) are in references/information-schema-queries.md; read it when you need to explain a bill or find repeated expensive patterns.

Partitioning and clustering

create table `proj.analytics.events`
partition by date(event_ts)
cluster by tenant_id, event_name
options (
  require_partition_filter = true,
  partition_expiration_days = 400
) as
select * from `proj.raw.events`;

Decision	Rule
Partition column	The date or timestamp almost every query filters on; daily granularity by default, monthly or yearly when daily partitions would be tiny
Partition limit	10,000 partitions per table; do not partition by hour over years
Integer range partitioning	Only when queries filter on an integer range (customer bucket, ID ranges)
Clustering columns	Up to 4, ordered by how often they are filtered, high-to-medium cardinality (tenant, user, event type)
Small tables (under about 1 GB per partition)	Clustering alone often beats partitioning
Guardrail	require_partition_filter = true on large fact tables

Pruning only happens when the filter is on the partition column with constant expressions (or query parameters). These defeat it:

-- prunes
where event_ts >= timestamp('2025-09-01') and event_ts < timestamp('2025-09-02')
where date(event_ts) = '2025-09-01'             -- matches partition expression

-- often scans everything
where format_timestamp('%Y-%m', event_ts) = '2025-09'
where event_ts > (select max(loaded_at) from `proj.ops.watermarks`)   -- check the dry run

Run a dry run to confirm bytes processed dropped; if a subquery-derived filter does not prune, compute the value first (scripting variable or parameter) and pass it in.

Query rewrites

Pattern	Rewrite
select *	Name columns; BigQuery bills by columns read. LIMIT does not reduce bytes billed on non-clustered tables
Repeated scans of the same big table in one script	Materialize once into a temp table
count(distinct x) on huge data where exact is not needed	approx_count_distinct(x)
Join of two large tables then filter	Filter and aggregate each side first
Dedupe via self-join	qualify row_number() over (partition by id order by updated_at desc) = 1
Wildcard tables events_*	Filter _TABLE_SUFFIX with constants, or migrate to one partitioned table
Dashboard re-running the same aggregate	Materialized view or a scheduled summary table; BI Engine for interactive dashboards

Read the execution graph (console Execution details, or JOBS job_stages): stages with large “shuffle output” or “slot time” skewed between avg and max indicate data skew or exploding joins.

Guardrails

# per-query cap: the job fails instead of billing more than 50 GB
bq query --use_legacy_sql=false --maximum_bytes_billed=50000000000 'select ...'

from google.cloud import bigquery

job_config = bigquery.QueryJobConfig(maximum_bytes_billed=50 * 10**9, labels={"team": "growth"})
client.query(sql, job_config=job_config).result()

• Set custom quotas per user or project (query usage per day) in the Cloud console for on-demand projects.
• Label jobs (--label team:growth or job_config.labels) so cost can be attributed.
• Use require_partition_filter on big tables and partition_expiration_days for data you do not need forever.

Capacity pricing (Editions)

• Compare: last 30 days of on-demand TiB billed times the on-demand rate versus slot-hours used (total_slot_ms) times the Edition rate. Fetch current prices from the BigQuery pricing page; they differ by region and edition.
• Start with a small or zero baseline plus autoscaling max; raise the baseline only for steady load. Idle autoscaled slots still bill in increments, so short spiky workloads waste some.
• Separate reservations (or assignments) for ELT and BI so a heavy backfill cannot starve dashboards.

Storage

• Long-term storage (tables or partitions unmodified for 90 days) costs about half; avoid rewriting old partitions needlessly (full-table CREATE OR REPLACE resets it).
• Physical (compressed) storage billing can be cheaper for well-compressed datasets but bills time travel and fail-safe bytes; compare with INFORMATION_SCHEMA.TABLE_STORAGE before switching.

Gotchas

• Cached results (identical query, unchanged tables, within 24 h) are free; benchmarks must disable cache (--nouse_cache) to compare fairly.
• DML on partitioned tables should filter by partition; a MERGE without a partition predicate on the target scans the whole table.
• Streaming inserts land in a buffer that affects DML for a while; use the Storage Write API for new pipelines.
• Clustering is re-applied automatically in the background at no charge, but heavy small-batch writes leave recent data less well clustered for a while.
• Bytes estimates for clustered tables in dry runs are upper bounds; actual bytes billed can be lower.

Report template

**Scope:** project analytics-prod, on-demand, region US, last 30 days
**Top drivers:** looker dashboard "Daily KPIs" 41% of TiB billed (select * on events, no date filter)
**Changes:** events partitioned by date(event_ts), clustered by tenant_id, event_name; require_partition_filter on; KPI view rewritten with named columns
**Measured:** dry run 3.8 TiB -> 21 GiB per dashboard refresh
**Guardrails:** maximum_bytes_billed 100 GB on scheduled queries; job labels team:*
**Needs approval:** recreate events table (copy, validate counts, swap) during low traffic