Skip to content
Tutorials Center
My learning

Topics

Lesson 8 of 37 ยท Architecture and Setup

Loading and Exporting Data in BigQuery

Learn every way to get data into BigQuery (load jobs, bq load, LOAD DATA, streaming, external tables) and out again (EXPORT DATA), file formats compared, schema autodetect and common load errors.

An empty warehouse is not much use. This lesson covers the main ways to get data in and get data out, which file formats to choose, and how to avoid the errors beginners hit. Loading from files is free in BigQuery when you use the shared pool: you pay for the storage afterwards, not for the load job.

Ways to ingest data

flowchart TD
  A["Files in Cloud Storage"] -->|"batch load job (free)"| T["BigQuery table"]
  B["Local file upload"] -->|"console or bq load"| T
  C["Application events"] -->|"Storage Write API (streaming)"| T
  D["SaaS and databases"] -->|"Data Transfer Service, Datastream"| T
  E["Files left in place"] -->|"external table"| X["Queried without loading"]

Pick batch loading by default; use streaming only when you need data within seconds.

Method Best for Notes
Batch load job Files from Cloud Storage or upload Free with shared slots, atomic (all or nothing)
Storage Write API / streaming Real-time events Costs money, data is visible in seconds
Data Transfer Service Google Ads, YouTube, S3, scheduled copies Managed, scheduled
External table Files you do not want to copy Slower, no caching of the data itself
INSERT DML / CREATE TABLE AS Small edits and derived tables Not meant for bulk row-by-row inserts

File formats compared

Format Schema inside? Compression Verdict
CSV No Weak Universal, but ambiguous (quotes, dates, nulls)
JSON (newline-delimited) Loosely Weak Good for nested data
Parquet Yes Excellent, columnar Best default for large data
Avro Yes Good, row-based Great for streaming pipelines
ORC Yes Excellent Common from Hadoop

Prefer Parquet or Avro: types travel with the file, so there is no guessing and no quoting bugs.

Loading a CSV with bq

Upload orders.csv to a Cloud Storage bucket, then (read-only commands, they need a real project):

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

Or with a schema you control, which is safer than --autodetect:

Terminal
bq load --source_format=CSV --skip_leading_rows=1 \
  shop.orders gs://my-bucket/exports/orders.csv \
  id:INT64,customer_id:INT64,ordered_at:TIMESTAMP,status:STRING,total:NUMERIC

The SQL way uses LOAD DATA:

SQL
LOAD DATA INTO `my-project.shop.orders`
FROM FILES (
  format = 'CSV',
  skip_leading_rows = 1,
  uris = ['gs://my-bucket/exports/orders_*.csv']
);

Use OVERWRITE instead of INTO to replace a table, and the wildcard * to load many files in one job.

Autodetect and its limits

--autodetect samples the file and guesses types. It works for tidy files and fails on tricky ones: a column that has numbers in the first 500 rows and N/A later will be typed INT64 and then fail. A column of ZIP codes such as 02134 can become the integer 2134. For anything repeated, define the schema.

Common load errors

  • Bad row: a text value in a number column. Use --max_bad_records=10 to skip a few, or load into STRING and clean with SAFE_CAST.
  • Wrong column count: unquoted commas inside values. Fix the file or set the quote character.
  • Date format: BigQuery wants YYYY-MM-DD for CSV dates and YYYY-MM-DD HH:MM:SS for timestamps.
  • Location mismatch: the bucket and dataset must be in compatible locations.
  • Encoding: BigQuery expects UTF-8.

The "load into STRING, then clean" approach is a good pattern, because it never loses data. You can rehearse it with SQL on the stand-in, where the raw columns are text:

SQL Stand-in: clean raw text with casts ยท database: shop

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

In BigQuery you would write SAFE_CAST(amount AS NUMERIC) and SAFE.PARSE_DATE(...). The idea is identical: bad values become NULL, and you inspect them rather than losing the whole load.

SQL Stand-in: find the rows that failed cleaning ยท database: shop

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

Streaming and the Storage Write API

When events must be queryable within seconds, use the Storage Write API (the older tabledata.insertAll streaming also exists). Rows land in a streaming buffer first, then are optimised in the background. Caveats: it costs money, and recently streamed rows can be hard to update or delete for a short period. Most teams stream only what truly needs it and batch the rest.

External tables

Instead of loading, define a table that points at files:

SQL
CREATE EXTERNAL TABLE `my-project.lake.clicks`
OPTIONS (
  format = 'PARQUET',
  uris = ['gs://my-bucket/clicks/*.parquet']
);

External tables are convenient for a data lake, but queries are slower and cannot use BigQuery's storage tricks. Load the data when you query it often.

Exporting data

To get data out, use EXPORT DATA (it writes files to Cloud Storage):

SQL
EXPORT DATA OPTIONS (
  uri = 'gs://my-bucket/out/orders_*.csv',
  format = 'CSV',
  header = true,
  overwrite = true
) AS
SELECT id, customer_id, total
FROM `my-project.shop.orders`
WHERE ordered_at >= '2025-01-01';

Or bq extract for a whole table. Export is free, but you pay for the query inside EXPORT DATA. The console can also send results to Google Sheets or Looker Studio, and the console download is limited to small results.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. Which loading method is free when using the shared slot pool?

    Batch loads from Cloud Storage or uploads do not bill for compute; streaming does.
  2. 2. Which format carries its own schema and compresses well?

    Parquet is columnar, typed and compressed, so it is the best default for large loads.
  3. 3. A ZIP code column loads as 2134 instead of 02134. What is the likely cause?

    Define a STRING column for identifiers with leading zeros.
  4. 4. What is a safe way to ingest messy CSV data?

    Staging as text keeps every row and lets SQL handle failures as NULL.

Summary

  • Batch load jobs from Cloud Storage are the free, default way to ingest data.
  • Use Parquet or Avro; define schemas explicitly for CSV.
  • Stream only when you need seconds-level freshness.
  • External tables skip loading but are slower.
  • EXPORT DATA writes query results to Cloud Storage.

Next: SELECT EXCEPT, REPLACE and Friends