How do you optimise BigQuery query cost and performance?

Cost in on-demand BigQuery is driven by bytes scanned, so reduce what each query reads.

• Partition tables, usually by date, and add partition filters so only relevant partitions are scanned.
• Cluster on columns used in filters and joins; clustering prunes blocks within partitions.
• Select only the columns you need. BigQuery is columnar, so SELECT * is expensive.
• Use materialised views or scheduled queries to precompute expensive aggregations.
• Avoid correlated subqueries and cartesian joins, and use approximate functions when exact counts are unnecessary.
• Check bytes processed with a dry run before running.

SELECT DATE(ts) AS d, COUNT(*) AS c
FROM `proj.dataset.events`
WHERE ts >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 7 DAY)
GROUP BY d;

For steady heavy usage, consider flat-rate slot reservations and monitor slot utilisation in INFORMATION_SCHEMA.JOBS.