Advanced
Prepare and test your skills
Advanced
Prepare and test your skills
Exactly-Once BigQuery Streaming with Dead-Letter Handling
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 financial organization is designing an automated ingestion and processing architecture to stream high-volume transaction records from Pub/Sub into BigQuery. The architecture must satisfy the following technical requirements:
- Ingest records directly into BigQuery storage while strictly guaranteeing exactly-once processing semantics.
- Isolate and extract individual malformed or schema-mismatched rows into a dead-letter collection without failing the entire streaming pipeline.
- Maintain high throughput without relying on legacy streaming buffer inserts.
Which ingestion pathway and processing approach should the organization implement?
Explanations
Deploy an Apache Beam pipeline on Dataflow using the BigQueryIO connector configured with the STORAGE_WRITE_API method, and invoke getFailedStorageApiInserts on the WriteResult to route invalid rows to a dead-letter sink.
Deploy an Apache Beam pipeline on Dataflow using the BigQueryIO connector configured with the STORAGE_WRITE_API method, and invoke getFailedStorageApiInserts on the WriteResult to route invalid rows to a dead-letter sink.
What the BigQuery Storage Write API with Dataflow is
The BigQuery Storage Write API is a unified, high-performance streaming and batch ingestion API integrated into Apache Beam's BigQueryIO connector in Dataflow. It provides low-latency streaming directly to BigQuery storage while enforcing strict transactional boundaries.
How it meets the requirements
- Guarantees exactly-once semantics: The
STORAGE_WRITE_APIwrite method uses stream offset tracking and stream commits to guarantee that records are committed to BigQuery tables exactly once, avoiding duplicate records. - Handles row-level errors: Any individual rows that cannot be appended (such as schema mismatches or invalid types) are output to a separate
PCollectionaccessed viagetFailedStorageApiInserts(). This allows the pipeline to cleanly route bad records to a dead-letter queue or error table without causing the main streaming job to fail. - Maintains high throughput: It streams directly to BigQuery managed storage with high efficiency, eliminating the limitations and quotas of legacy streaming inserts.
Key advantages and capabilities
- Unified API: Combines the benefits of streaming inserts and batch file loads into a single protocol.
- Row-level dead-lettering: Enables granular isolation of corrupt payloads without halting continuous stream processing.
- Automatic resource optimization: Scales throughput efficiently across Dataflow worker threads and slots.
- Modern protocol: Uses gRPC and protocol buffers for compact serialization and optimal network utilization.
Why this is optimal
Alternative ingestion approaches either cannot guarantee exactly-once processing (such as at-least-once modes), cause entire batch load failures upon a single row error (such as file loads), or incur extra operational latency. Using STORAGE_WRITE_API in BigQueryIO achieves exact-once reliability and fine-grained error isolation natively within the pipeline.
Deploy a Cloud Data Fusion pipeline with Transformation Pushdown enabled to stage records in Cloud Storage and push SQL join transformations to BigQuery.
Deploy a Cloud Data Fusion pipeline with Transformation Pushdown enabled to stage records in Cloud Storage and push SQL join transformations to BigQuery.
What Cloud Data Fusion Transformation Pushdown is
Transformation Pushdown in Cloud Data Fusion is an optimization mechanism that offloads resource-heavy pipeline stages (such as relational JOIN operations) from Spark on Dataproc to BigQuery's SQL query engine.
Why it doesn't meet the requirement
- Focuses on transformation execution, not streaming ingestion: Transformation pushdown is an execution optimization for ELT/ETL batch workflows moving data between Spark and BigQuery SQL, rather than a real-time event ingestion mechanism.
- Lacks native streaming row-level dead-letter collection: It relies on staging files in Cloud Storage and BigQuery load jobs, meaning malformed data causes batch stage failures rather than granular row routing via
WriteResult.
Key characteristics and limitations
- Optimizes compute costs: Allows Dataproc clusters to remain smaller by delegating heavy joins and aggregations to BigQuery.
- Staging overhead: Loads datasets into BigQuery via Cloud Storage load jobs before running SQL pushdown queries.
- Spark execution engine: Underlying stages run inside Spark rather than providing native Apache Beam streaming I/O controls.
This answer would be correct if
The goal was to build a graphical batch integration pipeline where heavy SQL transformations and table joins were causing high memory consumption on a Spark/Dataproc cluster.
Additional context
Transformation Pushdown operates at the query and stage level, making it unsuitable for solving stream-level record triage and high-throughput real-time streaming ingestion.
Deploy an Apache Beam batch pipeline on Dataflow using the BigQueryIO connector configured with the FILE_LOADS method, staging intermediate files in Cloud Storage.
Deploy an Apache Beam batch pipeline on Dataflow using the BigQueryIO connector configured with the FILE_LOADS method, staging intermediate files in Cloud Storage.
What the FILE_LOADS write method is
The FILE_LOADS method buffers incoming data into temporary staging files in Cloud Storage and subsequently executes an asynchronous BigQuery load job to import the files.
Why it doesn't meet the requirement
- Lacks row-level error isolation: When a BigQuery load job encounters a malformed row or fatal schema mismatch during execution, the entire load job fails and throws a runtime exception in Dataflow. It does not return individual failed rows to a separate
PCollectionfor dead-lettering. - Not suited for real-time streaming:
FILE_LOADSis intended primarily for bounded batchPCollectionsand introduces batching latency while writing and loading staging files.
Key characteristics and limitations
- Default for batch pipelines: The standard approach for loading large bounded batch datasets using free shared slot pools.
- All-or-nothing job execution: A failure in any part of the staged file fails the entire ingestion load job.
- No dead-letter PCollection: The connector does not produce row-level error collections when a load job fails.
This answer would be correct if
You were executing a scheduled, non-latency-sensitive batch ingestion job where staging Parquet or Avro files in Cloud Storage is standard and whole-job retries are acceptable upon failure.
Additional context
Because BigQuery load jobs fail atomically when an unparseable row is encountered, batch file loading cannot provide continuous streaming error triage.
Deploy an Apache Beam pipeline on Dataflow using the BigQueryIO connector configured with the STORAGE_API_AT_LEAST_ONCE method, and route write retries to an error table.
Deploy an Apache Beam pipeline on Dataflow using the BigQueryIO connector configured with the STORAGE_API_AT_LEAST_ONCE method, and route write retries to an error table.
What the Storage API At-Least-Once mode is
The STORAGE_API_AT_LEAST_ONCE write method in Apache Beam's BigQueryIO uses the BigQuery Storage Write API in a low-latency mode that does not commit streams with transactional offset tracking.
Why it doesn't meet the requirement
- Fails exactly-once guarantee:
STORAGE_API_AT_LEAST_ONCEexplicitly provides at-least-once delivery semantics. In the event of transient network issues, worker restarts, or pipeline retries, duplicate rows can be committed to the target table. - Misaligned error strategy: Retrying failed writes blindly does not isolate schema-invalid records from valid records.
Key characteristics and limitations
- Lower latency: Reduces write latency slightly compared to exactly-once stream management.
- Risk of duplicates: Allows duplicate entries whenever streams or worker connections are re-established.
- Cost efficiency for tolerant workloads: Recommended only when downstream consumers or queries perform their own deduplication.
This answer would be correct if
The scenario prioritized absolute lowest streaming latency and lowest cost while explicitly tolerating duplicate records or performing deduplication during downstream transformation.
Additional context
For financial transaction data and strict compliance use cases, duplicate ingestion is unacceptable, making STORAGE_WRITE_API (exactly-once) the required method.
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
Pub/Sub Ordering Keys
Identifying the services (e.g., Dataflow, Apache Beam, Dataproc, Cloud Data Fusion, BigQuery, Pub/Sub, Apache Spark, Hadoop ecosystem, and Apache Kafka)
AdvancedProfessional Data Engineer
Dataproc vs Dataflow vs Data Fusion
Identifying the services (e.g., Dataflow, Apache Beam, Dataproc, Cloud Data Fusion, BigQuery, Pub/Sub, Apache Spark, Hadoop ecosystem, and Apache Kafka)
AdvancedProfessional Data Engineer
CMEK Organization Policy Constraints
Legal and regulatory compliance
AdvancedProfessional Data Engineer
Resource Location Restriction Non-Retroactive
Regional considerations (data sovereignty) for data access and storage
AdvancedProfessional Data Engineer
Federated Shared Responsibility Model
Building a federated governance model for distributed data systems
FundamentalAssociate Data Practitioner
BigQuery ML Mean Absolute Error
Execute SQL to create, train, and evaluate models using BigQuery ML
Last updated Validated