Advanced
Prepare and test your skills
Advanced
Prepare and test your skills
Using BigQuery ELT Instead of Dataproc ETL
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 data analytics team is redesigning their batch processing architecture. Every night, multi-terabyte structured datasets from operational databases and Cloud Storage are ingested, requiring complex multi-table joins and analytical aggregations before being delivered to reporting dashboards in BigQuery.
The existing pipeline runs programmatic ETL transformations entirely on Apache Spark within Cloud Dataproc clusters. However, resource-intensive shuffle operations during large-scale joins frequently cause cluster memory bottlenecks and lead to inflated compute costs.
Which architectural approach should the team implement to optimize performance and reduce compute costs?
Explanations
Migrate the batch ETL pipeline to Cloud Dataflow using the STORAGE_API_AT_LEAST_ONCE write mode to stream joined records into BigQuery
Migrate the batch ETL pipeline to Cloud Dataflow using the STORAGE_API_AT_LEAST_ONCE write mode to stream joined records into BigQuery
What Dataflow with Storage Write API Is
Cloud Dataflow is a fully managed service running Apache Beam pipelines for unified batch and stream processing. The STORAGE_API_AT_LEAST_ONCE mode is an ingestion setting in the BigQuery Storage Write API designed for high-throughput, low-latency streaming writes where minor record duplication is tolerable.
Why It Doesn't Meet the Requirement
- Incorrect Delivery Semantics: Using
STORAGE_API_AT_LEAST_ONCEintroduces duplicate records into financial or reporting tables, corrupting analytical aggregations. - Does Not Optimize Relational Joins: Re-implementing multi-terabyte batch joins in Apache Beam on Dataflow still requires large distributed shuffle steps (
CoGroupByKey), retaining execution complexity. - Unnecessary Streaming Overhead: The scenario involves scheduled nightly batch processing, making low-latency streaming write modes an inefficient architectural fit.
Key Limitations
- Potential Data Duplication: At-least-once ingestion requires downstream deduplication pipelines, adding architectural complexity.
- Development Overhead: Rewriting existing Spark pipelines into Apache Beam Java or Python pipelines requires significant engineering refactoring without eliminating join shuffle constraints.
This Answer Would Be Correct If
The system required near-real-time streaming analytics from streaming endpoints via Pub/Sub where latency is prioritized over exact deduplication.
Additional Context
Dataflow's FILE_LOADS mode is the standard default for bounded batch collections writing to BigQuery, while STORAGE_WRITE_API provides exactly-once streaming.
Scale up the Cloud Dataproc cluster worker nodes to high-memory machine types and cache intermediate joined DataFrames in HDFS
Scale up the Cloud Dataproc cluster worker nodes to high-memory machine types and cache intermediate joined DataFrames in HDFS
What Scaling Dataproc Instances Is
This approach maintains the traditional programmatic ETL paradigm by expanding the physical infrastructure of the Cloud Dataproc cluster. It involves provisioning Compute Engine worker instances with larger RAM allocations (such as n2-highmem series) and persisting intermediate Spark resilient distributed datasets or DataFrames across HDFS local disks.
Why It Doesn't Meet the Requirement
- Cost Inefficiency: Scaling up Dataproc worker memory across a multi-terabyte processing pipeline significantly inflates infrastructure costs, directly contradicting the cost-optimization objective.
- Persistent Operational Burden: The team remains responsible for cluster sizing, YARN memory tuning, garbage collection configuration, and HDFS replication factor management.
- Failure to Leverage Managed Services: It fails to exploit BigQuery's native, highly parallel SQL execution capabilities for relational joins and aggregations.
Key Limitations
- High Infrastructure Footprint: Requires over-provisioning worker capacity to handle peak memory spikes during large-scale shuffle operations.
- Data Duplication: Persisting intermediate tables in HDFS duplicates data that is already destined for long-term BigQuery storage.
- Suboptimal Resource Utilization: Dataproc worker resources remain idle or underutilized during non-peak execution phases.
This Answer Would Be Correct If
The workload required non-relational, low-level programmatic libraries, specialized machine learning model training in PySpark, or custom binary C++ graph algorithms unsupported natively by BigQuery SQL.
Additional Context
While resizing Dataproc clusters is supported, vertically scaling Spark clusters to solve large relational join bottlenecks is an anti-pattern on Google Cloud when a serverless data warehouse like BigQuery is the target destination.
Stash intermediate raw records in Cloud Spanner and execute transformations using Spanner-to-Cloud Storage templates before loading into BigQuery
Stash intermediate raw records in Cloud Spanner and execute transformations using Spanner-to-Cloud Storage templates before loading into BigQuery
What Cloud Spanner Staging Is
This approach uses Cloud Spanner, a globally distributed, strongly consistent relational OLTP database, as an intermediate staging and transformation layer. It uses Dataproc Serverless Spanner-to-Cloud Storage batch templates to extract transformed datasets before final loading into BigQuery.
Why It Doesn't Meet the Requirement
- Massive Cost Overruns: Cloud Spanner is engineered and priced for high-availability mission-critical OLTP transactions, making it extremely cost-prohibitive as a multi-terabyte staging and batch transformation engine.
- Unnecessary Architectural Complexity: Ingesting into Spanner, transforming, exporting to Cloud Storage, and then loading into BigQuery introduces multiple redundant I/O hops.
- Poor Analytical Performance: OLTP databases like Spanner are not optimized for heavy analytical multi-table batch joins compared to columnar analytical engines like BigQuery.
Key Limitations
- Excessive Network and Storage I/O: Staging data across multiple disparate managed database services increases latency and egress costs.
- Complex Pipeline Orchestration: Requires managing Spanner node provisioning alongside batch extraction jobs.
This Answer Would Be Correct If
The enterprise required a global, horizontally scalable transactional database to process concurrent ACID OLTP transactions before archiving data to an analytical warehouse.
Additional Context
Dataproc batch templates facilitate point-to-point data movements between storage services, but routing analytical batch workloads through an OLTP database violates Google Cloud architectural best practices.
Adopt an ELT design pattern by loading raw datasets directly into BigQuery staging tables and executing heavy joins and transformations declaratively using BigQuery SQL
Adopt an ELT design pattern by loading raw datasets directly into BigQuery staging tables and executing heavy joins and transformations declaratively using BigQuery SQL
What the ELT Design Pattern Is
An Extract, Load, Transform (ELT) architecture shifts data transformation from external compute engines (such as Spark on Cloud Dataproc) to the target data warehouse engine (BigQuery). Instead of transforming multi-terabyte datasets in memory before loading, raw structured records are ingested directly into BigQuery staging tables, where transformations and analytical joins execute natively using BigQuery SQL.
How It Meets the Requirements
- Optimizing Join Performance: BigQuery uses distributed Dremel execution engines and an internal Petabit-scale Jupiter network to execute large multi-table joins and distributed aggregations substantially faster than traditional cluster-based memory shuffles.
- Reducing Compute Costs: Shifting resource-heavy relational joins into BigQuery eliminates the need to maintain large, high-memory Dataproc worker nodes. Compute resources scale automatically with query demand rather than incurring continuous cluster provisioning expenses.
- Addressing Memory Bottlenecks: Offloading complex transformation logic natively leverages BigQuery's managed execution slots and storage optimization, avoiding Spark
OutOfMemoryexceptions and cluster tuning overhead.
Key Advantages and Capabilities
- Serverless Scaling: Eliminates operational overhead and cluster management by automatically allocating slot capacity for massive joins.
- Storage and Compute Decoupling: Allows ingestion of raw data directly into managed storage without provisioning intermediate transformation compute nodes.
- Declarative SQL Simplicity: Leverages ANSI SQL and built-in analytical window functions rather than complex programmatic Spark DAG implementations.
- Cost-Efficiency: Enables the use of smaller, ephemeral Dataproc clusters (or direct BigQuery load jobs) since heavy transformation workloads execute inside BigQuery.
Why This Is Optimal
When workloads consist primarily of SQL-expressible transformations and joins on structured datasets, declarative ELT in BigQuery offers superior throughput and lower total cost of ownership compared to programmatic ETL on Dataproc, directly removing Spark shuffle bottlenecks.
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
TriggerDagRunOperator with TaskGroup vs SubDAG
Defining data transformation and orchestration logic
AdvancedProfessional Data Engineer
Cloud Spanner INSERT_OR_UPDATE Idempotent Writes
Defining data transformation and orchestration logic
AdvancedProfessional Data Engineer
Dataflow Session Windows Allowed Lateness
Streaming (e.g., windowing, late arriving data)
AdvancedProfessional Data Engineer
CMEK Organization Policy Constraints
Legal and regulatory compliance
AdvancedProfessional Data Engineer
Spanner Normalization vs BigQuery Denormalization
Deciding the degree of data normalization
FundamentalAssociate Data Practitioner
LookML Sum Measure
Manipulate simple LookML parameters to modify a data model
Last updated Validated