professional-cloud-data-engineer
Prepare and test your skills
Prepare and test your skills
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.
Keep the momentum going with these hand-picked practice scenarios
Want more questions like this?
Get a free certification question every week.
Last updated
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:
Which monitoring approach and capacity reservation strategy should the team implement?
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.
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.
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.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.
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.
In BigQuery editions (Standard, Enterprise, Enterprise Plus), slot reservations allow organizations to allocate dedicated computing capacity to specific workloads, folders, or projects. Workload monitoring leverages both Cloud Monitoring metrics (such as reservation slot allocation and execution metrics) and the administrative metadata views in INFORMATION_SCHEMA.JOBS_BY_* and INFORMATION_SCHEMA.RESERVATION_USAGE to diagnose throughput bottlenecks, concurrency limits, and execution stage skew.
StudyCerts
StudyCertsINFORMATION_SCHEMA.JOBS_BY_PROJECT, administrators can examine metrics such as job queuing time (timeline or execution start delays) and total_slot_ms. When total_slot_ms is high and jobs spend substantial time in a pending or queued state while Cloud Monitoring shows slots_allocated pinned at the reservation ceiling, performance degradation is definitively driven by slot starvation rather than un-partitioned scan bottlenecks.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.
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.