BigQuery Essentials

A serverless data warehouse

BigQuery stores data in a columnar format and runs SQL across it using thousands of workers you never manage. Data lives in datasets (with a location) containing tables and views. Pricing has two parts: storage and compute. Compute is either on-demand, charged by the bytes each query scans, or capacity-based, where you buy slots through BigQuery editions. Because storage is columnar, SELECT * scans every column and costs the most; select only the columns you need. Partitioning a table (usually by a date or timestamp column) and clustering it (sorting by up to four columns) let BigQuery skip data, cutting bytes scanned. Adding LIMIT does not reduce bytes scanned for an ordinary query. Use the dry run or the query editor's estimate to check bytes before running.

Scan only what you need

Columnar storage plus partition pruning means a well-written query touches a small slice of the table.

A partitioned, clustered table and a pruned query

The filter on event_date lets BigQuery read only the last seven partitions.

CREATE TABLE shop.events (
  event_date DATE,
  user_id    STRING,
  event_name STRING,
  amount     NUMERIC
)
PARTITION BY event_date
CLUSTER BY event_name, user_id
OPTIONS (require_partition_filter = TRUE);

SELECT event_name, COUNT(*) AS events, SUM(amount) AS revenue
FROM shop.events
WHERE event_date >= DATE_SUB(CURRENT_DATE(), INTERVAL 7 DAY)
  AND event_name IN ('purchase', 'refund')
GROUP BY event_name;

-- estimate first: bq query --use_legacy_sql=false --dry_run '<query>'

Guard against runaway queries

Set require_partition_filter on large tables, set a maximum bytes billed on queries or in tools, and use custom quotas per user or project. One accidental SELECT * on a multi-terabyte table can cost real money.

Quick check: Which change reduces bytes scanned by an on-demand BigQuery query on a large unpartitioned table?

• Adding LIMIT 10
• Ordering the results
• Selecting only the needed columns instead of SELECT *
• Running it twice