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.
An enterprise runs two primary BigQuery workloads under a capacity-based reservations model:
Recent monitoring shows that interactive dashboard queries experience sudden latency spikes when batch ETL tasks trigger rapid slot autoscaling. Furthermore, short-lived ETL queries that finish in a few seconds are driving unnecessary compute expenses by causing the autoscaler to provision large slot spikes, and multi-referenced CTEs are consuming excessive processing resources.
Which strategy should you implement to optimize performance, eliminate workload contention, and control costs?
This approach uses a single unpartitioned reservation without autoscaling, applies custom query quotas to limit byte consumption per user, and converts repeated subquery expressions into recursive common table expressions.
This strategy would be appropriate if you needed to enforce strict departmental billing guardrails to prevent rogue runaway queries in ad-hoc sandbox environments rather than tuning production ETL and BI pipelines.
Custom query quotas measure scanned bytes, which are primarily relevant in the on-demand billing model rather than internal compute slot management within capacity reservations.
This suggestion splits billing models by placing batch ETL workloads into an on-demand pricing model with custom byte quotas, placing dashboards in a pure autoscaling reservation, and writing intermediate query stages to date-sharded tables.
table_YYYYMMDD) introduces substantial metadata and permission overhead for BigQuery, degrading query performance compared to using partitioned and clustered tables or temporary session tables.This architecture would be suitable if the batch workload consisted of very small, infrequent queries where maintaining dedicated slot commitments is cost-prohibitive compared to pay-per-byte processing.
Google Cloud strongly recommends using time-unit partitioned tables instead of date-sharded tables, and using session-scoped temporary tables for intermediate ETL processing stages.
This proposal attempts to use a single shared capacity-based reservation with dynamic autoscaling from zero, switches dashboard queries to BATCH priority, and relies on standard SQL logical views for intermediate ETL calculations.
BATCH priority queues the queries until idle resources become available. Batch queries do not start immediately, which breaks user experience for interactive reporting.This approach would be appropriate if all jobs were non-urgent background transformations that required minimal cost with no latency SLAs, and if intermediate data logic needed central governance without materialization overhead.
BATCH priority is intended for asynchronous maintenance or backfill jobs that can sit in a queue for several minutes or hours without impacting operational deadlines.
This architecture establishes dedicated BigQuery reservations to isolate interactive and batch workloads, applies appropriate slot baseline allocations and autoscaling constraints, and refactors SQL query execution using temporary tables to eliminate redundant evaluations.
slot_seconds within the scaled minute, eliminating idle compute spend.Isolating reservations while pairing baseline capacity for steady interactive traffic and bounded autoscaling for bursty transformations creates the ideal balance between performance guarantees and cloud expenditure.
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