BQ FinOps CLI

BigQuery cost monitoring and optimization toolkit for data engineers

Monitor BigQuery costs, analyze usage patterns, and optimize table structures with a simple command-line interface.

🎯 Features

• Cost Analysis: Analyze BigQuery costs over time periods
• Query Insights: Identify expensive queries and usage patterns
• Table Analysis: Evaluate table structure and optimization opportunities
• Optimization Recommendations: Get actionable suggestions for cost reduction
• DDL Generation: Auto-generate partitioning/clustering DDL
• Multi-dimensional Breakdown: Costs by dataset, user, table
• CLI & Python API: Use as command-line tool or import as library

📦 Installation

pip install bq-finops-cli

Requirements:

• Python 3.8+
• GCP credentials with BigQuery access
• BigQuery API enabled in your project

🚀 Quick Start

1. Setup Authentication

# Set up GCP credentials
gcloud auth application-default login

# Or set service account key
export GOOGLE_APPLICATION_CREDENTIALS="/path/to/service-account-key.json"

2. Analyze Costs

# Analyze costs for the last 30 days
bq-finops analyze costs -p my-gcp-project

# Analyze specific date range
bq-finops analyze costs -p my-project \
  --start-date 2025-01-01 \
  --end-date 2025-01-31

Sample Output:

============================================================
💰 COST SUMMARY
============================================================
Total Cost (USD)        $1,234.56
Query Count             15,432
Avg Cost per Query      $0.0800
Total Bytes Processed   987,654,321,098

============================================================
🔥 TOP EXPENSIVE QUERIES
============================================================
╔══════════════════════╦════════════════════════╦═════════════╦══════════════╗
║ Job ID               ║ User                   ║ Cost        ║ Bytes        ║
╠══════════════════════╬════════════════════════╬═════════════╬══════════════╣
║ job_abc123...        ║ user@example.com       ║ $125.50     ║ 20,123,456   ║
║ job_def456...        ║ analyst@example.com    ║ $98.30      ║ 15,789,012   ║
╚══════════════════════╩════════════════════════╩═════════════╩══════════════╝

3. Analyze a Table

bq-finops analyze table -p my-project -d warehouse -t fact_sales

4. Generate Optimization Report

bq-finops optimize report -p my-project -d warehouse -t fact_sales

5. Generate DDL

# Add partitioning
bq-finops optimize generate-ddl -p my-project \
  -d warehouse -t fact_sales \
  --partition-column sale_date

# Add partitioning + clustering
bq-finops optimize generate-ddl -p my-project \
  -d warehouse -t fact_sales \
  --partition-column sale_date \
  --cluster-columns customer_id \
  --cluster-columns product_id

📚 Commands

Analyze Commands

bq-finops analyze costs

Analyze BigQuery costs over a time period.

Options:

• -p, --project-id: GCP project ID (required)
• -s, --start-date: Start date (YYYY-MM-DD)
• -e, --end-date: End date (YYYY-MM-DD)
• -d, --datasets: Filter by datasets (multiple allowed)
• -f, --format: Output format (table/json)

Examples:

# Last 30 days (default)
bq-finops analyze costs -p my-project

# Specific date range
bq-finops analyze costs -p my-project \
  --start-date 2025-01-01 --end-date 2025-01-31

# Specific datasets
bq-finops analyze costs -p my-project \
  -d warehouse -d marts

# JSON output
bq-finops analyze costs -p my-project --format json

bq-finops analyze table

Analyze a specific table for optimization opportunities.

Options:

• -p, --project-id: GCP project ID (required)
• -d, --dataset-id: Dataset ID (required)
• -t, --table-id: Table ID (required)

Example:

bq-finops analyze table -p my-project -d warehouse -t dim_employee

Optimize Commands

bq-finops optimize report

Generate optimization report with recommendations.

Options:

• -p, --project-id: GCP project ID (required)
• -d, --dataset-id: Dataset ID (required)
• -t, --table-id: Table ID (required)

Example:

bq-finops optimize report -p my-project -d warehouse -t fact_payroll

bq-finops optimize generate-ddl

Generate DDL for table optimization.

Options:

• -p, --project-id: GCP project ID (required)
• -d, --dataset-id: Dataset ID (required)
• -t, --table-id: Table ID (required)
• --partition-column: Column to partition by
• --cluster-columns: Columns to cluster by (multiple allowed)

Examples:

# Partition only
bq-finops optimize generate-ddl -p my-project \
  -d warehouse -t fact_sales \
  --partition-column sale_date

# Cluster only
bq-finops optimize generate-ddl -p my-project \
  -d warehouse -t fact_sales \
  --cluster-columns customer_id --cluster-columns product_id

# Both
bq-finops optimize generate-ddl -p my-project \
  -d warehouse -t fact_sales \
  --partition-column sale_date \
  --cluster-columns customer_id --cluster-columns product_id

Utility Commands

bq-finops examples

Show example commands.

bq-finops --version

Show version.

bq-finops --help

Show help.

🐍 Python API

Use BQ FinOps programmatically:

Cost Analysis

from bq_finops import CostAnalyzer

# Initialize analyzer
analyzer = CostAnalyzer(project_id="my-gcp-project")

# Analyze costs
report = analyzer.analyze_period(
    start_date="2025-01-01",
    end_date="2025-01-31",
    datasets=["warehouse", "marts"]
)

print(f"Total cost: ${report.total_cost:.2f}")
print(f"Query count: {report.query_count}")

# Analyze a specific table
table_analysis = analyzer.analyze_table("warehouse", "fact_sales")
print(f"Table size: {table_analysis['size_gb']} GB")

# Get dataset cost
dataset_cost = analyzer.get_dataset_cost("warehouse", days=30)
print(f"Dataset cost: ${dataset_cost['total_cost_usd']:.2f}")

Query Optimization

from bq_finops import QueryOptimizer

# Initialize optimizer
optimizer = QueryOptimizer(project_id="my-gcp-project")

# Generate partition DDL
ddl = optimizer.generate_partition_ddl(
    "warehouse",
    "fact_sales",
    "sale_date"
)
print(ddl)

# Generate cluster DDL
ddl = optimizer.generate_cluster_ddl(
    "warehouse",
    "fact_sales",
    ["customer_id", "product_id"],
    partition_column="sale_date"
)
print(ddl)

# Analyze a query
query = "SELECT * FROM `project.dataset.table` WHERE date > '2025-01-01'"
analysis = optimizer.analyze_query(query)
print(f"Recommendations: {analysis['recommendations']}")

# Generate optimization report
report = optimizer.generate_optimization_report("warehouse", "fact_sales")
print(f"Current state: {report['current_state']}")
print(f"Recommendations: {report['recommendations']}")

💰 Cost Optimization Tips

1. Partition Large Tables

• Impact: 50-90% cost reduction
• Best for: Tables > 1 GB with date/timestamp column
• Recommended: Partition by DATE or TIMESTAMP column

2. Cluster Frequently Filtered Columns

• Impact: 10-30% performance improvement
• Best for: Tables > 1 GB with common filter patterns
• Recommended: Cluster by 2-4 columns max

3. Set Data Retention Policies

• Impact: Reduces storage costs
• Best for: Time-series data
• Recommended: Set partition expiration (e.g., 90 days)

4. Avoid SELECT *

• Impact: 50-80% cost reduction per query
• Recommendation: Select only needed columns

5. Use Partition Filters

• Impact: 90%+ cost reduction per query
• Recommendation: Always filter on partition column

🎯 Use Cases

FinOps Dashboard

Monitor monthly BigQuery spending:

# Get current month costs
bq-finops analyze costs -p my-project \
  --start-date 2025-11-01 \
  --format json > november_costs.json

Optimization Audit

Audit all tables for optimization opportunities:

# Check each table
for table in fact_sales fact_orders fact_inventory; do
  bq-finops optimize report -p my-project -d warehouse -t $table
done

CI/CD Integration

Add cost checks to your CI pipeline:

# Validate query before deployment
bq-finops analyze costs -p dev-project --format json | \
  jq '.total_cost < 100' # Fail if > $100

🔧 Development

Setup

git clone https://github.com/yourusername/bq-finops-cli.git
cd bq-finops-cli
pip install -e ".[dev]"

Run Tests

pytest tests/ -v --cov=bq_finops

Code Quality

black src/
flake8 src/

📖 Examples

See the examples/ directory for sample configurations and scripts.

🤝 Contributing

Contributions welcome! Please:

1. Fork the repository
2. Create a feature branch
3. Add tests for new features
4. Submit a pull request

📄 License

MIT License - see LICENSE file for details

🔗 Links

• PyPI: https://pypi.org/project/bq-finops-cli/
• GitHub: https://github.com/yourusername/bq-finops-cli
• Documentation: https://github.com/yourusername/bq-finops-cli#readme

💡 Why BQ FinOps CLI?

• Save Money: Identify and fix expensive queries
• Optimize Performance: Proper partitioning/clustering
• Data Governance: Track usage by team/dataset
• Proactive Monitoring: Catch cost spikes early
• Best Practices: Built-in BigQuery optimization rules

🎓 Related Projects

This module is part of the Payroll & Workforce Analytics Modernization project:

• synthetic-payroll-lab - Generate test data
• scd2-bq-engine - SCD Type 2 automation
• dataform-warehouse-blueprints - SQLX template generator

---

Built with ❤️ for data engineers who care about costs.