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?

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.

Bigquery Dataproc Workload Architecture Selection | professional-cloud-data-engineer

Bigquery Dataproc Workload Architecture Selection

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.

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?

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).

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 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 using BigLake external tables querying raw files in Cloud Storage; orchestrate Workload B using Cloud Run functions triggered by Pub/Sub messages.

Professional Cloud Data Engineer

Bigquery Dataproc Workload Architecture Selection

Related questions