PDE Designing Data Processing Systems Practice Question
A retail company runs a nightly batch pipeline that loads point-of-sale transactions into BigQuery. The pipeline uses a Cloud Composer (Apache Airflow) DAG with a BigQueryInsertJobOperator task that runs a SQL MERGE statement to upsert yesterday's sales into a large fact_sales table partitioned by transaction_date. The data engineering team notices that the MERGE task occasionally fails with a 'Resources exceeded during query execution' error when the source staging table contains more than 50 million rows. They need to redesign the DAG to reliably load large daily volumes without changing the final table schema or downstream dashboards. What should they do?
⚠ Common exam trap
The trap here is assuming that adding slots or clustering will fix a query that fails due to internal shuffle/memory limits, when the real fix is to decompose the MERGE into smaller, partition-scoped operations.
Answer choices
Why each option matters
Answer the question above first, then reveal the full breakdown to understand why each option is right or wrong.
Correct answer & explanation
✓
Replace the single MERGE statement with a multi-statement script that first deletes rows from the target partition for the affected dates and then inserts the staged rows using a BigQueryInsertJobOperator task with writeDisposition set to WRITE_APPEND.
A single large MERGE in BigQuery can hit internal resource limits because it must shuffle and join all source and target rows. Splitting the operation into a partition-scoped DELETE followed by an INSERT with WRITE_APPEND reduces the working set per statement and avoids the expensive shuffle. This pattern is a common, reliable way to upsert large daily batches while keeping the target schema and downstream queries unchanged.
Answer analysis
Option-by-option breakdown
For each option: why learners choose it and why it is or isn't the right answer here.
- ✓
Replace the single MERGE statement with a multi-statement script that first deletes rows from the target partition for the affected dates and then inserts the staged rows using a BigQueryInsertJobOperator task with writeDisposition set to WRITE_APPEND.
Why this is correct
This approach avoids the memory-intensive shuffle of a single large MERGE by splitting work into a bounded DELETE on a partition and a streaming-friendly INSERT. BigQuery can execute each statement with less resource contention, and WRITE_APPEND avoids rewriting the entire target table. It preserves the schema and downstream behavior while scaling to large daily volumes.
- ✗
Schedule the MERGE task to run during off-peak hours and increase the BigQuery slot reservation for the project so that the MERGE has more compute resources available.
Why it's wrong here
Adding slots can help with concurrency and throughput, but a single MERGE statement has internal limits on shuffle size and memory per stage. Off-peak scheduling does not change the query plan. If the MERGE exceeds those limits, more slots will not guarantee success; the statement itself must be decomposed or rewritten to avoid the resource bottleneck.
- ✗
Increase the number of Dataflow workers in the DAG by adding a DataflowOperator task that reads from the staging table and writes to the fact_sales table using a BigQueryIO write with STREAMING_INSERTS.
Why it's wrong here
Dataflow with STREAMING_INSERTS is designed for low-latency streaming, not bulk batch upserts. It incurs higher cost and does not provide atomic MERGE semantics for deduplication. Adding workers does not solve the MERGE resource error, and streaming inserts into a partitioned table would bypass the intended upsert logic, risking duplicate or stale rows in fact_sales.
- ✗
Convert the fact_sales table to a clustered table on transaction_date and re-run the original MERGE statement, relying on clustering to reduce the bytes scanned and avoid the resource error.
Why it's wrong here
Clustering improves pruning and query performance for filtered reads, but it does not reduce the shuffle or memory required by a large MERGE operation. The 'Resources exceeded' error stems from the join and update phase, not from scanning too many bytes. Clustering alone will not prevent the failure at 50 million rows.
Go deeper
Related to this question
About these practice questions
This PDE question is part of Courseiva's 747-question bank — original exam-style content with full explanations and wrong-answer analysis, never real exam questions or exam dumps. Learn why practice questions differ from exam dumps →
JA
Written and reviewed by Johnson Ajibi, MSc IT Security
Senior Network & Security Engineer · founder of Courseiva
Last reviewed September 2026 · checked against the official Google Cloud exam blueprint
This PDE practice question is part of Courseiva's free Google Cloud certification practice question bank. Courseiva provides original exam-style practice questions with explanations, topic-based practice, mock exams, readiness tracking, and study analytics to help learners prepare for the PDE exam.