Your organization runs multiple BigQuery batch jobs that load data from Cloud Storage, perform joins, and write results. Recently, you've noticed that some jobs fail intermittently with: 'Error: Cannot write to destination table, concurrent writes detected.' Which two approaches best resolve this without losing data or increasing latency?
- A. Use BigQuery MERGE with WHEN MATCHED/NOT MATCHED clauses to idempotently upsert data rather than overwriting, and serialize writes at the application level if necessary✓ Correct
- B. Enable BigQuery's concurrent slot reservation to prioritize write operations
- C. Use BigQuery table snapshots to create read-only copies before each load, preventing concurrent write conflicts
- D. Redesign the pipeline to write to separate intermediate tables per job, then use a final merge/consolidation step to combine results into the destination table✓ Correct
- E. Implement a distributed locking mechanism (e.g., using Firestore or Cloud Datastore) to serialize writes to the destination table across jobs
Explanation
Options 3 and 4 are correct. Option 3 isolates writes by having each job write to a unique intermediate table, then a controlled merge step consolidates results. This is the cleanest solution and doesn't require external coordination. Option 4 uses MERGE semantics, which are idempotent and work well if writes are properly sequenced. Option 1 (snapshots) is read-only and doesn't solve write conflicts. Option 2 (external locking) adds operational complexity and latency—if not carefully implemented, it can cause deadlocks. Option 5 mentions concurrent slot reservation, which doesn't exist or doesn't address write conflicts (slot reservations control query capacity, not write serialization).