A specialized analytical tool designed to audit Google Cloud BigQuery usage and slash unexpected costs through pattern analysis and storage optimization.
Install & Download
1. ClawHub CLI
The fastest way to install a skill directly from the registry.
npx clawhub@latest install bigquery-optimizer
2. Manual Installation
Copy the skill folder to one of these locations
~/.openclaw/skills/ <project>/skills/ Priority: Workspace > Local > Bundled
3. Prompt Installation
Copy this prompt to OpenClaw to install it automatically.
Help me install bigquery-optimizer using Clawhub. If Clawhub is not installed, install it first (npm i -g clawhub).
Prefer to download?
Get the raw skill files in a ZIP archive.
What is GCP BigQuery Cost Optimizer?
The GCP BigQuery Cost Optimizer is a strategic Openclaw Skills resource designed for cloud architects and data engineers who need to rein in exploding GCP bills. BigQuery is frequently the primary source of surprise expenses due to its on-demand pricing model of $6.25 per TB scanned. This skill acts as a virtual cost consultant, analyzing query logs and storage metrics to identify high-impact savings opportunities.
Rather than requiring direct access to your cloud environment, this skill operates on a data-provided basis, ensuring maximum security. By processing your exported INFORMATION_SCHEMA data, it pinpoints inefficient query patterns, missed partitioning opportunities, and storage mismanagement that lead to unnecessary overhead.
GCP BigQuery Cost Optimizer Use Cases
- Auditing monthly GCP bills to discover which specific queries or service accounts are driving cost spikes.
- Refactoring legacy SQL queries that perform expensive full-table scans into optimized, partitioned versions.
- Determining the financial break-even point to switch from on-demand billing to dedicated slot reservations.
- Identifying large, unused datasets that can be moved to long-term storage or deleted to reduce monthly costs.
How GCP BigQuery Cost Optimizer Works
- The user executes provided read-only CLI commands using the Google Cloud SDK to gather job metadata and storage statistics.
- The skill analyzes the INFORMATION_SCHEMA.JOBS_BY_PROJECT results to find the top 50 most expensive queries from the last 30 days.
- It evaluates query syntax to identify the lack of partition filters or the use of SELECT * anti-patterns.
- The system calculates the ratio of active versus long-term storage to optimize the lifecycle of your data.
- A comprehensive report is generated, including plain-English query rewrites and a prioritized list of optimization tasks based on potential USD savings.
GCP BigQuery Cost Optimizer Setup
To use this within the ecosystem of Openclaw Skills, ensure you have the Google Cloud SDK (gcloud) installed and the following IAM permissions: roles/bigquery.resourceViewer and roles/bigquery.jobUser.
Export your recent job history using this command:
bq query --use_legacy_sql=false 'SELECT user_email, query, total_bytes_billed, ROUND(total_bytes_billed/1e12 * 6.25, 2) as cost_usd, creation_time FROM `region-us`.INFORMATION_SCHEMA.JOBS_BY_PROJECT WHERE DATE(creation_time) >= DATE_SUB(CURRENT_DATE(), INTERVAL 30 DAY) ORDER BY total_bytes_billed DESC LIMIT 50'
Paste the resulting data into the chat for analysis.
GCP BigQuery Cost Optimizer Data Schema & Taxonomy
The optimizer focuses on three primary data vectors to calculate potential savings:
| Data Category | Metrics Analyzed | Goal |
|---|---|---|
| Job Metadata | total_bytes_billed, query_text, user_email |
Identify expensive query patterns |
| Storage Metrics | size_bytes, table_schema, is_partitioned |
Optimize storage costs and partitioning |
| Billing Data | cost_usd, creation_time |
Establish ROI for slot reservations |
GCP BigQuery Cost Optimizer Advanced Features
- Break-even analysis for Slot Reservations vs. On-demand pricing models.
- Materialized view identification for frequently repeated queries scanning the same data blocks.
- Automated partition pruning impact assessment to show exactly how much a query would cost if properly filtered.
- Identification of long-term storage candidates that qualify for the automatic 50% price reduction after 90 days of inactivity.
SKILL.md
Loading
Related Openclaw Skills
Azure Bandwidth Optimizer
A specialized AI tool for identifying and eliminating hidden Azure networking and data egress costs through intelligent data analysis.
AWS Anomaly Explainer
An automated AWS cost incident responder that translates complex billing spikes into actionable root cause analysis and containment steps.
Azure Activity Log & Sentinel Threat Detector
A sophisticated security analysis tool for identifying threat patterns and attack timelines within Azure Activity Logs and Sentinel data.
Solo Leveling
A ruthless life RPG system that transforms real-world habits into an addictive progression system with AI-verified accountability.
AWS CloudTrail Threat Detector
A specialized AI security analyst that detects threats, unauthorized changes, and MITRE ATT&CK patterns within AWS CloudTrail logs.
AWS Compliance Gap Analyzer
An expert analysis skill that maps exported AWS environment data against major compliance frameworks like CIS, SOC 2, HIPAA, and PCI-DSS.
Featured*
