Sources
BigQuery
Load BigQuery (beta) tables, views and query results through the Storage Read API: connections, choosing tables, query tables, cursors, types, cost and permissions.
This source is in beta: it is tested, but gets less everyday use than the other connectors, so expect rough edges, and please report them in GitHub issues.
A bigquery source loads tables, views and query
results from BigQuery. SQLLoad reads the rows as Arrow through the
Storage Read API, several streams at
once, and loads them into the source’s target. No bucket is needed.
[connections.bq]kind = "bigquery"location = "EU"service_account_key = "${ENV:BIGQUERY_KEY}"
[sources.marts]kind = "bigquery"connection = "bq"description = "Tables of the marts datasets."include = ["marts.*"]exclude = ["marts.tmp_*"]write_strategy = "replace"
[sources.marts.tables."marts.events"]description = "Events, one row per event."cursor_column = "event_at"write_strategy = "append"
[sources.marts.tables.recent]description = "Orders of the last week, with their customer."sql = "SELECT o.*, c.name FROM marts.orders o JOIN marts.customers c USING (customer_id)"cursor_column = "ordered_at"write_strategy = "append"$ sqlload plan -v…Loads (2) sqlload load├── raw.marts__events append (cursor: event_at)│ ├── source bigquery marts marts.events│ └── reads event_at > 2026-11-08 23:59:58+00 (warehouse maximum)└── raw.marts__recent append (cursor: ordered_at) ├── source bigquery marts query recent └── reads ordered_at > 2026-11-08 23:41:07+00 (warehouse maximum)…Connection
Section titled “Connection”A source uses a bigquery connection, the same kind a
BigQuery target uses. Read sessions and query jobs are billed to the connection’s project, and
query jobs run in its location. The source’s own project setting reads tables that live in
another project:
| Key | Meaning |
|---|---|
project |
The project the tables live in; the connection’s when omitted. |
Choosing tables
Section titled “Choosing tables”include and exclude are dataset.table patterns, such as marts.*. BigQuery names are
case-sensitive, so the patterns are too. SQLLoad lists the datasets and tables from their metadata,
which bills nothing. A [sources.marts.tables."dataset.table"] entry sets options for one table; on
its own, it doesn’t add the table.
A source needs at least one include pattern or one query table. A merge table merges on the
primary key the table declares with unique_key = "{primary_key}", or on the columns you name.
BigQuery doesn’t enforce primary keys, and plan refuses "{primary_key}" when a key
column is NULLABLE, as it usually is; name the unique_key columns then (see
NULLs in keys). A table’s description becomes
the table’s description.
How tables are read
Section titled “How tables are read”- Tables are read through a read session with the selected columns, split into up to eight streams that SQLLoad reads in parallel. A cursor window becomes the session’s row filter, so BigQuery sends only the rows in it. Each table is read as of the moment its load starts, so all of its cursor windows see the same data; different tables of one run can be read moments apart.
- Views, materialized views and external tables are read with a query job, whose result is then read through a read session in the same way. They read the data as it is when the job runs.
- Query tables run as a query job too, and also read the data as it is when the job runs.
BigQuery loads the rows itself
Section titled “BigQuery loads the rows itself”When the target is BigQuery and writes through the source’s own connection, no rows pass through
SQLLoad. BigQuery selects each batch’s rows with one INSERT ... SELECT query job into the load’s
staging or build table, which then publishes as any other load does. A table is read as of the
moment its load starts, as a read session reads it, and views, query tables and tables with where
run their query in the same job. The rows load exactly as SQLLoad’s reader would load them, with
the same types.
plan shows the route:
route BigQuery INSERT ... SELECT on the source's connection (nothing through the loader)SQLLoad still reads the rows itself, and plan -v on the table says why, for a table that:
- has a
STRUCT,RANGE,GEOGRAPHYor repeated column, which SQLLoad converts to JSON or text; - pins a column to another type than its own;
- is in another location than the connection’s, which a read session reaches but a query job doesn’t;
- has a
mask,parse,transformor checks, which SQLLoad applies to the rows.
A target on another bigquery connection, even with the same key, reads through SQLLoad as before.
Query tables
Section titled “Query tables”A table entry with sql is a query table: its rows are the query’s result, and the entry’s key is
its name. At plan, SQLLoad describes the query with a dry run, which reads and bills nothing.
cursor_column names a column of the query’s result. SQLLoad wraps the query and filters on the
cursor outside it, so the query needs no placeholder. Write the query in GoogleSQL, naming tables
as dataset.table in the connection’s project, or as `project.dataset.table` for another
project, including the source’s project.
Filtering rows
Section titled “Filtering rows”where narrows a table to the rows that match a condition, written in GoogleSQL:
[sources.marts]where = "region = 'EU'"
[sources.marts.tables."marts.orders"]where = "region = 'EU' AND status != 'void'"SQLLoad ANDs it into every SELECT it reads the table with, next to the cursor filter: cursor
reads, batch_size windows, --reload, replace, and the key scan of load --sync-deletes. The
table keeps its primary key, so unique_key = "{primary_key}" still works, and the database can
use its indexes. On a source it applies to every table include matches; a table’s own where
replaces it. plan checks the condition on the source with a dry run, which reads and bills nothing, and shows it on a filter line, so a typo
fails plan, naming the table.
A row that later stops matching isn’t removed from a merge, append or delete_insert table; it
stops being updated. replace drops it, and so does deletes with load --sync-deletes (see
Rows deleted at the source).
A query table refuses where: put the condition in its sql.
A table with where is read through a query job, like a view, rather than directly by the Storage Read
API, so the filter can use any GoogleSQL. The job is billed for the bytes it scans, and reads the
table as it is when the job runs.
| BigQuery type | Loads as |
|---|---|
INT64 |
int |
NUMERIC(p,s), BIGNUMERIC(p,s), p up to 38 |
decimal(p,s); a NUMERIC with no precision is decimal(38,9) |
FLOAT64 |
float |
BOOL |
bool |
STRING, STRING(n) |
text, varchar(n) |
BYTES |
binary |
DATE, TIME, DATETIME |
date, time, timestamp_ntz |
TIMESTAMP |
timestamp_tz, in UTC |
JSON, STRUCT, RANGE |
json |
ARRAY<T> |
json, or array(T) when pinned |
GEOGRAPHY |
text, as WKT |
BIGNUMERIC with no precision |
none: pin a decimal(p,s) its values fit, or text |
INTERVAL |
none: pin text, which writes it as 14 mons 3 days 4 hours 5 mins 6.500000000 secs, or exclude the column |
Pin a type to load a column another way, such as columns = { total = "decimal(18,2)" }. A value
that doesn’t convert to the pinned type fails the read, naming the column, rather than loading as
NULL. A value with more decimal places than a pinned decimal keeps, or a
fraction in a pinned integer, fails the read too. NaN and infinities inside a repeated
FLOAT64 load as null in its JSON. See
Types.
Read sessions bill the bytes of the columns and rows they read; the first 300 TB a month are free. Views and query tables also bill their query job. A table BigQuery loads itself bills its query job’s scan instead of a read session. Listing tables, describing them and dry runs bill nothing.
A table read from a cursor with null_cursor = "fail" (the default) and a cursor column
that is NULLABLE first counts its rows with a NULL cursor in a query job, which bills a scan of the
cursor column, even for a table otherwise read through a read session. null_cursor = "ignore" or
"include" skips the count, as does a REQUIRED cursor column. See
NULL cursor values.
Permissions
Section titled “Permissions”The service account needs bigquery.readSessionUser on the connection’s project, and
bigquery.dataViewer on the datasets it reads. Views and query tables also need
bigquery.jobUser on the connection’s project. So does a table read from a
NULLABLE cursor column under the default null_cursor = "fail", whose count of NULL cursors
runs as a query job (bigquery.jobs.create).