Loading Data into BigQuery: Batch and Streaming

There are two ways into a BigQuery table. Files can be loaded in a batch, or rows can be streamed in as they are produced. The choice is usually framed as a question of speed, and that is half of it. The other half is what happens when a write is sent twice, because every real pipeline retries. This guide to loading data into BigQuery compares the methods on both counts, with cost and limits alongside.

The methods at a glance

Google's overview lists further routes, including change data capture from databases and external tables that avoid loading altogether (Google Cloud: loading data). The six above cover most pipelines.

Batch loading

A load job reads files, usually from Cloud Storage, and writes them to a table. Avro, CSV, newline-delimited JSON, ORC and Parquet are accepted. In SQL it is one statement (Google Cloud: LOAD DATA statement):

LOAD DATA INTO sales.orders
FROM FILES (
  format = 'PARQUET',
  uris = ['gs://example-landing/orders/2026-10-01/*.parquet']
);

INTO appends. OVERWRITE replaces the table. From the command line the equivalent is bq load, where appending is also the default and --replace overwrites (Google Cloud: load CSV data from Cloud Storage):

bq load \
  --source_format=CSV \
  --skip_leading_rows=1 \
  sales.orders \
  "gs://example-landing/orders/2026-10-01/*.csv" \
  order_id:INTEGER,customer_id:INTEGER,amount:NUMERIC,ordered_at:TIMESTAMP

Three properties make load jobs the default choice:

• They are atomic. If a load job fails, none of its data is available; if it succeeds, all of it is. A half-loaded table is not a state you have to handle.
• They are free. Batch loading uses a shared pool of slots at no charge. You pay for the storage afterwards, and for network transfer if the bucket and dataset are in different regions (Google Cloud: BigQuery pricing).
• They retry safely. Submitting a job with a job ID you generate is idempotent: however many times it is resent, at most one attempt succeeds.

Format matters more than it first appears. Parquet, Avro and ORC carry their own schema, so the table schema is read from the files. CSV carries none, so you supply one or accept auto-detection, which infers types from a sample of up to 500 rows of one file (Google Cloud: schema auto-detection). And a gzip-compressed CSV file cannot be read in parallel, which makes it slower to load than the same file uncompressed.

The limits that shape a design

These are the documented defaults (Google Cloud: quotas and limits). The first one decides how close to real time a batch design can go, as the calculation further down shows.

Streaming with the Storage Write API

When data must be queryable within seconds, rows are written through the Storage Write API. Its central idea is a stream, and the kind of stream decides the guarantee (Google Cloud: Storage Write API):

• The default stream needs no setup. Rows are available as soon as they are written. The guarantee is at-least-once: a row will arrive, possibly more than once.
• A committed stream that you create also makes rows available immediately, and adds offsets. Each write states the position it expects to land on, and is performed only if that position is the current end of the stream. That gives exactly-once delivery within the stream.
• A pending stream buffers rows until you commit, then makes them all visible in one atomic step. It is a batch load performed by an application instead of from files.

Streaming is not free. The pricing page lists a per-GiB charge for the Storage Write API, with the first 2 TiB each month free. Google also advises sending a batch of rows in each call, not one row at a time.

An older interface still exists. What Google now calls the Storage Write API (REST) was known for years as tabledata.insertAll, or simply streaming inserts. It remains supported, but the gRPC API is recommended for new work (Google Cloud: Storage Write API (REST)).

What a retry does

Suppose a client sends a batch, the write succeeds, and the reply is lost on the network. The client cannot tell success from failure, so it sends the batch again. The script below models that moment on both kinds of stream. It is a model of the documented offset rule, not the API itself.

# A model of what a retried write does under the two Storage Write API guarantees. It is not the API:
# it applies the documented rule that a write carrying an offset is performed only when that offset
# equals the next append offset of the stream.
class Stream:
    def __init__(self):
        self.rows = []

    def append(self, batch, offset=None):
        if offset is None:                     # default stream: always written at the current end
            self.rows.extend(batch)
            return "OK"
        if offset < len(self.rows):
            return "ALREADY_EXISTS"            # these rows were written before; safe to ignore
        if offset > len(self.rows):
            return "OUT_OF_RANGE"              # an earlier write is missing; resend from the last success
        self.rows.extend(batch)
        return "OK"


def send(batches, use_offsets, lost_reply_on):
    """Send every batch once; the reply to one batch is lost, so the client sends that batch again."""
    stream, offset, events = Stream(), 0, []
    for number, batch in enumerate(batches):
        position = offset if use_offsets else None
        stream.append(batch, position)
        if number == lost_reply_on:            # the write succeeded, but the client never heard
            events.append(f"batch {number} resent -> {stream.append(batch, position)}")
        offset += len(batch)
    return stream, events


batches = [[f"event-{b}{r}" for r in "abc"] for b in range(4)]          # 4 batches of 3 rows = 12 rows
for label, use_offsets in [("default stream (no offsets)", False), ("committed stream with offsets", True)]:
    stream, events = send(batches, use_offsets, lost_reply_on=1)
    duplicates = len(stream.rows) - len(set(stream.rows))
    print(f"{label:30} {'; '.join(events):32} rows in table: {len(stream.rows):2}  duplicates: {duplicates}")

gap = Stream()
gap.append(batches[0], 0)
print(f"\nbatch 2 sent at offset 6 after batch 1 was never written -> {gap.append(batches[2], 6)}")

# How often can a table be batch loaded? Jobs per day against the documented per-table limit.
PER_TABLE_PER_DAY = 1500
print(f"\n{'load schedule':18} {'jobs a day':>10}  share of the {PER_TABLE_PER_DAY:,} per-table limit")
for label, seconds in [("hourly", 3600), ("every 15 minutes", 900), ("every 5 minutes", 300),
                       ("every minute", 60), ("every 30 seconds", 30)]:
    jobs = 86400 // seconds
    print(f"{label:18} {jobs:10,}  {jobs / PER_TABLE_PER_DAY:6.0%}")

default stream (no offsets)    batch 1 resent -> OK             rows in table: 15  duplicates: 3
committed stream with offsets  batch 1 resent -> ALREADY_EXISTS rows in table: 12  duplicates: 0

batch 2 sent at offset 6 after batch 1 was never written -> OUT_OF_RANGE

load schedule      jobs a day  share of the 1,500 per-table limit
hourly                     24      2%
every 15 minutes           96      6%
every 5 minutes           288     19%
every minute            1,440     96%
every 30 seconds        2,880    192%

Reading the first two lines:

• On the default stream the retry is written again. Twelve rows were sent and fifteen are in the table. Nothing reported an error.
• With offsets the retry is refused. The stream answers ALREADY_EXISTS, which the documentation says can be safely ignored, and the table holds twelve rows (Google Cloud: Storage Write API best practices).

The third line shows the other offset error. A batch sent at offset 6 when only three rows exist is rejected as OUT_OF_RANGE: an earlier write is missing, and the client must resend from its last success. Offsets therefore catch gaps as well as repeats.

Exactly-once has a price in complexity. The application must track its offset and handle both errors. Google's advice is to ask first whether you need it. If the source itself only promises at-least-once, or duplicates are easy to remove afterwards with a key, the default stream is the simpler and recommended choice.

How close to real time can batch go?

The second table in the output answers a common design question. One load every five minutes uses 19% of a table's daily allowance. One a minute uses 96%, leaving almost no room for a failed job, since failures count. Every thirty seconds is impossible.

So batch loading reaches down to a few minutes comfortably. Below that, the documentation's own advice applies: if you regularly exceed load job limits, stream instead.

From Pub/Sub straight into a table

If events are already published to Pub/Sub and need no reshaping, a BigQuery subscription writes them to a table with no code running anywhere (Google Cloud: BigQuery subscriptions):

gcloud pubsub subscriptions create orders-to-bigquery \
  --topic=orders \
  --bigquery-table=analytics-prod.sales.order_events

The command follows the documented form (Google Cloud: create BigQuery subscriptions). Two limits to know. Delivery is at-least-once, so plan for duplicates. And once messages need joining, windowing or aggregation before they land, Google recommends a Dataflow pipeline instead.

Choosing

1. Is a delay of several minutes acceptable? Use load jobs. They are free, atomic and simple. Most reporting data belongs here.
2. Is the source a supported application or another cloud's storage? Consider the Data Transfer Service before writing anything.
3. Must data be queryable in seconds? Use the Storage Write API, starting with the default stream.
4. Would a duplicate row cause real harm, such as a payment counted twice? Use a committed stream with offsets, or remove duplicates downstream on a business key.
5. Are the events already in Pub/Sub and usable as they are? Use a BigQuery subscription.

Mistakes that cost time

• Streaming data that is read once a day. It adds cost and duplicate handling for no benefit.
• Loading a table every minute. It works until the first bad day, when failed jobs exhaust the limit.
• Trusting schema auto-detection in production. The types come from a sample. A column that looks numeric in the sample and is not further down fails the load.
• Appending on retry without a job ID. The default write behaviour is append, so a blind rerun doubles the data.
• Loading across regions. The load itself is free; the network transfer is billed.

Laying out the bucket so that loads can be rerun safely is covered in Cloud Storage to BigQuery: building an ingestion layer. For where the files live, see Google Cloud Storage, and for the table design that follows, partitioning and clustering.

Ingestion on Google Cloud is one of the topics in the Data Engineering course.

Sources

• Introduction to loading data (Google Cloud Documentation) — Google Cloud
• Load CSV data from Cloud Storage (Google Cloud Documentation) — Google Cloud
• Load statements in GoogleSQL (Google Cloud Documentation) — Google Cloud
• Quotas and limits (Google Cloud Documentation) — Google Cloud
• BigQuery pricing (Google Cloud) — Google Cloud
• Introduction to the Storage Write API (gRPC) (Google Cloud Documentation) — Google Cloud
• BigQuery Storage Write API (gRPC) best practices (Google Cloud Documentation) — Google Cloud
• Use the Storage Write API (REST) (Google Cloud Documentation) — Google Cloud
• BigQuery subscriptions (Google Cloud Documentation) — Google Cloud
• Create BigQuery subscriptions (Google Cloud Documentation) — Google Cloud
• Using schema auto-detection (Google Cloud Documentation) — Google Cloud