Streaming Data into BigQuery: Storage Write API, Datastream CDC, and Continuous Queries

Batch loads answer yesterday's questions. Fraud checks, inventory counts, live ops dashboards and freshness SLAs on operational replicas all need data in BigQuery within seconds, not on the next hourly schedule. BigQuery has three distinct ways to get there, and picking the wrong one is how teams end up with expensive, duplicate-riddled tables.

This tutorial walks through all three — the Storage Write API for application events, Datastream for change data capture from operational databases, and continuous queries for transforming and routing rows as they land — plus the deduplication and cost patterns that keep the result trustworthy.

Which path do you need?

Source	Use	Typical latency	Notes
Application/services emitting events	Storage Write API (default stream)	Seconds	Exactly-once with committed streams; cheaper than the legacy tabledata.insertAll
Pub/Sub topic already carrying events	Pub/Sub BigQuery subscription	Seconds	No code at all; uses the Storage Write API under the hood
MySQL, PostgreSQL, Oracle, SQL Server	Datastream CDC	Seconds to a few minutes	Managed log-based replication with automatic merge into BigQuery
Rows already in BigQuery needing transform/route	Continuous queries	Seconds	SQL that never finishes; writes to a table, Pub/Sub or Bigtable

Rule of thumb: if you own the producer, use the Storage Write API. If you are replicating someone else's database, use Datastream. If you need to enrich, filter, score or fan out rows that are already landing, layer a continuous query on top.

Path 1: The Storage Write API

The Storage Write API replaced legacy streaming inserts as the default path. It is a gRPC streaming interface that is roughly half the price per gigabyte of insertAll, supports exactly-once delivery, and makes rows queryable within seconds.

There are two modes worth knowing:

• Default stream — at-least-once delivery, no stream management, highest throughput. Use it when your downstream logic tolerates the occasional duplicate (or you dedupe on read, see below).
• Committed / pending streams — you create a named stream, append with offsets, and commit. Offsets give exactly-once semantics; pending streams make an entire batch atomically visible on commit, which is how you get all-or-nothing loads.

A minimal Python producer using the default stream:

from google.cloud import bigquery_storage_v1
from google.cloud.bigquery_storage_v1 import types, writer
from google.protobuf import descriptor_pb2
import event_pb2  # generated from your .proto matching the table schema

client = bigquery_storage_v1.BigQueryWriteClient()
parent = client.table_path("my-project", "analytics", "events")
stream = f"{parent}/_default"

proto_schema = types.ProtoSchema()
descriptor = descriptor_pb2.DescriptorProto()
event_pb2.Event.DESCRIPTOR.CopyToProto(descriptor)
proto_schema.proto_descriptor = descriptor

request_template = types.AppendRowsRequest(
    write_stream=stream,
    proto_rows=types.AppendRowsRequest.ProtoData(writer_schema=proto_schema),
)
append_stream = writer.AppendRowsStream(client, request_template)

rows = types.ProtoRows()
rows.serialized_rows.append(
    event_pb2.Event(event_id="e-1001", user_id="u-7", action="checkout").SerializeToString()
)
request = types.AppendRowsRequest(
    proto_rows=types.AppendRowsRequest.ProtoData(rows=rows)
)
future = append_stream.send(request)
print(future.result())
append_stream.close()

Practical notes:

• Batch rows into appends of a few hundred kilobytes to a couple of megabytes; one row per append wastes most of your quota and throughput.
• The target table should be time-partitioned on ingestion time or an event timestamp and clustered on your most common filter (tenant, user, device). Streaming into an unpartitioned table is the single most common cause of runaway scan costs later.
• Watch quota: default-stream throughput and concurrent connection limits are per project and per region. If you are near them, shard across streams before you shard across tables.
• Schema changes are additive-friendly: add a nullable column in BigQuery, regenerate the proto, redeploy. Removing or retyping columns still requires a coordinated cutover.

If your events already flow through Pub/Sub, skip the code entirely and create a BigQuery subscription; it writes directly to the table with the Storage Write API and supports schema mapping from the topic schema.

Path 2: Datastream CDC from an operational database

Datastream reads the database's transaction log (binlog, WAL, redo) and streams row-level changes into BigQuery. Two write modes matter:

• Merge mode (default): Datastream keeps the BigQuery table a live replica of the source, applying inserts, updates and deletes. You query the table normally and see current state.
• Append-only mode: every change becomes a new row with metadata columns. Use this when you need history — slowly changing dimensions, audit trails, or reprocessing.

The staleness knob is the one that drives your bill. Datastream lets you set the maximum data staleness per table (for example 15 minutes vs. 0). Lower staleness means more frequent merge operations, which means more BigQuery compute. A 15-minute staleness setting is often 5–10× cheaper than near-zero, and for most dashboards nobody can tell the difference. Set it deliberately, per table, not globally.

Things that bite teams in practice:

• Source load. Log-based CDC is light, but the initial backfill of a large table is not. Schedule backfills off-peak and backfill big tables one at a time.
• Primary keys are mandatory for merge mode. Tables without them must run append-only.
• DDL drift. New columns propagate; dropped or retyped columns can stall a stream. Alert on Datastream's freshness and error metrics rather than discovering it in a dashboard.
• Deletes. In append-only mode a delete is a row with a change type, not a disappearance. Your downstream views must filter it.

A view over an append-only CDC table that reconstructs current state:

CREATE OR REPLACE VIEW `analytics.orders_current` AS
SELECT * EXCEPT(rn, datastream_metadata)
FROM (
  SELECT
    *,
    ROW_NUMBER() OVER (
      PARTITION BY order_id
      ORDER BY datastream_metadata.source_timestamp DESC,
               datastream_metadata.change_sequence_number DESC
    ) AS rn
  FROM `analytics.orders_cdc`
)
WHERE rn = 1
  AND datastream_metadata.change_type != 'DELETE';

Path 3: Continuous queries

A continuous query is a BigQuery SQL statement that never terminates: it processes rows as they arrive and writes results to another BigQuery table, a Pub/Sub topic, or Bigtable. That makes it the streaming transform layer you would otherwise build in Dataflow.

Route high-value events to Pub/Sub as they land:

EXPORT DATA
OPTIONS (
  format = 'CLOUD_PUBSUB',
  uri = 'https://pubsub.googleapis.com/projects/my-project/topics/high-value-orders'
) AS (
  SELECT TO_JSON_STRING(STRUCT(order_id, customer_id, amount, event_ts)) AS message
  FROM APPENDS(TABLE `analytics.orders_stream`, CURRENT_TIMESTAMP() - INTERVAL 10 MINUTE)
  WHERE amount > 5000
);

Or enrich and land into a serving table:

INSERT INTO `analytics.orders_enriched`
SELECT
  a.order_id,
  a.customer_id,
  a.amount,
  c.segment,
  a.event_ts
FROM APPENDS(TABLE `analytics.orders_stream`, CURRENT_TIMESTAMP() - INTERVAL 10 MINUTE) AS a
LEFT JOIN `analytics.customers` AS c
  ON c.customer_id = a.customer_id;

The APPENDS table-valued function is what makes this incremental: it returns only rows appended since the given point, so the query does not rescan history. Requirements to be aware of before you plan around continuous queries: they run on a reservation with the CONTINUOUS job type (they are not an on-demand feature), they run under a service account, and each one holds slots for as long as it runs. Budget them like a always-on service, because that is what they are.

Deduplication you should assume you need

At-least-once delivery means duplicates. Do not fight this at ingestion; design for it:

1. Give every event a stable event_id at the producer (a UUID, or a hash of the natural key plus timestamp).
2. Query through a deduplicating view for anything user-facing:

CREATE OR REPLACE VIEW `analytics.events_dedup` AS
SELECT * EXCEPT(rn)
FROM (
  SELECT *, ROW_NUMBER() OVER (PARTITION BY event_id ORDER BY ingest_ts DESC) AS rn
  FROM `analytics.events`
  WHERE ingest_ts >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 7 DAY)
)
WHERE rn = 1;

3. Periodically compact into a clean table so the dedupe window stays small and cheap:

MERGE `analytics.events_clean` AS t
USING (
  SELECT * FROM `analytics.events_dedup`
  WHERE ingest_ts >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 1 DAY)
) AS s
ON t.event_id = s.event_id
WHEN NOT MATCHED THEN INSERT ROW;

Watching cost and freshness

Streaming pipelines fail quietly. Two queries belong in every real-time deployment.

Freshness per table:

SELECT
  'orders_stream' AS table_name,
  MAX(event_ts) AS newest_event,
  TIMESTAMP_DIFF(CURRENT_TIMESTAMP(), MAX(event_ts), SECOND) AS lag_seconds
FROM `analytics.orders_stream`
WHERE event_ts >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 1 DAY);

Storage Write API volume and errors, from the write API's own view:

SELECT
  DATE(start_timestamp) AS day,
  table_name,
  SUM(total_rows) AS rows_written,
  SUM(total_input_bytes) / POW(1024, 3) AS gib_written,
  SUM(error_count) AS errors
FROM `region-us`.INFORMATION_SCHEMA.WRITE_API_TIMELINE_BY_PROJECT
WHERE start_timestamp >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 7 DAY)
GROUP BY day, table_name
ORDER BY day DESC, gib_written DESC;

Alert on two things: lag_seconds above your SLA, and any sustained non-zero errors. A stream that stopped yesterday looks exactly like a quiet business day on a dashboard.

A sane default architecture

For most mid-market teams, the pipeline that works is boring:

1. Application events → Pub/Sub → BigQuery subscription → raw partitioned, clustered landing table.
2. Operational databases → Datastream in merge mode, staleness set to 15 minutes for reporting tables and near-zero only for the one or two tables that genuinely need it.
3. A dedupe view over each landing table, plus a daily compaction MERGE.
4. Continuous queries only where seconds actually matter — alerting, routing, real-time scoring — and everything else left to scheduled transforms.
5. Freshness and error monitoring queries running on a schedule with alerting attached.

Start with the cheapest option that meets the SLA the business will actually enforce, and tighten only where someone can name the cost of being late.

If you are designing or repairing a streaming pipeline into BigQuery, our data engineering and data migration teams do this work daily — get in touch with your source systems and freshness targets and we will sketch the architecture with you.