BigQuery

SmartSRE provides comprehensive optimization and cost control for Google BigQuery.

What SmartSRE Scans

Category	Checks
Cost	Query costs, slot utilization, on-demand spend
Performance	Slow queries, missing partitioning/clustering
Storage	Unused tables, expired data, storage optimization
Configuration	Reservation settings, quotas, access controls

Findings

High-Priority

Issue Type	Severity	Description
expensive_query	High	Query exceeds cost threshold
slot_overutilization	High	Slot usage > 95% causing queuing
unpartitioned_large_table	Medium	Table > 1TB without partitioning

Medium-Priority

Issue Type	Severity	Description
no_clustering	Low	Table could benefit from clustering
expired_table	Medium	Table not queried in 90+ days
suboptimal_partition_filter	Medium	Queries scanning full partitions

Available Fixes

Slot Management

Operation	Description	Impact
increase_slots	Temporarily add slot capacity	Low
decrease_slots	Reduce slot allocation	Low
create_reservation	Create new reservation pool	Medium

Table Optimization

Operation	Description	Impact
add_partitioning	Enable time-based partitioning	Medium
add_clustering	Add clustering columns	Low
set_table_ttl	Configure table expiration	Medium

Cost Control

Operation	Description	Impact
set_query_quota	Limit daily query bytes	Low
archive_table	Move to long-term storage tier	Medium
delete_unused_table	Remove unused tables	High

Required Permissions

For Scanning

roles/bigquery.resourceViewer
roles/bigquery.jobUser
bigquery.reservations.get

For Remediation

roles/bigquery.dataEditor
roles/bigquery.admin (for reservations)

Example ChangeSet

{
  "service": "bigquery",
  "intent": "Optimize slot allocation for cost efficiency",
  "steps": [
    {
      "op": "decrease_slots",
      "resource_ref": {
        "project_id": "analytics-prod",
        "location": "US",
        "reservation_name": "default"
      },
      "params": {
        "current_slots": 500,
        "target_slots": 300
      },
      "estimated_cost_usd": -2000.00,
      "impact_score": 25
    }
  ]
}

Configuration Options

Configure BigQuery-specific thresholds in Settings → Risk Policy:

Setting	Default	Description
min_slots	100	Minimum slot allocation
max_slots	1000	Maximum slot allocation
query_cost_threshold_usd	10	Flag queries exceeding this cost
table_expiry_days	90	Days without access before flagging

Best Practices

1. Enable slot analytics — Ensure INFORMATION_SCHEMA.JOBS access
2. Set project budgets — Enable accurate cost guardrails
3. Use partitioning scopes — Target specific datasets for scanning
4. Monitor query patterns — Use findings to identify optimization opportunities