Analyzing Bigquery

BigQuery Analysis Skill

Analyze and optimize BigQuery with safe, read-only operations.

MANDATORY: Two-Phase Execution

You MUST follow this two-phase pattern. Skipping Phase 1 causes hallucinated dataset/table names.

Phase 1: Discovery (ALWAYS run first)

#!/bin/bash

# 1. List datasets
bq ls --project_id="$GCP_PROJECT" --format=json

# 2. List tables in a dataset
bq ls --project_id="$GCP_PROJECT" "$DATASET" --format=json

# 3. Get table schema (never assume column names)
bq show --schema --format=json "$GCP_PROJECT:$DATASET.$TABLE"

# 4. Table details
bq show --format=json "$GCP_PROJECT:$DATASET.$TABLE"

# 5. Sample data
bq query --use_legacy_sql=false --max_rows=5 "SELECT * FROM \`$GCP_PROJECT.$DATASET.$TABLE\` LIMIT 5"

Phase 1 outputs:

• Datasets and tables in the project
• Table schemas with column names and types
• Table metadata (size, row count, partitioning)

Phase 2: Analysis (only after Phase 1)

Only reference datasets, tables, and columns confirmed in Phase 1.

Shell Script Patterns

Helper Function

#!/bin/bash

# Core BigQuery runner — always use this
bq_query() {
    local query="$1"
    bq query --use_legacy_sql=false --format=json --max_rows="${2:-100}" "$query"
}

# Dry run for cost estimation
bq_dryrun() {
    local query="$1"
    bq query --use_legacy_sql=false --dry_run "$query" 2>&1
}

Anti-Hallucination Rules

• NEVER reference a dataset or table without confirming via bq ls
• NEVER reference column names without seeing them in bq show --schema
• NEVER assume partitioning scheme — check table metadata
• NEVER guess project IDs — always confirm with gcloud config get project
• ALWAYS dry-run expensive queries to check cost before execution

Safety Rules

• READ-ONLY ONLY: Use only SELECT, bq show, bq ls, INFORMATION_SCHEMA queries
• FORBIDDEN: INSERT, UPDATE, DELETE, DROP, CREATE TABLE, bq rm without explicit user request
• ALWAYS dry-run first for queries scanning more than 1GB
• ALWAYS add LIMIT to exploration queries
• Use --max_rows to limit bq output
• Prefer partitioned/clustered scans — filter on partition column to reduce cost

Common Operations

Dataset & Table Overview

#!/bin/bash
echo "=== Datasets ==="
bq ls --project_id="$GCP_PROJECT" --format=json | jq '.[] | {datasetId: .datasetReference.datasetId, location}'

echo ""
echo "=== Largest Tables ==="
bq_query "SELECT table_schema, table_name, ROUND(size_bytes/1024/1024/1024, 2) as size_gb, row_count, TIMESTAMP_MILLIS(creation_time) as created, TIMESTAMP_MILLIS(last_modified_time) as modified FROM \`$GCP_PROJECT\`.INFORMATION_SCHEMA.TABLE_STORAGE ORDER BY size_bytes DESC LIMIT 20"

echo ""
echo "=== Partitioned Tables ==="
bq_query "SELECT table_catalog, table_schema, table_name, partition_type, partition_expiration_ms FROM \`$GCP_PROJECT\`.INFORMATION_SCHEMA.TABLE_OPTIONS t JOIN \`$GCP_PROJECT\`.INFORMATION_SCHEMA.PARTITIONED_TABLES p USING (table_catalog, table_schema, table_name) LIMIT 20" 2>/dev/null

Job & Cost Analysis

#!/bin/bash
echo "=== Recent Jobs (last 24h) ==="
bq_query "SELECT job_id, user_email, statement_type, total_bytes_processed, total_slot_ms, creation_time, state FROM \`region-us\`.INFORMATION_SCHEMA.JOBS_BY_PROJECT WHERE creation_time > TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 24 HOUR) ORDER BY total_bytes_processed DESC LIMIT 20"

echo ""
echo "=== Cost by User (last 7 days) ==="
bq_query "SELECT user_email, COUNT(*) as query_count, ROUND(SUM(total_bytes_processed)/1024/1024/1024/1024, 4) as tb_processed, ROUND(SUM(total_bytes_processed)/1024/1024/1024/1024 * 6.25, 2) as estimated_cost_usd FROM \`region-us\`.INFORMATION_SCHEMA.JOBS_BY_PROJECT WHERE creation_time > TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 7 DAY) AND job_type = 'QUERY' GROUP BY user_email ORDER BY tb_processed DESC LIMIT 20"

echo ""
echo "=== Slot Utilization ==="
bq_query "SELECT TIMESTAMP_TRUNC(period_start, HOUR) as hour, AVG(period_slot_ms / TIMESTAMP_DIFF(period_end, period_start, MILLISECOND)) as avg_slots FROM \`region-us\`.INFORMATION_SCHEMA.JOBS_TIMELINE_BY_PROJECT WHERE period_start > TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 24 HOUR) GROUP BY hour ORDER BY hour DESC LIMIT 24"

Query Optimization

#!/bin/bash
echo "=== Expensive Queries (last 24h, >1GB) ==="
bq_query "SELECT job_id, SUBSTR(query, 1, 100) as query_preview, ROUND(total_bytes_processed/1024/1024/1024, 2) as gb_processed, total_slot_ms, TIMESTAMP_DIFF(end_time, start_time, SECOND) as duration_sec FROM \`region-us\`.INFORMATION_SCHEMA.JOBS_BY_PROJECT WHERE creation_time > TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 24 HOUR) AND total_bytes_processed > 1073741824 ORDER BY total_bytes_processed DESC LIMIT 15"

echo ""
echo "=== Query Plan Analysis ==="
# Dry run to check bytes scanned
bq_dryrun "SELECT col1, col2 FROM \`$GCP_PROJECT.$DATASET.$TABLE\` WHERE partition_col = '2024-01-01'"

Storage Analysis

#!/bin/bash
echo "=== Storage by Dataset ==="
bq_query "SELECT table_schema, COUNT(*) as tables, ROUND(SUM(size_bytes)/1024/1024/1024, 2) as total_gb, ROUND(SUM(CASE WHEN storage_tier = 'LONG_TERM' THEN size_bytes ELSE 0 END)/1024/1024/1024, 2) as long_term_gb FROM \`$GCP_PROJECT\`.INFORMATION_SCHEMA.TABLE_STORAGE GROUP BY table_schema ORDER BY total_gb DESC"

echo ""
echo "=== Tables with No Long-term Storage Savings ==="
bq_query "SELECT table_schema, table_name, ROUND(size_bytes/1024/1024/1024, 2) as gb, TIMESTAMP_MILLIS(last_modified_time) as last_modified FROM \`$GCP_PROJECT\`.INFORMATION_SCHEMA.TABLE_STORAGE WHERE last_modified_time > UNIX_MILLIS(TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 90 DAY)) ORDER BY size_bytes DESC LIMIT 20"

Output Format

Present results as a structured report:

Analyzing Bigquery Report
═════════════════════════
Resources discovered: [count]

Resource       Status    Key Metric    Issues
──────────────────────────────────────────────
[name]         [ok/warn] [value]       [findings]

Summary: [total] resources | [ok] healthy | [warn] warnings | [crit] critical
Action Items: [list of prioritized findings]

Target ≤50 lines of output. Use tables for multi-resource comparisons.

Counter-Rationalizations

Common Pitfalls

• Full table scans: Queries without partition filters scan entire tables — always filter on partition column
• **SELECT ***: Scanning all columns is expensive — select only needed columns
• On-demand pricing: Each TB scanned costs ~$6.25 — always dry-run first
• Slot contention: Flat-rate reservations share slots — check slot utilization
• Streaming buffer: Recently streamed data may not be in partitions yet — affects partition pruning
• INFORMATION_SCHEMA region: Must specify region (e.g., region-us) for jobs metadata
• Legacy SQL: Always use --use_legacy_sql=false — legacy SQL has different syntax and limitations