Data Loading and Streaming

## What a load job is BigQuery has four job types — query, load, extract and copy. A **load job** is the batch ingestion path: it reads whole files from Cloud Storage (or from a local file through the client, or from a readable data source) and writes them into a BigQuery table. It is asynchronous: you submit it, get a job id, and poll for completion. Unlike a query, it does not produce a result set; its output is rows in a destination table. ## Starting one Three equivalent front doors: ```sql LOAD DATA INTO mydataset.events FROM FILES ( format = 'PARQUET', uris = ['gs://my-bucket/events/2026-08-20/*.parquet'] ); ``` ```bash bq load --source_format=PARQUET mydataset.events 'gs://my-bucket/events/*.parquet' ``` and the `jobs.insert` REST call with a `configuration.load` block, which is what every client library wraps. One wildcard per URI is allowed, and you may list several URIs. The source bucket must be colocated with the dataset's location — you cannot load a EU-region bucket into a US dataset. ## Source formats and what each buys you - **CSV** — ubiquitous, but text: every value must be parsed and coerced, and the file carries no schema. You supply `--skip_leading_rows`, `--field_delimiter`, `--allow_quoted_newlines`, `--allow_jagged_rows`. - **Newline-delimited JSON** — one JSON object per line. Also text, also parsed row by row, but it carries nested structure naturally, which maps onto BigQuery `STRUCT`/`ARRAY` columns. - **Avro** — binary, self-describing, block-structured. Google recommends it for loading compressed data because its internal blocks can be read in parallel. - **Parquet** and **ORC** — binary, self-describing, columnar. Types come from the file, so no schema flag is needed. - **Datastore/Firestore export files** — for restoring exports of those databases. ## Schema handling With a self-describing format the schema is inferred from the file. With CSV or JSON you either pass an explicit schema (a JSON schema file or `col:TYPE,...`) or set `--autodetect`, which samples rows and guesses. Autodetect is convenient for exploration and risky in a pipeline: a column that is all digits today can be inferred `INTEGER` and break tomorrow when an alphanumeric id shows up. Production loads normally pin the schema. When the incoming files legitimately gain a column, `--schema_update_option=ALLOW_FIELD_ADDITION` lets the load extend the table's schema; `ALLOW_FIELD_RELAXATION` lets it turn a `REQUIRED` column into `NULLABLE`. Nothing lets a load drop or retype a column. ## Write disposition and atomicity `WRITE_APPEND` adds rows, `WRITE_TRUNCATE` replaces the table's (or the addressed partition's) contents, `WRITE_EMPTY` fails if the destination already has data. The important property is that the job is **atomic**: readers see either the pre-job table or the fully loaded table, never a half-loaded one. That makes "truncate and reload this partition every night" a safe, idempotent design — a retried job produces the same table. You can address a single ingestion-time partition with the decorator syntax `mydataset.events$20260820`. ## Errors and bad rows By default a single unparseable row fails the job. `--max_bad_records=N` tolerates up to N; `--ignore_unknown_values` drops fields not in the schema instead of erroring. Both are useful and both hide data problems, so set them deliberately. ## Cost and limits Batch loads carry no per-byte ingestion charge — they execute on a free shared pool of slots (and can optionally be routed to a reservation so they get dedicated capacity instead of competing in the shared pool). You pay for the resulting storage. That free-and-atomic combination is why batch loading is the default answer whenever minutes of latency are acceptable. The counterweight is a **daily cap on the number of load jobs per table**, which makes "a load job every few seconds" an unworkable design. Micro-batching every few minutes is fine; sub-minute freshness belongs to the Storage Write API instead. ## When batch is the wrong tool If rows must be queryable seconds after they are produced, or they arrive one at a time from an application rather than as files, a load job is the wrong shape — you would be paying for job orchestration and burning the per-table job quota. That is the streaming path's job.