BigQuery

From sqlload 0.30.0, 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
…
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

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

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 from 0.51.0 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

• 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.

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.

Types

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. From 0.45.0, a value with more decimal places than a pinned decimal keeps, or a fraction in a pinned integer, fails the read too; before, it was rounded half away from zero, and a rounded cursor skipped rows on the next read. NaN and infinities inside a repeated FLOAT64 load as null in its JSON. See Types.

Cost

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. Listing tables, describing them and dry runs bill nothing.

From 0.51.0, 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

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. From 0.51.0, 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).