analyzing-bigquery

name	analyzing-bigquery
description	Use when working with Bigquery — google BigQuery job analysis, slot utilization, cost analysis, dataset management, and query optimization.
connection_type	gcp
preload	false

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

Shortcut	Counter	Why
"I'll skip discovery and check known resources"	Always run Phase 1 discovery first	Resource names change, new resources appear — assumed names cause errors
"The user only asked for a quick check"	Follow the full discovery → analysis flow	Quick checks miss critical issues; structured analysis catches silent failures
"Default configuration is probably fine"	Audit configuration explicitly	Defaults often leave logging, security, and optimization features disabled
"Metrics aren't needed for this"	Always check relevant metrics when available	API/CLI responses show current state; metrics reveal trends and intermittent issues
"I don't have access to that"	Try the command and report the actual error	Assumed permission failures prevent useful investigation; actual errors are informative

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