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?

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_API` write 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 `PCollection` accessed via `getFailedStorageApiInserts()`. 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.

Dataflow Bigquery Storage Write Api Ingestion | professional-cloud-data-engineer

Dataflow Bigquery Storage Write Api Ingestion

Worked example. The correct answer is already marked and every option is explained below, so there is nothing to select here. To answer questions yourself, start the free trial.

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?

Deploy a Cloud Data Fusion pipeline with Transformation Pushdown enabled to stage records in Cloud Storage and push SQL join transformations to BigQuery.

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 batch pipeline on Dataflow using the BigQueryIO connector configured with the FILE_LOADS method, staging intermediate files in Cloud Storage.

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.

Professional Cloud Data Engineer

Dataflow Bigquery Storage Write Api Ingestion

Related questions