PDE Preparing and Using Data for Analysis Practice Question

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's exactly-once semantics by setting a stream name and offset.

  Why this is correct

  The Storage Write API provides exactly-once delivery semantics when you use a stream name and specify offsets for each record. This ensures that retried records are not duplicated. It is designed for high-throughput streaming and supports deduplication natively, making it the correct choice for preventing duplicates on retries.

• ✗

  Create a unique constraint on the destination table to reject duplicate rows.

  Why it's wrong here

  BigQuery does not support unique constraints or primary keys. While you can enforce uniqueness via application logic or deduplication queries, there is no native constraint that rejects duplicates on insert. This approach is not feasible in BigQuery and would not prevent duplicates during streaming.

• ✗

  Enable BigQuery's streaming inserts with insertId to deduplicate records.

  Why it's wrong here

  The legacy streaming API uses insertId for best-effort deduplication, but it is not guaranteed and is not supported in the Storage Write API. The Storage Write API uses a different mechanism for exactly-once semantics. Relying on insertId would not meet the requirement for guaranteed deduplication in this scenario.

• ✗

  Use a MERGE statement to upsert records after each streaming batch.

  Why it's wrong here

  MERGE statements are used for batch upserts and are not suitable for streaming inserts because they require running a query over existing data, adding latency and cost. They also do not prevent duplicates during the streaming write itself. For streaming deduplication, the Storage Write API's exactly-once feature is more appropriate.

Go deeper

Related to this question