The BigQuery Command Cheat Sheet Every Data Engineer Should Bookmark

The commands and

bq

Table Design

SQL
-- Partitioned and clustered table
CREATE TABLE sales.orders (
    order_id    INT64,
    customer_id INT64,
    order_date  DATE,
    amount      NUMERIC
)
PARTITION BY order_date
CLUSTER BY customer_id;

-- Partition by ingestion time when no natural date column exists
CREATE TABLE sales.events (
    event_id STRING,
    payload  JSON
)
PARTITION BY _PARTITIONDATE;

-- BIGNUMERIC for precision beyond 38 digits
CREATE TABLE finance.high_precision (
    amount BIGNUMERIC(76, 38)
);

Loading and Exporting

Shell
# Load from GCS
bq load --source_format=PARQUET \
  sales.orders \
  gs://my-bucket/orders/*.parquet

# Load CSV with schema autodetect
bq load --autodetect --source_format=CSV \
  sales.orders \
  gs://my-bucket/orders.csv

# Export to GCS
bq extract --destination_format=PARQUET \
  sales.orders \
  gs://my-bucket/exports/orders_*.parquet

SQL
-- Export via query
EXPORT DATA OPTIONS (
  uri = 'gs://my-bucket/exports/orders_*.parquet',
  format = 'PARQUET'
) AS
SELECT * FROM sales.orders WHERE order_date >= '2026-01-01';

Cost and Performance Diagnostics

SQL
-- Dry run to check bytes scanned before running
-- (bq CLI): bq query --dry_run --use_legacy_sql=false 'SELECT ...'

-- Query bytes billed from job history
SELECT
  job_id, user_email, total_bytes_billed,
  total_slot_ms, creation_time
FROM `region-us`.INFORMATION_SCHEMA.JOBS_BY_PROJECT
WHERE creation_time > TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 1 DAY)
ORDER BY total_bytes_billed DESC
LIMIT 20;

-- Slot utilization by reservation
SELECT * FROM `region-us`.INFORMATION_SCHEMA.RESERVATIONS_TIMELINE;

-- Table storage size and row counts
SELECT table_name, row_count, size_bytes / POW(10,9) AS size_gb
FROM sales.INFORMATION_SCHEMA.TABLE_STORAGE
ORDER BY size_bytes DESC;

Partition and Clustering Inspection

SQL
-- Partition-level metadata
SELECT partition_id, total_rows, total_logical_bytes
FROM sales.INFORMATION_SCHEMA.PARTITIONS
WHERE table_name = 'orders'
ORDER BY partition_id DESC;

-- Check clustering effectiveness via query plan
-- (bq CLI): bq show --format=prettyjson -j <job_id>  # inspect "statistics.query.statementStats"

Access Control

SQL
-- Grant at dataset level
GRANT `roles/bigquery.dataViewer` ON SCHEMA sales TO 'user:analyst@company.com';

-- Row-level access policy
CREATE ROW ACCESS POLICY region_filter
ON sales.orders
GRANT TO ('group:apac-analysts@company.com')
FILTER USING (region = 'APAC');

-- Column-level security via policy tags requires Data Catalog taxonomy setup (Console/Terraform, not pure SQL)

Useful

Code

bq

CLI Commands

Shell
# List datasets
bq ls

# Show table schema
bq show --schema --format=prettyjson sales.orders

# Copy a table
bq cp sales.orders sales.orders_backup

# Query with named parameters
bq query --use_legacy_sql=false \
  --parameter='region:STRING:APAC' \
  'SELECT * FROM sales.orders WHERE region = @region'

# Check job status
bq show -j <job_id>

Scheduled Queries

SQL
-- Set up via bq CLI
-- bq query --use_legacy_sql=false --schedule='every 24 hours' \
--   --display_name='daily_orders_rollup' \
--   'SELECT DATE(order_date) d, SUM(amount) FROM sales.orders GROUP BY d'

Keep

INFORMATION_SCHEMA.JOBS_BY_PROJECT

dry_run