An enterprise runs two primary BigQuery workloads under a capacity-based reservations model: Interactive BI Dashboards: Used concurrently by analysts and business users throughout the day, requiring consistent low-latency response times without queuing delays. Batch ETL Pipelines: Executed periodically throughout the day and night to process large staging tables; several queries contain complex Common Table Expressions (CTEs) referenced multiple times across downstream calculations. 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?

Create dedicated reservations for interactive dashboard and batch ETL workloads, assigning a guaranteed slot baseline to the dashboard reservation; configure a moderate maximum autoscale limit for the ETL reservation to mitigate the 1-minute scale-down cost penalty; and materialize repeated CTEs into temporary tables.: ### What the Solution Is 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. ### How It Meets Workload Isolation and Baseline Requirements * **Workload Isolation**: Splitting interactive BI queries and batch ETL jobs into separate reservations prevents unpredicted batch bursts from stealing compute slots from interactive users. * **Guaranteed Low-Latency Performance**: Configuring a **slot baseline** (backed by annual or multi-year capacity commitments) on the dashboard reservation ensures that compute capacity is immediately available without cold-start provisioning delays or query queuing. ### How It Optimizes Autoscaling and Query Computation * **Mitigating Autoscaler Waste**: BigQuery's slot autoscaler enforces a **1-minute minimum** before scaling down slots. When a short-lived query scales up to an excessively high maximum slot limit and finishes in seconds, the organization is billed for all scaled slots across the full minute. Capping the maximum autoscale slots for batch jobs forces the queries to consume more `slot_seconds` within the scaled minute, eliminating idle compute spend. * **Optimizing CTE Execution**: BigQuery does not automatically cache CTE subqueries referenced multiple times within a single query; the optimizer often evaluates the CTE body each time it is referenced. Materializing the intermediate result into a **temporary table** or scalar variable evaluates the data once and reuses it efficiently. ### Key Advantages * **Deterministic Performance**: Provides strict SLA protection for business dashboards. * **Cost Predictability**: Minimizes pay-as-you-go autoscaling waste during brief ETL bursts. * **Resource Efficiency**: Drastically lowers total slot-seconds required for complex batch transformations. * **Elimination of Resource Contention**: Prevents heavy background transformations from starving interactive user queries. ### Why This Is Optimal 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.

Optimize Bigquery Reservations And Query Workloads | professional-cloud-data-engineer

Optimize Bigquery Reservations And Query Workloads

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.

An enterprise runs two primary BigQuery workloads under a capacity-based reservations model:

Interactive BI Dashboards: Used concurrently by analysts and business users throughout the day, requiring consistent low-latency response times without queuing delays.
Batch ETL Pipelines: Executed periodically throughout the day and night to process large staging tables; several queries contain complex Common Table Expressions (CTEs) referenced multiple times across downstream calculations.

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?

Configure a single reservation for all workloads, set strict per-user daily byte processing quotas on BI analysts, convert repeated CTEs into recursive CTEs, and disable slot autoscaling entirely.

Create dedicated reservations for interactive dashboard and batch ETL workloads, assigning a guaranteed slot baseline to the dashboard reservation; configure a moderate maximum autoscale limit for the ETL reservation to mitigate the 1-minute scale-down cost penalty; and materialize repeated CTEs into temporary tables.

Assign batch ETL workloads to an on-demand pricing project with custom byte quotas, keep dashboards in an autoscaling reservation with max slots, and materialize intermediate CTE results as physical date-sharded tables.

Consolidate all interactive and batch queries into a single shared reservation with a baseline of 0 slots; set the query priority of dashboard jobs to BATCH to lower execution costs; and rewrite duplicate CTEs as standard logical views.

Professional Cloud Data Engineer

Optimize Bigquery Reservations And Query Workloads

Related questions