Loading data into BigQuery

Batch load jobs are free and accept CSV, JSON, Avro, Parquet and ORC. Schema autodetection turns zip codes into integers and money into FLOAT, one bad row fails the whole load, and a table takes at most 1,500 loads a day.

A load job reads files, usually from Cloud Storage or your machine, and writes them into a table. Batch loads using the shared slot pool cost nothing; you only pay to store the result. That makes a load job the cheapest way to get large amounts of data into BigQuery, much cheaper than INSERT statements or streaming. The traps are in the details: what schema autodetection guesses, how bad rows are handled, and how often you can load into one table.

A load job

bq load \
  --source_format=CSV \
  --skip_leading_rows=1 \
  mydataset.orders \
  'gs://my-bucket/orders/2026-03-*.csv' \
  order_id:STRING,zip:STRING,amount:NUMERIC,ordered_on:DATE

The same in SQL, from a script or the editor:

LOAD DATA INTO mydataset.orders (order_id STRING, zip STRING, amount NUMERIC, ordered_on DATE)
FROM FILES (
  format = 'CSV',
  skip_leading_rows = 1,
  uris = ['gs://my-bucket/orders/2026-03-*.csv']
);

A wildcard URI loads many files in one job. By default a load appends (WRITE_APPEND); --replace or LOAD DATA OVERWRITE replaces the table (WRITE_TRUNCATE), atomically.

Formats

Prefer a self-describing format. With CSV and JSON, BigQuery has to be told the schema or has to guess it.

What schema autodetection guesses

With --autodetect, BigQuery looks at up to the first 500 rows and picks a type per column. We loaded this CSV with it:

id,zip,amount,ordered_on,note
1,02134,12.50,2026-03-01,ok
2,10001,7,2026-03-02,
3,94105,,2026-03-03,"has, comma"

And for newline-delimited JSON with a nested object, autodetect gave user the type RECORD with a REPEATED tags field, turned the string "7" into an INTEGER because the other row had a number, and filled the missing tags with an empty array. The columns came out in a different order than in the file.

Autodetect is fine for exploring a file once. Querylab.io’s Import tab detects a schema from a CSV, JSON, Excel, Parquet or Avro file and shows it before the load, with every column’s name, type and mode editable, so you can set the zip column to STRING first. It then runs a load job. For anything that runs again, pass an explicit schema, so that IDs, codes and phone numbers stay STRING, money is NUMERIC, and a sample without a value doesn’t decide a type for millions of later rows.

One bad row fails the load

By default a single value that doesn’t fit its column fails the whole job, and nothing is written:

Error while reading data, error message: Invalid NUMERIC value: abc;
line_number: 3 ... column_name: "amount" column_type: NUMERIC value: "abc"

--max_bad_records=N lets up to N bad rows be skipped. They are skipped silently: the job succeeds with a warning, and the table has fewer rows. In our test, three rows with one bad value loaded two rows. If you allow bad records, compare the row count with the source.

For JSON, a field that isn’t in the table’s schema also fails the load (“No such field: unknown_field”). --ignore_unknown_values drops such fields instead.

Loading into a partition

To replace one day of a partitioned table, load with WRITE_TRUNCATE to the partition decorator. Only that partition is replaced:

bq load --replace --source_format=PARQUET \
  'mydataset.events$20260302' 'gs://my-bucket/events/2026-03-02/*.parquet'

In SQL the same is LOAD DATA INTO mydataset.events OVERWRITE PARTITIONS (event_date = '2026-03-02') FROM FILES (...).

Limits

The per-table limit is the one that matters, and query and copy jobs that write to the table count toward the same 1,500. Loading a small file every minute uses 1,440 of them; every 30 seconds runs out after about 12 hours. For frequent small writes, collect files and load them in batches, or stream with the Storage Write API. See Streaming.

Cost

Batch loads on the shared slot pool are free. You pay for storage of the loaded data, and for network transfer if the bucket is in a different location than the dataset. Loads that run on a reservation use its slots. The alternatives cost money: INSERT statements are billed as queries, and streaming is billed per GB written.