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

AdvancedProfessional Cloud Data Engineer

A financial analytics firm is designing an infrastructure architecture on Google Cloud to handle two distinct analytical workloads: Workload A (Interactive Analytics): Financial analysts execute ad-hoc, low-latency SQL queries against multi-terabyte curated datasets to feed near-real-time executive dashboards, requiring consistent query response times and instant data recovery from accidental modifications within a 7-day window. Workload B (Scheduled Batch Processing): A nightly regulatory compliance pipeline executes complex, multistage Directed Acyclic Graph (DAG) transformations across large volumes of raw point-of-sale (POS) files in Cloud Storage, requiring custom Apache Spark transformations, cost-optimized ephemeral infrastructure, and automated orchestration. Which architectural pattern best meets the latency, fault tolerance, and operational requirements for both workloads?

Interactive or batch query jobs

AdvancedProfessional Cloud Data Engineer

Your enterprise data team is architecting a multi-step data processing pipeline on Google Cloud that orchestrates dependencies between long-running BigQuery batch transformation jobs and interactive analytical workloads. The design must satisfy the following technical and operational requirements: Orchestrate complex task dependencies, retries, and data transformations across hybrid services using Python. Prevent worker resource starvation and task timeouts while waiting for long-running BigQuery batch jobs to complete. Ensure end-to-end idempotency so that automated task retries or scheduled pipeline re-executions do not result in duplicate records or inconsistent states. Automatically capture and publish end-to-end data lineage to Dataplex Universal Catalog without requiring custom reporting code. Which orchestration and pipeline architecture should you implement?

Interactive or batch query jobs

AdvancedProfessional Cloud Data Engineer

Your organization is planning to migrate a legacy data warehouse to BigQuery. Before migrating, you need to perform a technical inventory of the source system to determine migration complexity and identify optimization opportunities. You have the following requirements for the assessment: Identify tables and databases that are frequently accessed together in a single query to understand data dependencies. Discover tables with no recent usage or writes to potentially exclude them from the migration and reduce storage costs. Identify low-latency queries that could be accelerated using BigQuery BI Engine. Which solution should you implement to achieve these requirements?

Analyzing current stakeholder needs, users, processes, and technologies, and creating a plan to get to desired state

AdvancedProfessional Cloud Data Engineer

An enterprise data lake team runs analytical queries against large data lake datasets in BigQuery. The team notices intermittent performance degradation and wants to establish proactive observability to identify bottlenecks and optimize compute resource allocation. They have the following operational requirements: Create an automated alerting policy in Cloud Monitoring that triggers when severe query latency outliers occur (specifically, when the 99th percentile of query execution times across a 5-minute rolling window exceeds 60 seconds). Track slot consumption alongside query execution metrics on a custom dashboard to evaluate whether to adjust capacity commitments and slot assignments. Which configuration should the team implement in Cloud Monitoring?

Monitoring the data lake

AdvancedProfessional Cloud Data Engineer

Your data engineering team manages cloud data infrastructure—including BigQuery datasets, Cloud Storage buckets, and Dataflow pipelines—across dev and prod environments on Google Cloud. You need to implement a continuous integration and continuous deployment (CI/CD) GitOps workflow using Terraform and Cloud Build that meets the following requirements: Provide a central, collaborative, and version-controlled mechanism for Terraform state management that prevents concurrency conflicts and state corruption. Automatically validate configurations and generate plan previews on proposed code changes before merging into environment branches. Enforce a strict multi-environment promotion sequence where changes are automatically applied only upon merging into the target environment branch. How should you structure your Terraform configuration, state storage, and Cloud Build triggers?

CI/CD (Continuous Integration and Continuous Deployment)

AdvancedProfessional Cloud DevOps Engineer

An enterprise DevOps team is configuring automated data migration from Microsoft Azure Blob Storage into Cloud Storage buckets. The organization's security policy enforces strict governance rules across all cloud environments: User-managed service account keys and static cloud credentials (such as Azure Shared Access Signatures or access keys) are strictly prohibited. Authentication between cloud providers must rely entirely on short-lived OpenID Connect (OIDC) tokens and keyless federated identities. The principle of least privilege must be maintained for all automated service identities. Which strategy should the team implement to fulfill these requirements for Storage Transfer Service?

Creating and managing service accounts

Professional Cloud Data Engineer

Optimize Bigquery Reservations And Query Workloads

Related questions

Professional Cloud Data Engineer

Optimize Bigquery Reservations And Query Workloads

What the Solution Is

How It Meets Workload Isolation and Baseline Requirements

How It Optimizes Autoscaling and Query Computation

Key Advantages

Why This Is Optimal

Related questions

What the Solution Is

How It Meets Workload Isolation and Baseline Requirements

How It Optimizes Autoscaling and Query Computation

Key Advantages

Why This Is Optimal