# 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.

Source: https://docs.querylab.io/bigquery/loading-data/

Updated: 2026-10-14

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

```sh
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:

```sql
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

| Format                 | Schema in the file | Notes                                                                                                                                   |
| ---------------------- | ------------------ | --------------------------------------------------------------------------------------------------------------------------------------- |
| Parquet, Avro, ORC     | yes                | Typed and compressed. The fastest to load and the safest choice.                                                                        |
| Newline-delimited JSON | no                 | One JSON object per line. Nested objects and arrays map to `STRUCT` and `ARRAY`.                                                        |
| CSV                    | no                 | Compressed files up to 4 GB, uncompressed up to 5 TB. Quoted values may contain commas; quoted newlines need `--allow_quoted_newlines`. |

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:

```text
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"
```

| Column       | Detected type | What happened                                          |
| ------------ | ------------- | ------------------------------------------------------ |
| `id`         | `INTEGER`     | fine                                                   |
| `zip`        | `INTEGER`     | `02134` was stored as `2134`; the leading zero is gone |
| `amount`     | `FLOAT`       | money as floating point, not `NUMERIC`                 |
| `ordered_on` | `DATE`        | fine                                                   |
| `note`       | `STRING`      | the empty field became NULL                            |

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](https://docs.querylab.io/data-management/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:

```text
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:

```sh
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

| Limit                         | Value                       |
| ----------------------------- | --------------------------- |
| Load jobs per table per day   | 1,500, failed ones included |
| Load jobs per project per day | 100,000                     |
| Size of one load job          | 15 TB                       |
| Files per load job            | 10 million                  |
| Row size (CSV, JSON)          | 100 MB                      |
| Run time                      | 6 hours                     |

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](https://docs.querylab.io/bigquery/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.

Open the playground at https://app.querylab.io?playground=true&utm_source=docs&utm_medium=concepts to run it. It is the real app on public BigQuery datasets and needs no sign-in.

## Sources

Based on the BigQuery documentation by Google, CC BY 4.0, rewritten with examples checked against BigQuery:

- [Introduction to loading data](https://cloud.google.com/bigquery/docs/loading-data)

- [Batch loading data](https://cloud.google.com/bigquery/docs/batch-loading-data)

- [Using schema auto-detection](https://cloud.google.com/bigquery/docs/schema-detect)

- [Quotas and limits, load jobs](https://cloud.google.com/bigquery/quotas#load_jobs)