bq-finops-cli 0.1.0

BigQuery cost monitoring and optimization toolkit

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...        ║ [e-mail]       ║ $125.50     ║ 20,123,456   ║
║ job_def456...        ║ [e-mail]    ║ $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.