PDE Preparing and Using Data for Analysis Practice Question
You are ingesting streaming data into BigQuery using the Storage Write API. The data arrives with occasional duplicate records due to at-least-once delivery semantics from the source system. You need to ensure that the final BigQuery table contains no duplicates based on a unique event_id column. Which approach should you use?
⚠ Common exam trap
The trap here is assuming that the Storage Write API's default stream or a unique constraint can provide deduplication, when in fact exactly-once requires application-created streams with offsets.
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
✓
Use the Storage Write API with application-created streams and specify a unique offset for each record to enable exactly-once semantics.
To achieve exactly-once semantics with the Storage Write API, you must use application-created streams and assign a unique offset to each record. This allows BigQuery to deduplicate records on write, ensuring no duplicates even with retries. The default stream does not provide exactly-once semantics, and BigQuery lacks unique constraints. MERGE is a batch alternative but not ideal for streaming.
Answer analysis
Option-by-option breakdown
For each option: why learners choose it and why it is or isn't the right answer here.
- ✓
Use the Storage Write API with application-created streams and specify a unique offset for each record to enable exactly-once semantics.
Why this is correct
The Storage Write API supports exactly-once semantics when using application-created streams. By assigning a unique offset to each record within a stream, BigQuery can deduplicate records on write. This ensures that even if the client retries, duplicates are not inserted. This is the recommended approach for streaming ingestion with deduplication requirements. It provides low-latency, exactly-once delivery without additional batch processing.
- ✗
Enable the exactly-once delivery semantics in the Storage Write API by using the default stream and specifying a unique offset for each record.
Why it's wrong here
The Storage Write API's default stream does not support exactly-once semantics; it provides at-least-once delivery. To achieve exactly-once, you must use the application-created streams with client-side deduplication or use the committed stream with offsets. Simply using the default stream with a unique offset does not guarantee deduplication. This option misstates the API's capabilities.
- ✗
Use BigQuery's MERGE statement to upsert records into the table based on event_id, running it periodically to deduplicate.
Why it's wrong here
MERGE is a batch operation that can deduplicate data, but it requires running periodic jobs and may introduce latency. For streaming ingestion, this approach is not real-time and adds complexity. It also does not prevent duplicates from being written initially, so the table may temporarily contain duplicates. While MERGE can be part of a solution, it is not the most efficient or recommended method for streaming deduplication.
- ✗
Create a unique constraint on the event_id column in the BigQuery table to automatically reject duplicates.
Why it's wrong here
BigQuery does not support unique constraints or primary keys. While you can define primary keys and foreign keys in some contexts, they are not enforced. Therefore, you cannot rely on a unique constraint to reject duplicates. This option is invalid for BigQuery. Deduplication must be handled via the ingestion method or downstream processing.
Go deeper
Related to this question
About these practice questions
One of 747 original PDE practice questions on Courseiva, each with a full explanation and wrong-answer analysis — not exam dumps or protected exam content. 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.