3.2 Streaming Ingestion & Deduplication Patterns

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:

1. A client application creates one or more pending streams attached to a target table.
2. Worker threads write records to their respective streams. Data is staged in an isolated, uncommitted buffer that is completely invisible to concurrent table queries.
3. Each stream is finalized with FinalizeWriteStream, and the client then issues one BatchCommitWriteStreams call covering all of them.
4. 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:

1. 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.
2. Copy delays: Streamed data can take up to 90 minutes to become available for copy operations.
3. 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:

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).