BigQuery is one of the most powerful tools in GCP and one of the easiest ways to accidentally generate a very large bill
The power is real. You can query terabytes of data in seconds. The cost implication of that power is also real when queries aren't written carefully
BigQuery has two pricing models. On-demand charges per TB of data processed. Capacity pricing charges for reserved slots regardless of queries run
For most teams starting out, on-demand is what they're using. And on-demand pricing punishes full table scans
A query that selects every column from a 500GB table processes 500GB of data and charges you accordingly. A query that selects only the three columns you actually need from a partitioned table that filters to the relevant date range might process 2GB of data
Same result. Very different cost
This is the BigQuery optimization that matters most: query efficiency
Use column selection instead of SELECT *. Always. The cost difference on large tables is not marginal
Partition your tables and use partition filters in your queries. BigQuery will only scan the relevant partitions. A date-partitioned table queried with a date filter processes only the data in those dates
Cluster your tables on the columns most frequently used in WHERE clauses and JOINs. Clustering reduces the data scanned for filtered queries
The second optimization: control who can run what
Uncontrolled BigQuery access means analysts running expensive exploratory queries against production data at full scale. Query costs that belong to individual analyses ending up on the company bill with no visibility into what generated them
Column-level access controls, project-level quotas on bytes processed, and requiring query review for tables above a certain size - these are organizational controls but they have direct cost implications
BigQuery's built-in INFORMATION_SCHEMA views let you see exactly which queries are processing the most data and which users are generating the most cost
Look at that data. It will tell you where to focus
Top comments (2)
Good list. Three guards that turn the "project-level quotas" point into something each query enforces on its own:
bq query --dry_run --use_legacy_sql=false '...'prints how many bytes a query would process before it runs, at no cost. The console shows the same estimate in the top right of the editor.--maximum_bytes_billed=10000000000(10 GB) on a query makes BigQuery refuse anything that would bill more instead of running it. A forgottenSELECT *fails fast rather than showing up on the invoice.ALTER TABLE dataset.events SET OPTIONS (require_partition_filter = TRUE);makes a query without a partition filter an error on that table, so the date filter you describe is no longer optional.For the review step,
region-us.INFORMATION_SCHEMA.JOBShastotal_bytes_billedanduser_emailper job, which makes a weekly "top 20 queries by bytes billed" report a single SELECT.Dry run for the bytes estimate plus maximum_bytes_billed as a hard refusal is the right pairing, one tells you before you run it and the other stops you if you ignore the warning anyway, require_partition_filter closes the gap where someone skips both
Does INFORMATION_SCHEMA.JOBS catch scheduled queries and dashboards the same way it catches ad-hoc ones, or do service accounts running on a schedule tend to hide in a different place?