Skip to content
LakeBench
ProblemsCommunityPricing
Sign inStart practicing
Back
  1. Home
  2. Interview prep
  3. BigQuery streaming ingestion options

Snowflake, BigQuery & Databricks · BigQuery

BigQuery streaming ingestion options

Mediumwarehouses-26
bigquerystreamingstorage-write-apiingestion

Question

How do you stream data into BigQuery, and what is the Storage Write API?

Solution

There are three main ways to stream into BigQuery: the older insertAll API, the newer Storage Write API, and managed paths that use these underneath, such as Pub/Sub BigQuery subscriptions and Dataflow. For new work, use the Storage Write API.

Legacy streaming (tabledata.insertAll)

You post JSON rows over REST, and they become queryable within seconds. It offers best-effort de-duplication if you pass an insertId, but only for a short window, and it is more expensive per GB and has lower throughput than the newer API. It still exists, but Google steers new designs away from it.

Storage Write API

A gRPC-based API with higher throughput and a lower price. It has different stream types, so you choose the delivery guarantee:

  • Default stream: rows are available immediately, with at-least-once delivery. The simplest option when occasional duplicates are acceptable or you dedupe later.
  • Committed type: you create a stream, append rows with offsets, and rows are visible once appended. The offsets let you retry safely without duplicates, which gives exactly-once semantics.
  • Pending type: rows are buffered and become visible only when you commit the stream, so you can load a batch atomically.

Managed options

  • Pub/Sub BigQuery subscriptions write messages straight into a table, with no code in between. Good for simple event capture.
  • Dataflow with BigQueryIO can use the Storage Write API with exactly-once mode for streaming pipelines.

Things to know

Streamed rows land first in a streaming buffer, and for a short time there have been restrictions on running some DML statements against the rows in that buffer. Check the current documentation before designing frequent updates on freshly streamed data.

Choosing

Duplicates fine, simplest path: default stream, or a Pub/Sub subscription. Need exactly-once: committed stream with offsets, usually through Dataflow or a client library. Need a clean batch boundary: pending stream. If freshness of a few minutes is enough, loading files in micro-batches is cheaper, as the next question covers.

PreviousNext