Study guide

3.2 Streaming Ingestion & Deduplication Patterns

Google Cloud Associate Data Practitioner Exam

Track your progress as you read

Finished sections get a check mark.

1 Introduction & Exam Overview

About the Associate Data Practitioner Exam

2 Chapter 1: Data Processing Paradigms & Ingestion Strategies

1.1 ETL, ELT, and ETLT Transformation Paradigms 1.2 Data Transfer and Migration Tools 1.3 Data Quality, Cleaning, and Validation

3 Chapter 2: Data Formats & Storage Solution Selection

2.1 File Formats & Serialization Characteristics 2.2 Cloud Storage Buckets, Location Types & Storage Classes 2.3 Selecting Google Cloud Databases and Storage Services

4 Chapter 3: Loading Data & Modern Ingestion Architectures

3.1 Batch Loading Data into BigQuery 3.2 Streaming Ingestion & Deduplication Patterns 3.3 BigQuery External Tables, BigLake & Federated Queries 3.4 Schema Definition & Evolution for Loaded Data

5 Chapter 4: BigQuery SQL Analytics & Query Foundations

4.1 BigQuery SQL Syntax & Analytical Functions 4.2 Complex Data Types: Arrays, Structs, and JSON 4.3 Answering Business Questions with BigQuery: Worked Analyses

6 Chapter 5: BigQuery Performance Optimization & Cost Governance

5.1 Table Partitioning Strategies 5.2 Table Clustering & Data Skipping 5.3 Query Optimization, Execution Plans, and Cost Controls

7 Chapter 6: Interactive Analysis, Notebooks & Visual Presentation

6.1 Exploratory Analysis with Jupyter & Colab Enterprise 6.2 Looker Studio (Data Studio) Reporting & Dashboard Design 6.3 Enterprise BI with Looker & LookML Foundations 6.4 Building, Modifying & Sharing Looker Dashboards

8 Chapter 7: Machine Learning with BigQuery ML & Vertex AI

7.1 Planning an ML Project: BigQuery ML, AutoML, or Pretrained Models? 7.2 Creating, Training & Evaluating Models with BigQuery ML 7.3 Running Inference and Organizing Models in Model Registry 7.4 Using Pretrained Gemini Models from BigQuery with Remote Connections

9 Chapter 8: Pipeline Engines & Transformation Services

8.1 Transformation Engine Selection Matrix 8.2 Apache Beam & Cloud Dataflow Core Concepts 8.3 Stream Processing: Windowing, Watermarks, and Triggers

10 Chapter 9: Workflow Orchestration, Automation & Monitoring

9.1 SQL Pipelines with Dataform & Scheduled Queries 9.2 Workflow Orchestration with Cloud Composer & Workflows 9.3 Event-Driven Ingestion: Pub/Sub to BigQuery & Eventarc Triggers 9.4 Monitoring Pipelines: Dataflow Job UI, Cloud Logging & Cloud Monitoring

11 Chapter 10: Access Control, Governance & Data Sharing

10.1 IAM Principles & Role Hierarchies for Data Services 10.2 Cloud Storage Security & Access Controls 10.3 Fine-Grained Security: Column-Level & Row-Level Controls 10.4 Secure Data Sharing with Analytics Hub (BigQuery Sharing)

12 Chapter 11: Lifecycle Management, Reliability & Compliance

11.1 Object Lifecycle Management & Storage Tiering 11.2 High Availability & Disaster Recovery Architectures 11.3 Data Retention, Time Travel, and Point-in-Time Recovery 11.4 Encryption Key Management & Privacy Compliance

3.2 Streaming Ingestion & Deduplication Patterns

Key Takeaways Explain This Topic AI

The modern BigQuery Storage Write API utilizes gRPC and Protocol Buffers to deliver high-throughput, low-latency streaming ingestion with exactly-once semantics at roughly half the price of legacy streaming.

Pending stream mode provides ACID transactions across multiple worker streams, committing rows atomically only when the finalized streams are committed together with BatchCommitWriteStreams.

Streamed rows are queryable immediately; rows from the legacy insertAll API can't be changed by DML for about 30 minutes (Storage Write API rows can), and copies can lag up to 90 minutes.

Legacy insertId deduplication is best-effort and only valid within an ephemeral 1-minute cache window, making it inadequate for strict financial or audit requirements.

Robust enterprise deduplication relies on two-step ELT architectures pairing append-only streaming landing tables with scheduled MERGE queries or analytical window QUALIFY clauses.

Last updated: October 2026

3.2 Streaming Ingestion & Deduplication Patterns

While batch loading is the most cost-effective solution for periodic data movement, modern digital enterprises increasingly operate on streaming data. Telemetry pipelines, IoT device networks, financial trading systems, and real-time fraud detection engines require data to be ingested and analyzed within seconds or milliseconds of generation. In Google Cloud, BigQuery provides native real-time ingestion capabilities capable of ingesting millions of rows per second directly into analytical tables.

1. Evolution of Streaming Ingestion: Storage Write API vs. Legacy Streaming

Historically, streaming records into BigQuery required calling the legacy tabledata.insertAll REST API. While functional, the legacy API suffered from significant architectural bottlenecks: +-----------------------------------------------------------------------------------------+

  • | STREAMING INGESTION ARCHITECTURE COMPARISON |
  • +-----------------------------------------------------------------------------------------+
  • | Feature | Legacy tabledata.insertAll | Storage Write API |
  • +-------------------------+----------------------------------+----------------------------+
  • | Protocol & Encoding | HTTP/1.1 REST with JSON strings | HTTP/2 gRPC with Protobuf |
  • | Connection Overhead | High (per-request handshakes) | Multiplexed streaming |
  • | Delivery Semantics | Best-effort (insertId) | Guaranteed Exactly-Once |
  • | Transactional Support | None (per-row immediate commit) | Multi-stream Two-Phase ACID|
  • | DML on just-written rows | Blocked for ~30 minutes | Allowed (gRPC API) |
  • | Base Pricing | $0.050 per GB ingested | $0.025 per GB ingested |
  • | Free Ingestion Tier | None | 2 TB per month free |
  • +-----------------------------------------------------------------------------------------+

The BigQuery Storage Write API represents a complete redesign of BigQuery's ingestion layer. By utilizing gRPC streaming and binary Protocol Buffers (Protobuf) , it minimizes network serialization overhead, dramatically increases throughput per connection, and slashes per-gigabyte ingestion costs by 50% compared to legacy streaming.

2. Storage Write API Stream Modes & Delivery Semantics

The Storage Write API supports different stream types to accommodate diverse consistency and latency requirements:

Loading diagram...

1. Default Stream

The Default stream mode is optimized for high-volume streaming where sub-second record availability is paramount and at-least-once delivery is acceptable (such as clickstream tracking or operational system metrics). Records appended to the default stream are immediately committed to the destination table and become queryable instantly. The default stream does not require client-side stream creation or management, functioning as a shared pipe directly into the table.

2. Committed Stream

The Committed stream mode provides strictly guaranteed exactly-once semantics . When writing to a committed stream, the client explicitly provides a monotonic row offset with each batch. If a network interruption occurs and the client retries an append request with an offset that BigQuery has already committed, BigQuery recognizes the offset duplicate and acknowledges the write without appending duplicate rows.

3. Pending Stream

The Pending stream mode brings ACID transactional guarantees to streaming data warehouse ingestion. In this mode:

A client application creates one or more pending streams attached to a target table.

Worker threads write records to their respective streams. Data is staged in an isolated, uncommitted buffer that is completely invisible to concurrent table queries.

Each stream is finalized with FinalizeWriteStream , and the client then issues one BatchCommitWriteStreams call covering all of them.

All staged records across the multiple streams are atomically committed to the table in a single transaction. If any worker fails or the pipeline aborts, the pending streams expire, leaving the destination table unmodified.

4. Buffered Stream

The Buffered stream mode allows clients to write records with stream offsets and subsequently execute flush operations, providing a hybrid balance between committed stream durability and batch flushing.

3. Streaming Buffer Mechanics & Operational Constraints

When records arrive via streaming ingestion (either Storage Write API or legacy insertAll ), they do not write directly into BigQuery's permanent, read-optimized Capacitor columnar files on Colossus storage. Writing tiny micro-batches directly to columnar files would result in millions of fragmented, uncompressed storage shards, degrading analytical query performance.

Instead, BigQuery routes incoming streaming rows into an in-memory, highly replicated Streaming Buffer : +-----------------------------------------------------------------------------------+

  • | STREAMING BUFFER LIFECYCLE |
  • +-----------------------------------------------------------------------------------+
  • | 1. Ingestion: Records append to transient in-memory Streaming Buffer in ms |
  • | 2. Querying: SELECT queries scan persistent columnar files + streaming buffer |
  • | 3. Extraction: Background workers extract, sort, and compress buffered rows |
  • | 4. Flush: Data flushes to permanent Capacitor columnar storage (1 - 90 min) |
  • +-----------------------------------------------------------------------------------+
  • Critical Operational Restrictions While Data is in the Streaming Buffer

While rows reside in the transient streaming buffer, BigQuery enforces several strict operational constraints that frequently appear on certification exams:

DML on recent legacy-streamed rows: Rows written with the legacy tabledata.insertAll API during roughly the last 30 minutes cannot be changed with UPDATE , DELETE , MERGE , or TRUNCATE ; the statement fails. Rows written with the Storage Write API (gRPC) can be modified with DML right away.

Copy delays: Streamed data can take up to 90 minutes to become available for copy operations.

Queries are immediate: Streamed rows are visible to SELECT queries as soon as the write succeeds; the background conversion to optimized columnar storage is invisible to queries.

4. Enterprise Deduplication Architectures

In distributed stream processing, the network is fundamentally unreliable. Retries caused by client timeouts, load balancer restarts, or transient network blips mean that upstream producers frequently transmit duplicate event payloads.

The Flaw of Legacy insertId Deduplication

In the legacy tabledata.insertAll API, developers supplied an insertId string with each row, expecting BigQuery to deduplicate incoming data. However, Google Cloud documentation clearly states that insertId deduplication is best-effort only :

BigQuery maintains insertId hashes in an ephemeral in-memory cache with an expiration window of approximately 1 minute .

If an upstream pipeline suffers a backoff delay, network timeout, or queue retry that re-submits a record 65 seconds after the original attempt, the cache entry has already expired. BigQuery accepts the record a second time, resulting in silent data duplication.

Consequently, relying on insertId for critical financial, healthcare, or auditing pipelines is a severe anti-pattern.

The Modern Two-Step ELT Deduplication Pattern

To guarantee complete deduplication regardless of retry intervals, enterprise architectures implement a two-step ELT pattern combining append-only streaming ingestion with downstream SQL deduplication.

Loading diagram...

Pattern A: Scheduled MERGE Statements

Raw events stream continuously into an append-only staging table ( raw_events ). Every hour (or every 15 minutes), a BigQuery scheduled query or Dataform pipeline executes an idempotent MERGE statement into the production target table: MERGE INTO `retail_dw.orders_curated` AS target

  • USING (
  • SELECT * FROM `retail_dw.orders_raw`
  • WHERE ingestion_timestamp >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 2 HOUR)
  • QUALIFY ROW_NUMBER() OVER(
  • PARTITION BY order_id
  • ORDER BY event_timestamp DESC, ingestion_timestamp DESC
  • ) = 1
  • ) AS source
  • ON target.order_id = source.order_id
  • WHEN MATCHED AND source.event_type = 'CANCELLED' THEN
  • DELETE
  • WHEN MATCHED THEN
  • UPDATE SET
  • order_status = source.order_status,
  • total_amount = source.total_amount,
  • updated_at = source.event_timestamp
  • WHEN NOT MATCHED THEN
  • INSERT (order_id, customer_id, order_status, total_amount, updated_at)

VALUES (source.order_id, source.customer_id, source.order_status, source.total_amount, source.event_timestamp);

Pattern B: Zero-Latency Deduplicated Views with QUALIFY

When end users need live, millisecond-fresh access to deduplicated data without waiting for a scheduled batch merge, engineers deploy an analytical view on top of the raw streaming table using BigQuery's QUALIFY clause: CREATE OR REPLACE VIEW `retail_dw.v_orders_live` AS

  • SELECT
  • order_id,
  • customer_id,
  • order_status,
  • total_amount,
  • event_timestamp
  • FROM `retail_dw.orders_raw`
  • QUALIFY ROW_NUMBER() OVER(
  • PARTITION BY order_id
  • ORDER BY event_timestamp DESC
  • ) = 1;
  • 5. Pricing Comparison: Batch vs. Streaming Economics

Architecting data ingestion requires balancing data freshness SLAs against cloud infrastructure expenditure:

  • Ingestion Type Cost per GB Ingested Free Tier Allowance Downstream Compute Cost
  • Batch Loading ( bq load , DTS) $0.00 Unlimited (completely free) None
  • Storage Write API $0.025 First 2 TB per month free Optional (dedup MERGE query costs)
  • Legacy Streaming ( insertAll ) $0.050 None Optional (dedup MERGE query costs)

Practical Architectural Decision Rule:

If analytical consumers can tolerate a 15-minute to 1-hour reporting latency, micro-batching data via Cloud Storage and bq load is optimal , providing zero ingestion compute charges.

If operational use cases demand sub-second dashboard refreshes, automated alerting, or real-time ML feature ingestion, adopt the BigQuery Storage Write API and budget for $0.025 per GB plus downstream deduplication queries.

If events already flow through Pub/Sub and need no transformation, a BigQuery subscription writes them straight into a table with no Dataflow job (Section 9.3).