When should you use the BigQuery Storage Write API instead of a batch load job from Cloud Storage?

## The two ingestion shapes BigQuery offers batch ingestion (a **load job** over files in Cloud Storage) and streaming ingestion (the **Storage Write API**, a gRPC service, plus the older `tabledata.insertAll` REST method it replaces). Choosing between them is one of the most common BigQuery design questions because cost, latency, atomicity and operational complexity all move together. ## Cost Batch load jobs carry **no per-byte ingestion charge**. They run on a free shared pool of slots — or, if you have a reservation, can be routed there for dedicated capacity. You pay for the storage the rows occupy afterwards, and that is all. The Storage Write API bills for the **bytes ingested**. Two consequences follow. First, at a steady multi-terabyte-per-day volume, streaming everything is a standing bill that batch loading would not incur. Second, rows are billed against a **minimum row size**, so a firehose of very small rows costs far more than the sum of their payload bytes. Batching many rows into each `AppendRows` request helps throughput and request overhead; it does not change the per-row billing floor, which is a reason to avoid streaming inherently tiny records. ## Latency A load job is scheduled, queued and executed: expect seconds to minutes end-to-end even for a small file, plus whatever latency your upstream took to land the file in Cloud Storage. Streamed rows are queryable within seconds of the append being acknowledged — BigQuery holds them in a write-optimised buffer that queries read, and converts them to columnar storage in the background. So the honest question in an interview is not "batch or stream" but **"what freshness does the consumer actually need?"** Dashboards refreshed hourly do not need streaming. A fraud rule that reads the last 30 seconds of events does. ## Throughput and quotas Load jobs are capped per table per day, which caps your cadence: micro-batching every five minutes is comfortable, every five seconds is not. The Storage Write API is designed for sustained high throughput and is limited by connection- and region-level throughput quotas rather than by a job count — it is the path built for continuous ingestion. ## Atomicity and correctness A load job is all-or-nothing: a failure leaves the table untouched, so retrying is idempotent. That single property removes an enormous amount of pipeline complexity. Streaming's correctness story is per-stream. The **default stream** gives at-least-once delivery, so retries can duplicate rows. Application-created **committed streams with offsets** give exactly-once, at the cost of managing streams and offsets. **Pending streams** recover batch-style atomicity — rows stay invisible until you commit the stream — which is why the Storage Write API can also serve as a programmatic replacement for load jobs when your data is already in memory rather than in files. ## Operational shape Batch pipelines are files, schedules and reruns: easy to inspect, easy to backfill, easy to reason about. `INFORMATION_SCHEMA.JOBS` shows every load, its bytes, its errors. Streaming pipelines are long-lived processes with connections, retries, backpressure and dead-letter handling; you own the client's failure modes. ## A practical decision rule 1. Establish the freshness SLA with the consumer, not the producer. 2. If it is minutes, land files in Cloud Storage and load them — the ingestion is free and the retry story is trivial. 3. If it is seconds, stream, choose the stream type from your duplication tolerance, and batch rows into large appends. 4. If it is a mix, do both: stream into a landing table for the real-time consumers and batch-load the same data into the curated table, or stream and periodically `MERGE` into a deduplicated table. ## The anti-patterns Streaming because "real-time is better" when the dashboard refreshes hourly is the expensive mistake. Running a load job per event, or per minute per table, to "get streaming for free" is the other one — it exhausts the per-table job quota and produces a pile of tiny commits. And streaming individual rows with one `AppendRows` call each maximises both request overhead and the per-row billing floor.