TopolearnChecking bytes processed before running is the habit that keeps BigQuery affordable.
Every query is a job with an id, a location, statistics and a result stored in a temporary table for about 24 hours. Repeating an identical query against unchanged tables returns the cached result for free.
Ways to run a query:
- BigQuery Studio in the Cloud console. The editor validates as you type and shows "This query will process X GB when run".
- bq:
bq query --use_legacy_sql=false 'SELECT ...'. bq has long defaulted to legacy SQL, so pass the flag or set it in~/.bigqueryrc. - Client libraries, for example Python's
client.query(sql).result().
The dialect is GoogleSQL (formerly "standard SQL"). Legacy SQL is an older dialect with square-bracket table names; avoid it for new work.
Cost controls before you run:
- A dry run (
--dry_run, or the console's estimate) validates the query and reports bytes processed without running or billing it. --maximum_bytes_billed(or the console's query settings) makes a query fail instead of running if it would bill more than the cap.- Preview a table in the console or with
bq headinstead ofSELECT *. Previews are free.
Use query parameters (@name placeholders) from code instead of string concatenation; they prevent SQL injection and help caching.
Example
# Estimate cost without running (no charge)
bq query --use_legacy_sql=false --dry_run \
'SELECT word, word_count FROM `bigquery-public-data.samples.shakespeare`'
# Query successfully validated. Assuming the tables are not modified,
# running this query will process ... bytes of data.
# Run it, but refuse to bill more than 1 GB
bq query --use_legacy_sql=false --maximum_bytes_billed=1000000000 \
'SELECT corpus, SUM(word_count) AS words
FROM `bigquery-public-data.samples.shakespeare`
GROUP BY corpus ORDER BY words DESC LIMIT 3'
# Look at a few rows for free
bq head -n 5 bigquery-public-data:samples.shakespeare
# Parameterized query
bq query --use_legacy_sql=false --parameter=min:INT64:500 \
'SELECT word FROM `bigquery-public-data.samples.shakespeare` WHERE word_count > @min'Remember
- Dry run first: it shows bytes processed and costs nothing
- maximum_bytes_billed makes expensive queries fail instead of bill
- Use GoogleSQL, not legacy SQL; preview tables instead of SELECT *
Watch out for
- Running SELECT * ... LIMIT 10 to peek at a big table and paying to scan every column.