How to load data into BigQuery: CSV files, Cloud Storage, Python, SQL and bulk loading

How do I load data into BigQuery?

BigQuery has five realistic loading paths. Use a batch load job for files in Cloud Storage, the Storage Write API for rows that must appear within seconds, the BigQuery Data Transfer Service for scheduled pulls from Google advertising products and other supported sources, Datastream when you are replicating a live operational database, and external tables when you would rather query the data where it already sits. Everything else, including every third-party ETL vendor, is a wrapper around one of those.

Which one you want depends almost entirely on where the data is coming from and how fresh it has to be:

Six ways to load data into BigQuery, by source, best use and the main caveat

Method	Source	Best for	What to know
BigQuery Studio upload	A file on your laptop	One-off loads and a first look at a new file	No code required, but it is a manual action and it never becomes a pipeline
bq load	Local files or Cloud Storage URIs	Scripted loads from a machine you control	The command line workhorse. Autodetect is convenient for a look and risky for production
Load job from Cloud Storage	CSV, JSON, Avro, Parquet, ORC in a bucket	Scheduled bulk loads and historical backfills	Free of charge on the shared slot pool. Capped at 1,500 jobs per table per day
Storage Write API	Rows written directly from an application	Event streams that must appear within seconds	No file step at all, which is why it reaches lower latency and avoids the load job quota
Data Transfer Service	Google Ads, Analytics 4, YouTube, Salesforce, S3, Redshift	Scheduled managed pulls you do not want to build	One directional. It cannot move data out of BigQuery
Datastream	A live Postgres, MySQL, Oracle or SQL Server database	Continuous replication with change data capture	Near real time, tuned with max_staleness. Events cap at 20 MB each

How to load data into BigQuery from a CSV file

For a single file you are exploring, upload it directly in BigQuery Studio and let it create the table. To script the same thing, the command line tool is one line:

bq load \
  --source_format=CSV \
  --skip_leading_rows=1 \
  --schema=order_id:STRING,customer_id:STRING,amount:NUMERIC,created_at:TIMESTAMP \
  analytics.orders \
  gs://my-bucket/orders/*.csv

Note what is not in that command: --autodetect. Autodetect samples the file and infers types from what it sees, which is genuinely useful when you are looking at an unfamiliar export and genuinely dangerous in a pipeline. If the first few hundred rows of an amount column happen to be whole numbers, autodetect picks INTEGER, and every cent in the rest of the file is gone without an error. Write the schema out once. It takes two minutes and it is the cheapest insurance in this whole process.

Three CSV specifics are worth setting deliberately. --skip_leading_rows=1 drops the header. --allow_quoted_newlines is required if any field contains a line break, which is common in address and notes columns. And --null_marker lets you tell BigQuery that the literal string NULL or \N in the export means an actual null rather than four characters of text.

How to load data into BigQuery from Cloud Storage

Cloud Storage is the normal staging area for anything at real volume, and the load itself is free of charge: batch load jobs run on a shared slot pool rather than billing your query capacity. You pay for the storage and, afterwards, for the queries.

A wildcard URI loads every matching file in one job, which is the behavior you want. A single job can reference up to 10,000 source URIs and read up to 10 million files, with a total input size of 15 TB. What it cannot do is run for longer than six hours, so an enormous first backfill should be split by date range into several jobs rather than submitted as one.

If your files are already columnar, use Parquet or Avro rather than CSV. BigQuery reads the schema from the file itself, so there is nothing to autodetect and nothing to get wrong, and the load is faster because the format is already typed and compressed.

How to load data into BigQuery using SQL

You can load without leaving the query editor. LOAD DATA is a DDL statement that reads from Cloud Storage and writes into a table, which means the whole load can live in a scheduled query alongside the transformations that follow it:

LOAD DATA INTO analytics.orders
  (order_id STRING, customer_id STRING, amount NUMERIC, created_at TIMESTAMP)
PARTITION BY DATE(created_at)
CLUSTER BY customer_id
FROM FILES (
  format = 'CSV',
  skip_leading_rows = 1,
  uris = ['gs://my-bucket/orders/*.csv']
);

The reason to prefer this form over bq load is on lines three and four. Declaring the partitioning and clustering at creation time is the difference between a table that stays cheap and one that scans its entire history on every dashboard refresh. BigQuery bills by bytes scanned, so partitioning is not a performance nicety, it is the bill.

Retrofitting it later is possible but tedious, because you have to create a new table and repoint everything that reads the old one. The cost of getting it wrong also hides well: an unpartitioned table shows up as a slightly larger analytics line rather than as an alert, which is why it helps to watch what each dataset actually costs you month to month instead of finding out in a quarterly review.

How to load data into BigQuery using Python

The official client library wraps the same load job API. For a pandas DataFrame, one call handles the serialization, upload and load:

from google.cloud import bigquery

client = bigquery.Client()

job_config = bigquery.LoadJobConfig(
    schema=[
        bigquery.SchemaField("order_id", "STRING"),
        bigquery.SchemaField("amount", "NUMERIC"),
        bigquery.SchemaField("created_at", "TIMESTAMP"),
    ],
    write_disposition="WRITE_APPEND",
)

job = client.load_table_from_dataframe(df, "analytics.orders", job_config=job_config)
job.result()  # wait, and raise on failure

Two things to get right here. First, job.result() is what turns a silent failure into an exception, and it is the line people leave out. Second, resist the temptation to call this inside a loop over batches of rows. Each call is a load job, and 1,500 of them against one table exhausts the daily quota. If you are producing rows continuously, the Storage Write API is the correct tool and it does not touch the load job quota at all.

The BigQuery limits that decide your design

These are the published quotas that most often change an architecture. All were read from the BigQuery quotas and limits reference on 15 August 2026.

BigQuery load job quotas and limits with the practical reason each one matters

Limit	Value	Why it matters
Load jobs per table per day	1,500	Failed jobs count too. This is the ceiling a per-file or per-record loop hits first
Load jobs per project per day	100,000	Replenished every 24 hours. Data Transfer Service runs draw from this same pool
Maximum size per load job	15 TB	Across all input files in one job. The limit does not apply to jobs with a reservation
Load job execution time limit	6 hours	A job fails if it runs longer. Split an enormous first backfill into chunks
Maximum columns per table	10,000	Includes nested and repeated columns, so wide JSON can approach it faster than you expect
CSV and ndJSON maximum row size	100 MB	Individual cells cap at 100 MB as well
Compressed CSV or ndJSON file size	4 GB	Uncompressed goes to 5 TB, so gzipping a huge file can be what breaks the load
Files per load job	10,000,000	A job configuration can also carry up to 10,000 source URIs

There is one quota interaction worth flagging because it catches teams who never wrote a pipeline at all. Data Transfer Service runs execute as load jobs, so they draw from the same 100,000 daily pool as your own work. Google publishes the arithmetic: daily jobs equals transfers times tables times schedule frequency times refresh window. A single Google Ads transfer creates roughly 60 tables and a Search Ads 360 transfer roughly 50, so a marketing team enabling several frequent transfers can consume a large share of the project quota before anyone notices.

How to stream data into BigQuery in real time

When rows have to be queryable within seconds, use the Storage Write API. It writes rows directly into BigQuery with no file staging step, which is why it reaches much lower latency than batching, and it is billed by data ingested rather than being free like batch loads.

Google's own published guidance is the clearest signal here: if you regularly exceed the load job limits because of frequent updates, you should be streaming into BigQuery instead. In other words, hitting the 1,500 job ceiling is not a problem to work around with retries and backoff, it is a sign you picked the wrong ingestion path.

How to load data into BigQuery incrementally

The first historical load is a one-time cost. After that you only want what changed, and there are two ways to know what changed.

A watermark query filters the source on an updated-at column and pulls anything newer than the last successful run. It is simple, works against any source with a reliable timestamp, and has two blind spots: hard deletes never appear, and any row modified by a process that does not touch the timestamp is missed silently.

Change data capture reads the database transaction log instead, so it catches updates and deletes with no cooperation from the application. Datastream is Google's managed option and writes into BigQuery continuously, with max_staleness as the dial between freshness and cost. That dial is real money: CDC ingestion runs background merge jobs, and without a BACKGROUND reservation those merges bill at on-demand rates. A lower staleness value means fresher data and more frequent merges.

Whichever you choose, land rows in a staging table and MERGE into the target on a stable business key. A load you can safely run twice is the difference between a failed run being a non-event and a failed run duplicating revenue in a report.

How to load data into BigQuery from a database

If the source is a live Postgres, MySQL, Oracle or SQL Server instance, do not write the loader yourself. The extraction is the easy part; what you would be signing up to maintain forever is the incremental logic, the schema drift handling, the retry behavior and the alerting.

Datastream covers those sources natively, along with MongoDB, Spanner, Salesforce and Workday, and writes to BigQuery, Cloud Storage or Apache Iceberg tables. One limit to design around: a single event caps at 20 MB for BigQuery destinations, so a table with very large blob columns needs a plan. Third-party platforms cover a wider catalog of SaaS sources, and our own per-source guides walk through the field mappings and type conversions for each specific pair.

Errors you will actually hit on a first load

Six failure modes cover most first-week problems. Note that only two of them produce an error message. The rest load successfully and are wrong, which is why reading what landed matters more than watching for a red status.

Common BigQuery load errors, their causes and how to fix each one

Symptom	Cause	Fix
Quota exceeded: too many table update operations	More than 1,500 load jobs against one table in a day, often a loop firing one job per file	Batch the files into fewer, larger jobs, or switch that table to the Storage Write API
Decimals silently lose their fraction	Money loaded into INT64, or autodetect picking INTEGER from a column whose first rows were whole numbers	Declare NUMERIC(19,4) explicitly in the schema instead of relying on autodetect
Timestamps shift by several hours	The source carried an offset and the target column is DATETIME, which stores no time zone	Use TIMESTAMP when the offset matters. DATETIME is the right choice only for wall clock values
Invalid field name	A source column contains a space, a hyphen or a leading digit	Rename in the load schema. BigQuery field names allow letters, numbers and underscores, and cannot start with a number
Row larger than the maximum allowed size	A single CSV or ndJSON row exceeded 100 MB, usually one enormous embedded JSON blob	Split the oversized field into its own table, or write it through the Storage Write API
Query costs jumped after the load	The table was created without partitioning, so every query scans the full history	Partition on an ingestion date or event timestamp and cluster on the columns you filter by

How to import data into BigQuery from Google Sheets

Sheets is a supported external data source, so you can query a spreadsheet in place without loading it. Create an external table pointing at the sheet URL and BigQuery reads it live, which is genuinely useful for small reference data such as a mapping of account codes to regions that a finance person maintains by hand.

The tradeoff is that queries are slower and the sheet becomes a production dependency that anyone with edit access can break. For reference tables that change monthly, that is a fine trade. For anything a dashboard depends on hourly, load a snapshot into a native table on a schedule instead.

The same caution applies to spreadsheet exports generally. Dates rendered as serial numbers, numbers stored as text with thousands separators, and leading zeros stripped from ZIP or account codes all load without complaint and are all wrong.

Choosing between building this and buying it

A script that loads one CSV is an afternoon. A pipeline that keeps a live source current is a standing commitment: someone owns the incremental logic, the schema changes, the credential rotations and the pager. That cost is real and rarely appears on the plan.

Build when the source is unusual, the transformation is genuinely custom, or you already run a data platform team. Buy when the sources are common ones, the requirement is dependable scheduled syncs, and the honest alternative is an engineer maintaining glue code between other projects. If you are weighing the vendors, our comparison of BigQuery ETL tools lines up the native Google services against the third-party platforms by billing model and best fit, and change data capture tools covers the log-based options in more depth.

Adapters sits in the middle of that: visual field mapping so an analyst can change a mapping without a deploy, scheduled syncs with retries and per-record error logs, and a flat monthly price rather than a row meter you have to forecast. If your warehouse is Snowflake instead, the same walkthrough for that platform is how to load data into Snowflake, and ETL vs ELT covers why almost everyone lands raw and transforms afterwards.