Data Cloud Optimization
BigQuery cost optimization from workload to query. Expensive queries, slot reservations, pricing model fit, table storage, and who is spending.
Data Cloud Optimization is the CloudIQ solution for BigQuery. It shows which workloads drive your BigQuery bill and how to reduce them, not only what you spent.
Three terms first#
- Slot: a unit of BigQuery compute.
- On-demand pricing: you pay per byte a query scans.
- Editions pricing: you pay for slot capacity you reserve, with optional autoscaling.
CloudIQ prices every opportunity under both models, so you can see which one fits each project.
Expensive queries#
Finds your most expensive queries and the recurring patterns behind them. The same query run with different values counts as one pattern with one total cost.
Evidence: 30 days of job history. Data processed, slot hours, run frequency, duration, and the tables each pattern reads.
Delivers:
- an optimized SQL rewrite for supported patterns, for an engineer to review
- whether each pattern runs daily, on weekends or on demand, so you can rewrite, reschedule or retire it
Slot reservations#
Finds capacity you pay for but do not use, and capacity so tight that queries wait in a queue.
Evidence: slot usage by project and reservation, minute by minute, with queue and run-time percentiles.
Delivers:
- the cost of unused capacity for each reservation
- a recommended baseline and autoscaling setting, with the effect on queue time shown
Pricing model and commitments#
Finds whether on-demand or Editions is cheaper for each project, and whether your commitments match your usage.
Evidence: your measured usage, your current commitments, and both pricing models applied to the same workload.
Delivers:
- projected savings for pay-as-you-go, one-year and three-year options
- a break-even point for each project
- daily alerts when you are over- or under-committed
Table storage#
Finds tables that cost more than they should: unpartitioned, unclustered, rarely read, or no longer needed.
Evidence: each table's storage cost plus the cost of the queries that read it, and when it was last read.
Delivers: partitioning, clustering and expiration settings, cleanup candidates, and cheaper storage options.
Who is spending#
Finds the users, projects and workloads behind your BigQuery spend, and spend changes as they happen.
Evidence: slot cost by user and project, and daily spend with the project and query pattern that moved it.
Delivers:
- cost allocation by team, ready for chargeback
- a profile of each project's workload
- an explanation of what moved when spend moves
Optional reservation automation#
Reservation automation is off by default and is not part of the standard read-only setup. If you turn it on:
- you set its scope and change limits
- it starts in dry-run mode
- it never reduces capacity without approval
- it writes a rollback command for every change and keeps two audit logs
Access needed#
A BigQuery-only setup needs five read-only roles and four APIs. A reduced option leaves out reservation administration. Every analysis query is logged in your own dataset.
Also included: BigQuery usage in committed use discount planning, and carbon estimates per query pattern and per reservation.
Next#
Try this against your own spend
A free savings analysis runs the checks described in these docs against your AWS or Google Cloud account and returns a ranked list of priced findings.