bigquery

BigQuery

Serverless, highly scalable data warehouse. Bills per bytes processed — always dry-run before executing queries on large datasets.

Safety Rule — Dry-Run First

# Estimate bytes before executing — always do this on unknown datasets
bq query --dry_run --use_legacy_sql=false 'SELECT * FROM dataset.table'
# Output: "Query successfully validated. Assuming the tables are not modified,
# running this query will process X bytes."

Core Patterns

Create a dataset

bq mk --dataset \
  --location=REGION \
  --description="Description" \
  PROJECT_ID:DATASET_NAME

Run a query (with cost confirmation)

# 1. Dry-run first (see above)
# 2. Execute only after confirming cost
bq query --use_legacy_sql=false --location=REGION \
  'SELECT field FROM `project.dataset.table` LIMIT 100'

Grant dataset access (least-privilege)

bq show --format=prettyjson PROJECT_ID:DATASET > /tmp/ds.json
# Edit roles in /tmp/ds.json, then:
bq update --source /tmp/ds.json PROJECT_ID:DATASET

Create partitioned table (cost control)

CREATE TABLE dataset.table (
  event_date DATE,
  user_id STRING
)
PARTITION BY event_date
OPTIONS (partition_expiration_days = 365);

Cost Controls

• Partition tables by date — queries on a partition scan only that partition
• Cluster tables by high-cardinality filter columns
• Set per-project quotas: IAM → Quotas → BigQuery — Query usage per day
• Use LIMIT in development; avoid SELECT * on multi-TB tables

References

• BigQuery Introduction
• BigQuery Access Control
• BigQuery Pricing
• Performance Best Practices