Skip to content
Tutorials Center
My learning

Topics

Lesson 31 of 37 ยท Pipelines and Integrations

Loading Data into BigQuery

Batch-load CSV, JSON, Avro and Parquet into BigQuery with bq load, LOAD DATA and the console, choose schemas and write modes, and query external tables.

A warehouse is only useful once data is in it. BigQuery offers several ways to get data in: batch load jobs from files, streaming for real-time rows, transfers from SaaS products and other clouds, and external tables that query files in place. This lesson covers batch loading, the most common path, and the decisions around file formats, schemas and write modes.

Loading needs a real Google Cloud project, so the commands are read-only. The runnable examples practice the parts you can model: parsing a CSV, validating rows before load, and the append, truncate and merge patterns in SQL.

Batch load jobs

A load job reads one or more files and writes them into a table. Batch loading from Cloud Storage or a local file is free of query charges (it uses a shared pool of capacity, so it is not billed per byte scanned), which makes it the cheapest way to ingest large volumes. Supported formats:

Format Notes
CSV Human-friendly; needs a schema or autodetect; watch delimiters, quotes and header rows
JSON (newline-delimited) One object per line; supports nested and repeated fields
Avro, Parquet, ORC Self-describing, typed, compressed; best choice for pipelines

Prefer Avro or Parquet when you control the producer: they carry the schema, preserve types exactly, and load faster than text.

The bq load command

Terminal
bq load \
  --source_format=CSV \
  --skip_leading_rows=1 \
  --autodetect \
  shop.orders_raw \
  gs://my-bucket/exports/orders_2026-03-02.csv

Useful flags: --schema=schema.json for an explicit schema, --replace to overwrite the table, --max_bad_records=10 to tolerate a few broken rows, --time_partitioning_field=order_date and --clustering_fields=country,status to create the table partitioned and clustered, and wildcards such as gs://bucket/path/*.parquet for many files.

The same operation in SQL, which is handy inside scripts and scheduled queries:

SQL
LOAD DATA INTO shop.orders_raw
FROM FILES (
  format = 'PARQUET',
  uris = ['gs://my-bucket/exports/orders_2026-03-*.parquet']
);

Use LOAD DATA OVERWRITE to replace the contents, which is the idempotent choice for a daily job that rebuilds a whole table.

Write dispositions

What should happen if the table already has rows? Pick deliberately:

  • WRITE_APPEND adds the new rows (the default for bq load). Re-running the job duplicates data.
  • WRITE_TRUNCATE replaces the table (or partition) contents. Safe to re-run.
  • WRITE_EMPTY fails if the table already has data.

For daily jobs, load into a dated partition with truncate (or load into a staging table and MERGE). Then a retry never creates duplicates.

Choose a schema, do not trust autodetect forever

--autodetect samples the file and guesses types. It is great for exploration but can misread a column that looks numeric in the first rows and has text later, or treat identifiers like 007 as integers. For production, write an explicit schema:

JSON
[
  {"name": "order_id", "type": "INT64", "mode": "REQUIRED"},
  {"name": "customer_email", "type": "STRING"},
  {"name": "order_date", "type": "DATE"},
  {"name": "total", "type": "NUMERIC"}
]

Prefer NUMERIC for money, not FLOAT64, and DATE or TIMESTAMP rather than strings. Parsing text dates at load time makes partitioning possible.

Validate before you load

A single malformed row can fail a whole load job, or silently become NULL with --max_bad_records. Cheap checks before loading save re-runs. The example parses a small CSV in Python and counts rows that would be rejected.

Python Validate a CSV before loading

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

In a real pipeline, write the rejected lines to a "dead letter" file for review instead of dropping them.

Idempotent loads and MERGE

If a source sends changes (new and updated rows), load them into a staging table and MERGE into the target. BigQuery's MERGE updates matching rows and inserts the rest in one statement:

SQL
MERGE shop.customers AS t
USING shop.customers_staging AS s
ON t.customer_id = s.customer_id
WHEN MATCHED THEN
  UPDATE SET email = s.email, country = s.country
WHEN NOT MATCHED THEN
  INSERT (customer_id, email, country) VALUES (s.customer_id, s.email, s.country);

SQLite supports an INSERT ... ON CONFLICT DO UPDATE upsert that does the same job, so you can practice the logic.

SQL Upsert from a staging table

Press Run (or Ctrl + Enter) to see the result here.

Row 2 was updated and row 3 inserted. Running the load a second time changes nothing, which is what idempotent means.

External tables and other sources

You do not always need to load. An external table points at files in Cloud Storage (or Drive, Bigtable) and queries them in place, which suits occasional reads of data that changes elsewhere:

SQL
CREATE EXTERNAL TABLE shop.orders_files
OPTIONS (
  format = 'PARQUET',
  uris = ['gs://my-bucket/exports/*.parquet']
);

External tables are slower and cannot be cached or partition-pruned the same way as native tables. BigLake tables add fine-grained security and caching over open formats such as Iceberg. For recurring imports from Google Ads, YouTube, Amazon S3, Teradata and many SaaS tools, use the BigQuery Data Transfer Service, which schedules and monitors transfers. For database replication, Datastream streams changes from MySQL, PostgreSQL and Oracle into BigQuery.

Ways to ingest

Batch load Files from Cloud Storage
Streaming Rows in near real time
Transfer service Scheduled SaaS imports
External table Query files in place

Prefer batch loads when seconds of delay are fine.

1 / 2

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. Which file format is best when you control the producer and want exact types?

    Parquet and Avro carry the schema, keep types exact and load efficiently.
  2. 2. Re-running a daily load with WRITE_APPEND will:

    Append adds rows every time, so retries duplicate data; use truncate on a partition or a MERGE for idempotency.
  3. 3. What is a downside of --autodetect in production?

    Autodetect samples rows, so write an explicit schema for dependable pipelines.
  4. 4. When is an external table a good fit?

    External tables query files in place; native tables are faster for frequent queries.

Summary

  • Batch load jobs from files are the cheap, standard way to ingest; Parquet and Avro are the best formats.
  • Use bq load or LOAD DATA, and choose write dispositions so retries are safe.
  • Write explicit schemas, with NUMERIC for money and real date types.
  • Validate files first, and MERGE staging tables for upserts.
  • External tables, the Data Transfer Service and Datastream cover other sources.

Next: Scheduled Queries and Routines