Advanced
Prepare and test your skills
Advanced
Prepare and test your skills
Matching Interactive and Batch Workloads to BigQuery and Dataproc
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 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?
Explanations
Serve Workload A directly from BigQuery using its built-in Multiversion Concurrency Control (MVCC) and time travel; orchestrate Workload B using Cloud Composer to provision ephemeral Dataproc clusters that execute Spark jobs against Cloud Storage.
Serve Workload A directly from BigQuery using its built-in Multiversion Concurrency Control (MVCC) and time travel; orchestrate Workload B using Cloud Composer to provision ephemeral Dataproc clusters that execute Spark jobs against Cloud Storage.
What this architecture is
This architecture pairs BigQuery as an interactive, serverless analytical engine with Cloud Composer and Dataproc for automated, cost-optimized batch data processing.
How it meets the requirements
- Workload A (Interactive Analytics): BigQuery separates compute and storage to deliver scalable, low-latency SQL query execution for ad-hoc requests and live dashboards. Its native Multiversion Concurrency Control (MVCC) architecture enables built-in time travel, allowing analysts to seamlessly query and recover data from any point in time within the past 7 days without provisioning external backup infrastructure.
- Workload B (Batch Processing): Cloud Composer manages workflow execution via Directed Acyclic Graphs (DAGs) to automate the lifecycle of ephemeral Dataproc clusters. These clusters spin up on demand, execute distributed Apache Spark batch transformations directly on data staged in Cloud Storage, and terminate upon completion to minimize infrastructure costs.
Key advantages
- Predictable Interactive Latency: BigQuery dynamically assigns compute capacity to provide responsive, highly concurrent interactive queries for dashboards.
- Built-in Fault Tolerance and Self-Service Recovery: BigQuery's 7-day time travel window protects against unintended data corruption or accidental deletions.
- Cost-Optimized Batch Execution: Ephemeral Dataproc clusters ensure compute resources are only billed for the exact duration of the nightly Spark job.
- Decoupled Storage and Compute: Using Cloud Storage as the primary data lake repository for Dataproc avoids persistent cluster storage overhead.
Why this is optimal
This approach aligns each workload with the compute engine engineered for its execution profile: BigQuery optimizes for interactive SQL latency and metadata-level data recovery, while ephemeral Dataproc orchestrated by Cloud Composer provides robust, cost-efficient batch compute for legacy or complex Spark frameworks.
Serve Workload A using BigLake external tables querying raw files in Cloud Storage; orchestrate Workload B using Cloud Run functions triggered by Pub/Sub messages.
Serve Workload A using BigLake external tables querying raw files in Cloud Storage; orchestrate Workload B using Cloud Run functions triggered by Pub/Sub messages.
What this option is
This approach uses BigLake to query unstructured and semi-structured files directly in Cloud Storage for interactive dashboards, while using event-driven Cloud Run functions triggered via Pub/Sub to process the nightly batch data.
Why it doesn't meet the requirements
- Workload A: Querying external files in Cloud Storage via BigLake typically yields higher and less predictable query latency than native BigQuery managed storage. External Cloud Storage tables also do not support BigQuery's 7-day MVCC time travel features.
- Workload B: Cloud Run functions are lightweight, serverless compute runtimes meant for event processing and microservices; they cannot execute large-scale, distributed Apache Spark batch jobs over multi-terabyte datasets due to memory, execution timeout, and horizontal distributed computing constraints.
Key limitations
- Execution Limits: Cloud Run functions have strict timeout limits and cannot coordinate distributed data processing graphs across worker nodes.
- Performance Penalty: External file scans on Cloud Storage lack the columnar indexing and metadata caching benefits of native BigQuery storage.
- No Time Travel: Point-in-time time travel is an exclusive feature of native BigQuery managed tables.
This answer would be correct if
Workload A only required occasional queries over open-format external data lake files, and Workload B consisted of lightweight, individual file-by-file validation checks rather than distributed Spark transformations.
Serve Workload A from a persistent Dataproc cluster running Spark SQL over HDFS; execute Workload B using BigQuery batch query jobs scheduled with Cloud Scheduler.
Serve Workload A from a persistent Dataproc cluster running Spark SQL over HDFS; execute Workload B using BigQuery batch query jobs scheduled with Cloud Scheduler.
What this option is
This option deploys a long-running, 24/7 Dataproc cluster utilizing Spark SQL and the Hadoop Distributed File System (HDFS) for interactive querying, while offloading nightly batch processing to scheduled BigQuery batch jobs.
Why it doesn't meet the requirements
- Workload A: Maintaining a persistent Dataproc cluster running Spark SQL introduces substantial operational overhead, cluster administration duties, and idle infrastructure costs. HDFS does not natively provide automatic 7-day multi-version time travel for point-in-time recovery without custom versioning scripts.
- Workload B: The compliance pipeline explicitly requires custom Apache Spark transformations and complex multi-step DAG dependencies, which cannot be executed natively inside standard BigQuery SQL batch queries.
Key limitations
- High Idle Costs: Persistent Dataproc instances incur continuous Compute Engine charges regardless of query activity.
- Operational Burden: Administrators must manually size, scale, patch, and manage YARN resource allocation.
- Incompatible Engine: BigQuery SQL cannot natively run custom open-source Spark JARs or PySpark scripts without translation.
This answer would be correct if
The organization had an existing interactive BI tool tightly coupled to the Hive Metastore/Hadoop ecosystem and simple SQL-only transformations for nightly compliance reporting.
Execute both Workload A and Workload B directly inside a high-performance Google Cloud Managed Lustre parallel file system attached to Compute Engine managed instance groups (MIGs).
Execute both Workload A and Workload B directly inside a high-performance Google Cloud Managed Lustre parallel file system attached to Compute Engine managed instance groups (MIGs).
What this option is
This architecture leverages Google Cloud Managed Lustre, a high-performance parallel file system designed for tightly coupled High Performance Computing (HPC) and Message Passing Interface (MPI) applications, attached to Compute Engine virtual machines in a Managed Instance Group (MIG).
Why it doesn't meet the requirements
- Workload A: Lustre is a POSIX-compliant distributed file system tailored for HPC storage bandwidth rather than an interactive SQL query engine with built-in multi-version concurrency and query optimization for dashboards.
- Workload B: While Lustre provides sub-millisecond storage access for tightly coupled HPC simulations, it lacks managed Spark cluster orchestration, built-in workflow scheduling, and serverless job lifecycle features.
Key limitations
- Architectural Mismatch: Lustre is optimized for HPC modeling, seismic analysis, and distributed AI training—not relational data warehousing or SQL analytics.
- Management Complexity: Building custom SQL query layers and distributed Spark execution on raw Compute Engine MIGs requires immense manual software engineering.
- Missing Features: Lacks automated 7-day time travel restoration out of the box.
This answer would be correct if
The workload consisted of tightly coupled, multi-node scientific simulations (such as weather modeling or computational fluid dynamics) requiring MPI inter-process communication and POSIX read-after-write consistency.
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 Reservations Workload Isolation
Interactive or batch query jobs
AdvancedProfessional Data Engineer
Cloud Composer Deferrable Operators
Interactive or batch query jobs
AdvancedProfessional Data Engineer
BigQuery Dynamic Data Masking with Policy Tags
Multi-environment use cases (development vs. production)
AdvancedProfessional Data Engineer
BigQuery VPC Service Controls RLS Dynamic Data Masking
Identity and Access Management (e.g., Cloud IAM and organization policies)
AdvancedProfessional Data Engineer
Service Account Impersonation
Identity and Access Management (e.g., Cloud IAM and organization policies)
FundamentalAssociate Data Practitioner
Structured Data Cloud SQL BigQuery
Classify use cases into having structured, unstructured, or semi-structured data requirements
Last updated Validated