Bigquery Slot Utilization Autoscale Reservation Optimization

Worked example. The correct answer is already marked and every option is explained below, so there is nothing to select here. To answer questions yourself, start the free trial.

A financial analytics company runs critical daily extract-transform-load (ETL) and business intelligence (BI) workloads in BigQuery under an Enterprise edition slot reservation named prod-analytics. During the daily market open window (08:00 to 11:00 UTC), BI reporting dashboards experience severe latency spikes, and scheduled transformation pipelines fail their service level agreements (SLAs).

The lead data engineer observes the following requirements and constraints:

Identify whether performance degradation is caused by slot contention and queuing versus inefficient, un-partitioned queries.
Ensure query queuing is eliminated during peak burst traffic.
Optimize compute costs by avoiding continuous over-provisioning during non-peak hours.

Which monitoring approach and capacity reservation strategy should the team implement?

Inspect Cloud Monitoring for spanner_sys.query_stats_top_hour metrics, configure query execution timeouts on the project metadata, and convert all ETL data pipelines to execute under a dedicated Cloud SQL read pool replica.

Query INFORMATION_SCHEMA.JOBS_BY_PROJECT and evaluate the job_stages along with reservation_id to correlate total_slot_ms and job queuing duration; inspect Cloud Monitoring metric bigquery.googleapis.com/reservation/slots_allocated against assigned capacity, then configure BigQuery reservation baseline slots combined with autoscale max slots on prod-analytics.

Review database query logs in Cloud Logging for out-of-memory (OOM) fatal events, increase shared_buffers in the BigQuery configuration console, and assign a permanent static baseline equal to maximum peak slot consumption 24/7.

Query INFORMATION_SCHEMA.TABLE_STORAGE to determine overall table churn, set work_mem parameters at the dataset level, and switch the BigQuery reservation from Enterprise edition to on-demand compute pricing with a maximum daily bytes billed cap.

Professional Cloud Data Engineer

Bigquery Slot Utilization Autoscale Reservation Optimization

Related questions