bigquery-optimization

Optimize BigQuery environments, storage costs, and SQL query performance.

23.7k|2.0k|Updated Mar 31, 2026
Ready to run
npx skills add https://github.com/google/skills --skill bigquery-optimization-google
💬 Prompt for ChatGPT / Claude Chat▼
Please help me install this Agent Skill.
Skill: bigquery-optimization
Source: https://github.com/google/skills/tree/main/skills/cloud/bigquery-optimization
Command: npx skills add https://github.com/google/skills --skill bigquery-optimization-google

SYSTEM DOCUMENTATION & REQUIREMENTS

💡 This Skill includes scripts (resource) and references (resource) components.

What problem does it solve? Optimizing BigQuery workloads requires deep understanding of capacity planning, storage billing models, table design, and query efficiency. This Skill automates cost analysis, architectural recommendations, and SQL rewrites to reduce slot time and data scanned. ## Core Features & Use Cases - Capacity & Editions Modeling: Evaluate On-Demand to Editions migrations and right-size reservations using the Slot Estimator. - Storage Optimization: Recommend physical vs. logical billing, design partitioning and clustering strategies, and manage retention policies. - SQL Query Optimization: Rewrite queries to reduce slot-time and data processed using 13+ proven optimization patterns. - Use Case: A company billing $50k/month on BigQuery runs a dashboard query that scans 2TB but returns only 50GB. Use this Skill to add partition filters, clustering, and materialized views to cut query costs by 70%. ## Quick Start Ask the skill to analyze your BigQuery dataset storage costs and recommend whether to migrate from on-demand billing to a 1-year edition commitment.

Frequently Asked Questions about bigquery-optimization

High-intent search queries and answers about installing and using this skill.

FAQPage Schema
How do I reduce BigQuery costs by migrating from on-demand to editions?▼

Use the Slot Estimator to model 30 days of historical workload and compare Pay-As-You-Go vs. 1-year or 3-year commitment costs. Steady baseline workloads (flat daily demand) benefit most from edition commitments at ~40% discounts; spiky workloads require careful autoscaling cap configuration.

Should I use physical or logical storage billing for my dataset?▼

Physical billing is cheaper when data compresses better than 2:1 (varies by region and contract). Calculate the break-even ratio by comparing active/long-term bytes across both models in INFORMATION_SCHEMA.TABLE_STORAGE, accounting for Time Travel and Fail-Safe overhead which add 9–14 days of active physical charges.

How do I design partitioning and clustering for large tables?▼

Partition by date or timestamp (one partition per day for tables spanning 1–7 years, avoiding the 10,000 partition limit), then cluster on up to 4 high-cardinality filter columns. Order clustering columns by filter frequency: most-filtered column first. Enforce partition filters with require_partition_filter=true to prevent unpruned scans.

What SQL optimizations reduce slot time the most?▼

Pre-aggregate join inputs before joining (reduces shuffle), replace SELECT * with explicit columns, add partition filters using BETWEEN instead of EXTRACT functions, and use materialized views for repeated aggregations. Combine 2–3 optimizations to see 30–60% slot reductions.

When should I use materialized views instead of search indexes?▼

Use materialized views for repeated aggregations or joins over large base tables; smart tuning automatically rewrites queries to use them. Use search indexes for selective keyword lookups on STRING/JSON columns >10GB. Combine both: partition a materialized view by date and cluster on high-cardinality columns, then use search indexes on text columns within.

Why do my queries scan more data than expected despite partitioning?▼

Partition pruning fails when predicates wrap the partition column in unsupported functions (EXTRACT(MONTH FROM ts) prevents pruning). Rewrite to isolate the column: replace EXTRACT(YEAR FROM date_col)=2024 with date_col BETWEEN '2024-01-01' AND '2024-12-31', or filter on partition column directly without arithmetic.