TopolearnGetting data in and out efficiently, and knowing which paths are free, shapes every BigQuery pipeline.
BigQuery offers several ways in, with different cost and latency.
Batch load jobs read files from Cloud Storage (or local upload) in CSV, newline-delimited JSON, Avro, Parquet or ORC. They are free on the shared slot pool and atomic: the whole job succeeds or nothing is written. Run them with bq load, the console, client libraries, or SQL: LOAD DATA INTO ds.t FROM FILES (format = 'PARQUET', uris = ['gs://bucket/path/*.parquet']). Self-describing formats like Parquet and Avro carry their schema; for CSV and JSON give a schema or use --autodetect. Choose WRITE_APPEND or WRITE_TRUNCATE (LOAD DATA OVERWRITE).
External tables query files in place without loading: CREATE EXTERNAL TABLE ds.ext OPTIONS (format = 'PARQUET', uris = [...]). Handy for occasional access, but slower, with fewer optimizations. BigLake tables add fine-grained security and caching over open formats such as Apache Iceberg.
Streaming: the Storage Write API ingests rows with low latency and exactly-once semantics, and is what connectors such as Dataflow use. The older insertAll streaming API still exists. Both are billed per volume.
Other routes: scheduled queries and the Data Transfer Service (SaaS sources, other warehouses), Datastream for database CDC, and federated queries to Cloud SQL or Spanner.
Out: EXPORT DATA OPTIONS (uri = 'gs://bucket/out/*.parquet', format = 'PARQUET') AS SELECT ... writes query results to Cloud Storage; bq extract exports a table.
Example
-- Load Parquet files from Cloud Storage into a table (free load job)
LOAD DATA INTO shop.orders
FROM FILES (
format = 'PARQUET',
uris = ['gs://my-bucket/orders/2025-06-*.parquet']
);
-- Query CSV files in place without loading
CREATE OR REPLACE EXTERNAL TABLE shop.ext_returns
OPTIONS (format = 'CSV', uris = ['gs://my-bucket/returns/*.csv'], skip_leading_rows = 1);
SELECT COUNT(*) FROM shop.ext_returns;
-- Export query results back to Cloud Storage
EXPORT DATA OPTIONS (
uri = 'gs://my-bucket/exports/big_orders_*.parquet',
format = 'PARQUET', overwrite = true
) AS
SELECT * FROM shop.orders WHERE amount > 1000;
-- CLI equivalent of the load:
-- bq load --source_format=PARQUET shop.orders "gs://my-bucket/orders/2025-06-*.parquet"Remember
- Batch loads from Cloud Storage are free and atomic
- External and BigLake tables query files in place
- Storage Write API for low-latency streaming; EXPORT DATA for output
Watch out for
- Streaming data that only needs to be fresh daily, paying for streaming instead of free batch loads.