Prepare and test your skills

Prepare and test your skills

Free sample question, answered and explained below. The Professional Data Engineer practice bank has 1,353 more like it. Try 10 free: instant score, no account.
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?
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.
INFORMATION_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.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.This option suggests querying TABLE_STORAGE metadata to check physical storage sizes and churn, modifying PostgreSQL-specific memory flags (work_mem), and migrating the entire workload back to on-demand pricing with custom project billing quotas.
TABLE_STORAGE reflects physical and logical byte storage, storage billing models, and time-travel storage. It provides no visibility into real-time slot usage, CPU milliseconds, or query queue wait times.work_mem is a PostgreSQL/Cloud SQL memory tuning parameter that does not exist in BigQuery's fully managed, serverless architecture.TABLE_STORAGE is strictly used for storage billing and data lifecycle analysis.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.This option attempts to diagnose BigQuery performance issues by monitoring Cloud Spanner system metrics (spanner_sys.query_stats_top_hour) and reroutes workloads to Cloud SQL read replicas.
spanner_sys views and Cloud SQL read pool replicas belong to transactional database architectures (Spanner and Cloud SQL), not BigQuery analytical data warehousing.shared_buffers in the BigQuery configuration console, and assign a permanent static baseline equal to maximum peak slot consumption 24/7.This option assumes BigQuery suffers from database kernel out-of-memory errors, attempts to configure relational cache flags (shared_buffers), and provisions a continuous, un-scaled maximum capacity commitment.
shared_buffers is a memory buffer configuration used in PostgreSQL and AlloyDB instances; BigQuery is a serverless, multi-tenant execution engine with managed memory and shuffle infrastructure.pg_stat_activity or host kernel logs; execution bottlenecks must be tracked via BigQuery job statistics.Answer them yourself and get your score the moment you finish. No account needed.
Full access includes
Keep the momentum going with these hand-picked practice scenarios
Last updated Validated