Advanced
Prepare and test your skills
Advanced
Prepare and test your skills
BigQuery Reservations with Idle Slot Sharing
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 is designing a compute capacity strategy for its analytical workloads in BigQuery. The workload environment consists of three distinct patterns:
- Production ELT pipelines: Run continuously 24/7 with a predictable, steady baseline compute demand.
- Data science ad-hoc analytics: Intermittent, bursty compute usage during business hours that requires rapid scaling without manual intervention.
- Departmental BI dashboards: Variable query volume throughout the day that can tolerate shared capacity and should utilize spare resources when available.
You need to optimize compute costs, prevent resource contention between workloads, ensure predictable performance, and maximize resource utilization across the organization.
Which capacity management and reservation strategy should you implement?
Explanations
Keep all workloads on on-demand billing, and apply project-level and user-level byte scan quotas to manage costs and prevent query concurrency bottlenecks.
Keep all workloads on on-demand billing, and apply project-level and user-level byte scan quotas to manage costs and prevent query concurrency bottlenecks.
What this option is
This approach uses BigQuery's default on-demand billing model, where compute charges are based solely on the number of bytes scanned by individual queries, with cost control managed via custom query quotas (bytes processed per day).
Why it doesn't meet the requirement
- No commitment discounts: On-demand pricing does not offer committed capacity discounts, making 24/7 continuous steady-state ELT pipelines significantly more expensive than capacity-based commitments.
- Unpredictable query concurrency and throttling: On-demand projects share a regional pool with a default soft limit of 2,000 slots per project, which does not guarantee isolated, dedicated compute capacity for critical production pipelines.
- No resource partitioning: Custom byte quotas only cap total data scanned per day or user; they do not guarantee execution slots or protect production jobs from being delayed by concurrent ad-hoc analytics.
Key characteristics and limitations
- Meters compute consumption strictly in TiB scanned rather than slot-hours.
- Provides high flexibility for sporadic queries but lacks predictability for continuous steady-state data transformations.
- Does not provide workload governance or compute isolation features available in BigQuery reservations.
This answer would be correct if
This option would be ideal for environments with highly infrequent, unpredictable query patterns across small datasets where continuous compute baseline costs would exceed the cost of paying per terabyte scanned.
Create separate administration projects for each workload type. Purchase slot commitments in each administration project and configure static slot reservations without autoscaling to prevent budget overruns.
Create separate administration projects for each workload type. Purchase slot commitments in each administration project and configure static slot reservations without autoscaling to prevent budget overruns.
What this option is
This configuration creates distinct, isolated administration projects for each workload department or category and provisions fixed, static slot reservations funded entirely by separate commitments.
Why it doesn't meet the requirement
- Prevents idle slot sharing: Idle compute slots cannot be shared across reservations that belong to different administration projects. When the ELT or BI pipelines are lightly loaded, their paid slot capacity sits unused rather than aiding data science spikes.
- Lacks elasticity for bursty workloads: Static slot allocations without autoscaling slots cause ad-hoc data science queries to queue or experience severe latency degradation during sudden workload spikes.
- Suboptimal cost trade-off: Committing fixed slot pools for bursty, intermittent workloads like ad-hoc analytics leads to paying for excess idle capacity during non-business hours.
Key characteristics and limitations
- Isolates billing completely between separate business units at the expense of infrastructure efficiency.
- Enforces hard compute boundaries that block cross-departmental resource pooling.
- Increases administrative overhead by requiring slot management across multiple projects.
This answer would be correct if
This approach would be appropriate if strict organizational policies required complete billing isolation between distinct legal entities or divisions where cross-project cost subsidization and compute resource sharing are explicitly prohibited.
Create a single reservation in an administration project with a static baseline pool of slots, assign all projects to it, and disable idle slot sharing to ensure strict fair-share scheduling.
Create a single reservation in an administration project with a static baseline pool of slots, assign all projects to it, and disable idle slot sharing to ensure strict fair-share scheduling.
What this option is
This configuration routes all organizational workloads (ELT, ad-hoc analytics, and BI) into a single, combined reservation with a fixed slot capacity and disables idle slot sharing.
Why it doesn't meet the requirement
- Resource contention and noisy neighbor risk: Placing production ELT and heavy ad-hoc exploratory queries in the same single reservation forces them to compete for the same slot pool, risking pipeline SLA breaches.
- No autoscaling flexibility: A purely static slot reservation cannot scale up automatically when multiple analysts submit complex queries concurrently during peak business hours.
- Inefficient slot utilization: Disabling idle slot sharing within a shared pool restricts BigQuery from opportunistically assigning spare compute units to pending jobs.
Key characteristics and limitations
- Lacks workload segmentation and governance controls.
- Prevents workload-specific performance SLAs and isolation.
- Forces batch transformations and interactive dashboards to share the exact same compute budget.
This answer would be correct if
This setup would be suitable for a small team running homogeneous, non-critical batch analytics where workload isolation is unnecessary and a strict, flat monthly compute budget is the sole operational constraint.
Create a centralized administration project. Purchase slot commitments for the steady-state baseline and assign them to an ELT reservation. Create separate reservations for data science and BI with configured autoscaling slot limits, and enable idle slot sharing across reservations.
Create a centralized administration project. Purchase slot commitments for the steady-state baseline and assign them to an ELT reservation. Create separate reservations for data science and BI with configured autoscaling slot limits, and enable idle slot sharing across reservations.
What this strategy is
This strategy leverages BigQuery capacity-based workload management centered in a single administration project. It combines committed slot capacity for predictable, steady-state workloads with autoscaling slots and idle slot sharing to serve bursty and variable analytics workloads cost-effectively.
How it meets the requirements
- Optimize compute costs for steady-state workloads: Purchasing slot commitments for the 24/7 production ELT baseline provides the highest discount rate compared to purely on-demand or non-committed capacity.
- Prevent resource contention: Dedicated reservations (
elt,data-science,bi) partition slot capacity so intensive queries in one workload domain do not starve critical production pipelines. - Handle bursty workloads without manual intervention: Configuring autoscaling slot limits on reservations allows data science and BI workloads to dynamically scale out slots as demand increases and scale down when idle.
- Maximize utilization and efficiency: Grouping reservations within a single centralized administration project allows BigQuery to dynamically share idle slots across all reservations, ensuring that unused ELT or BI capacity accelerates active ad-hoc queries.
Key advantages and capabilities
- Predictable billing and committed savings: Blends long-term committed use discounts for baseline workloads with flexible autoscaling budgets.
- Noisy neighbor isolation: Workload assignments to separate reservations guarantee dedicated floor allocations.
- Cross-reservation sharing: Idle slots automatically shift to reservations experiencing temporary compute spikes without manual rebalancing.
- Centralized governance: A dedicated administration project centralizes billing, slot management, and assignment policies.
Why this is optimal
This approach strikes the optimal trade-off between cost, performance, and flexibility. Steady-state baseline operations achieve maximum financial efficiency through commitments, while volatile queries scale elastically within defined slot caps and take advantage of shared idle capacity across the organization.
Try 10 interactive questions like this one, free
Answer them yourself and get your score the moment you finish. No account needed.
Full access includes
- 1,353 practice questions across all 5 exam domains
- Every answer explained: why it is right, and why the others are not
- Scout, an AI tutor that answers your follow-up questions
- Readiness tracking by domain, so you know when to book the exam
Related questions
Keep the momentum going with these hand-picked practice scenarios
AdvancedProfessional Data Engineer
BigQuery Shuffle Quota Exceeded
Manage workloads, such as jobs, queries, and compute capacity (reservations)
AdvancedProfessional Data Engineer
Dataproc Driver Node Groups
Manage workloads, such as jobs, queries, and compute capacity (reservations)
AdvancedProfessional Data Engineer
BigQuery Denormalization and Clustering
Minimizing costs per required business need for data
AdvancedProfessional Data Engineer
BigQuery Policy Tags Dynamic Data Masking
Security, data masking, Identity and Access Management (IAM), and Cloud Data Loss Prevention (Cloud DLP)
AdvancedProfessional Data Engineer
Cloud Storage gRPC Client and Broadcast Hash Join
Processing logic
AdvancedProfessional Cloud DevOps Engineer
Structured Logging and Tail-Based Sampling
Observability issues
Last updated Validated