Advanced Analytica Advanced Analytica: IBOM
← All connectors
BigQuery logo

Source connector

BigQuery

Set up BigQuery as a source and move its data into your chosen destination.

Version 1.0.2 certified generally available

description: >- BigQuery is Google Cloud’s fully managed, highly scalable and cost-effective data warehouse.

BigQuery

The BigQuery source connector reads tables, views, materialized views, external tables and table snapshots from Google BigQuery datasets. It supports full refresh and incremental syncs. It discovers nested STRUCT and ARRAY schemas and primary key constraints. Checkpoints let large tables resume after an interruption. On Airbyte Cloud, it reads tables through parallel streams using the BigQuery Storage Read API.

Version 1.0.0 rebuilt the connector on Airbyte’s Bulk CDK. It accepts the same configuration properties as earlier versions. See the migration guide for details of the changes.

Features

FeatureSupportedNotes
Full refresh syncYes
Incremental sync - appendYesCursor based, see Incremental sync
Change data capture (CDC)NoBigQuery has no change log for the connector to read
NamespacesYesEach dataset is a namespace
Primary keysYesRead from the table’s PRIMARY KEY constraint
Nested schemasYesSTRUCT and ARRAY columns keep their structure in the catalog
Storage Read APIYesAirbyte Cloud only, see Read throughput
SSLYesAll traffic to the BigQuery API uses HTTPS

The connector is read-only and never writes to your project. On Airbyte Cloud, it reads table data through the Storage Read API. Elsewhere, it uses SELECT queries. It runs SELECT queries for views and incremental syncs.

Get started

Requirements

  • A Google Cloud project with the BigQuery API enabled.
  • A service account with read access to the datasets you want to sync and permission to run query jobs.
  • For high-speed Airbyte Cloud reads, the service account also needs permission to create Storage Read API sessions.
  • A JSON key for that service account.

Service account

Follow Google’s Create service accounts guide to create a dedicated service account for Airbyte. Then grant it the following roles. A dedicated account keeps permissions and auditing simple.

RoleWhere to grant itWhy
BigQuery Data Viewer (roles/bigquery.dataViewer)The project, or each dataset to syncLists datasets and tables, reads table schemas and table data
BigQuery Job User (roles/bigquery.jobUser)The project that runs the query jobs (Project ID, or Job Execution Project ID when set)Runs the SELECT queries for views, incremental syncs and the fallback read path
BigQuery Read Session User (roles/bigquery.readSessionUser)The project that runs the query jobsAirbyte Cloud only. Reads tables through the Storage Read API, the high-speed path. See Permissions for high-speed reads

High-speed read permissions

On Airbyte Cloud, the connector reads tables through the BigQuery Storage Read API by default. Self-managed Airbyte uses the query API for every table and doesn’t need the permissions below. The Storage Read API makes large table reads faster. It needs permissions that the query path doesn’t:

PermissionIncluded in roleWhere it is needed
bigquery.readsessions.createBigQuery Read Session UserThe project that runs the jobs, which is Project ID or, when set, Job Execution Project ID
bigquery.readsessions.getDataBigQuery Read Session UserThe same project
bigquery.tables.getDataBigQuery Data ViewerThe datasets to sync

Google bills Storage Read API usage by bytes read, separately from the bytes processed by a query. See BigQuery pricing.

While Use the BigQuery Storage Read API is on, the connection test opens a read session to verify these permissions. If it fails, the message identifies the service account, missing role and project, or the API you must enable. Grant the role, or turn off the setting to use the query path. A sync repeats this check once when it starts. If a permission was revoked after setup, the sync doesn’t fail. Instead, the connector logs a warning naming the missing permission and uses the query path. This path is much slower and scans the table for every query. Grant the role before syncing tables larger than a few gigabytes.

The connector also uses the bigquery.datasets.get, bigquery.tables.list, bigquery.tables.get and bigquery.jobs.create permissions.

Service account key

Follow Google’s Create and delete service account keys guide and select the JSON key type. Download the key file, then paste its contents into the Service Account Key JSON field. Google shows the key only once. Keep the file until you’ve set up the source, then delete it from your computer.

Set up the BigQuery source

  1. In the Airbyte UI, click Sources, then + New source.
  2. Select BigQuery from the source type list.
  3. Complete the connection fields:
FieldRequiredDescription
Project IDYesThe Google Cloud project that owns the datasets to read.
Dataset IDNoRestricts the source to one dataset. Leave it empty to discover every dataset of the project. Set it on projects with a large number of datasets, because discovery lists every table of every dataset it can see.
Service Account Key JSONYesThe contents of the JSON key file.
Job Execution Project IDNoAdvanced. The project that runs, and is billed for, the query jobs. Use it to keep Airbyte’s query quota and cost apart from the data project, or to read from a project where the service account only has data access. Defaults to Project ID.
Max Concurrent Queries to DatabaseNoAdvanced, Airbyte Cloud only. How many queries and Storage Read API read streams the connector runs at the same time, across all tables. Leave it empty, which is recommended, to let Airbyte optimize concurrency for best performance: it runs one per data channel in speed mode, and one at a time otherwise. On self-managed Airbyte the field can’t be edited and is fixed at 1.
Use the BigQuery Storage Read APINoAdvanced, Airbyte Cloud only, on by default. Reads tables through the Storage Read API and streams query results through it. Turn it off to read everything through the query API, which is much slower on large tables. On self-managed Airbyte the field can’t be edited and stays off: every table is read through the query API. See Read throughput.
  1. Click Set up source. The connection test lists the datasets and tables, then runs a trivial query. It fails with Discovered zero tables if the dataset or entire project contains no tables.

Replication

Discovered streams

Each dataset becomes a namespace. Each table, view, materialized view, external table and table snapshot becomes a stream. The connector skips BigQuery ML models and tables without columns. Column names and types come from table metadata. This metadata includes nested fields in STRUCT columns and the element type of ARRAY columns. A table with a PRIMARY KEY constraint reports it as the source-defined primary key. BigQuery declares this constraint but doesn’t enforce it.

Full refresh

On Airbyte Cloud, a full table refresh opens a Storage Read API session. This session splits the table into read streams. A read stream is BigQuery’s unit for parallel reading. It isn’t related to an Airbyte stream. The connector reads these streams in order, processing up to Max Concurrent Queries to Database at once. It requests many small streams of about 8 GiB each, providing frequent checkpoints. Each completed read stream becomes a checkpoint.

The Storage Read API can’t read views, materialized views, external tables or table snapshots. The connector reads each one using a single SELECT query. It also uses this approach for every self-managed table or when the Storage Read API is unavailable. On this fallback path, a table with a single-column primary key is divided into roughly equal key ranges. The connector calculates these ranges using APPROX_QUANTILES over the key column. Each range becomes one query and one checkpoint. Tables without this type of key are read in one query.

Incremental sync

Incremental sync uses a cursor column that you choose for each stream. The first sync reads the entire table in the same way as a full refresh. On Airbyte Cloud, it uses parallel Storage Read API streams. This means a terabyte table’s first sync takes minutes rather than days. For consistency, the connector pins the read to one table version. It selects a snapshot time a couple of seconds in the past. It opens the read session using the table at that time. BigQuery time travel provides the cursor’s maximum from the same time. That maximum becomes the cursor checkpoint after every read stream completes. Later syncs query rows with cursors above the checkpoint and no higher than the table’s current maximum. The highest value read becomes the next checkpoint. Rows with a NULL cursor are never read.

Choose a cursor column whose values only increase and are never updated. An insertion timestamp or sequence number works well. If the table is partitioned or clustered on this column, BigQuery prunes the scanned data. This reduces the bytes billed for each incremental sync.

You can use the following column types as cursors:

Cursor column typeNotes
INT64, NUMERIC, BIGNUMERIC, FLOAT64
STRINGOrdered as BigQuery orders strings
DATE, DATETIME, TIME, TIMESTAMPCompared at full microsecond precision

You can’t use BOOL, BYTES, JSON, STRUCT or ARRAY columns as cursors. GEOGRAPHY, INTERVAL and RANGE columns appear in the cursor list because they’re read as strings. However, they haven’t been validated as cursors. Prefer one of the types in the table.

State and resuming

The connector emits state while reading, not only when a sync ends. For Storage Read API tables, the state identifies the read session and last stream completed in order. It also records the row offset for every read stream still in progress. A failed or cancelled sync resumes from that exact point. An incremental stream’s first sync also records its snapshot time and cursor maximum at that time. A resumed sync therefore keeps the same table version and checkpoint. A read session remains valid for six hours after opening. A full refresh resumed later starts reading the table again. An incremental first sync reopens a session at its recorded snapshot time. BigQuery provides this within the table’s time travel window of two to seven days. The connector takes a new snapshot only after that window. On the query path, state stores the last completed full-refresh key range or last incremental cursor value. Resetting the connection’s state makes the next sync start from the beginning.

The connector also reads state saved by versions before 1.0.0. A stream with earlier state resumes after its saved cursor value, instead of reading the table again. Its next state uses the new format. The stream is read again in full if the configured cursor column differs from the saved state. The same applies if the saved value can’t be interpreted as the column’s type.

Read throughput

The Storage Read API path and concurrent reads are available only on Airbyte Cloud. Self-managed Airbyte reads each table through the query path, one at a time.

The Storage Read API delivers table data as compressed Arrow batches across many read streams at once. BigQuery serves these streams without running a query. The query path handles views, incremental syncs and fallback reads. It pages results through the BigQuery REST API, a few thousand rows at a time. It also scans the table for every query. The following results were measured using a 515 GiB table with 275 million rows:

Read pathThroughputTime to read the table
Storage Read API, four read streamsat least 480 MB/s of table datawell under an hour, depending on the worker’s resources
Query API, four queries at a timeabout 3.7 MB/s per queryabout ten hours

On Airbyte Cloud, query results also stream through the Storage Read API when the connector has the required permissions. Views and incremental syncs benefit too. On a 5.2 million-row, 1.3 GB test table, one query took about 15 minutes through the REST API. The same query took under a minute through the Storage Read API. Rows and values are identical on every path. See Permissions for high-speed reads for the required permissions.

Billed bytes

A full table refresh through the Storage Read API reads the table once. Billing covers Storage Read API bytes, not query bytes. Views, incremental syncs and fallback reads run queries. Their processed bytes follow BigQuery’s on-demand pricing, or your reservation. An incremental query reads the cursor range. Each fallback key range scans the entire table unless it’s clustered or partitioned on the key. Set Job Execution Project ID to move query costs and quota to another project.

Data type mapping

BigQuery column types map to the following Airbyte types. The connector’s field type tests cover the values produced by every type.

BigQuery typeAirbyte typeNotes
BOOLboolean
INT64 (INT, SMALLINT, INTEGER, BIGINT, TINYINT, BYTEINT)integer
FLOAT64number
NUMERIC (DECIMAL)numberExact, written in plain decimal notation
BIGNUMERIC (BIGDECIMAL)numberExact, written in plain decimal notation
STRINGstring
BYTESstringBase64 encoded
DATEdateExact for every date, including dates before 1582-10-15
DATETIMEtimestamp without timezoneMicrosecond precision
TIMEtime without timezoneMicrosecond precision
TIMESTAMPtimestamp with timezoneUTC, microsecond precision
JSONjsonAny JSON value: object, array, string, number or Boolean
GEOGRAPHYstringWell-known text (WKT)
INTERVALstringBigQuery’s canonical interval text
RANGE<T>stringBigQuery’s range text
STRUCTobjectNested fields keep their own types in the schema
ARRAY<T>arrayItems keep the type of T

Upgrade from versions before 1.0.0

Version 1.0.0 keeps the existing configuration properties and understands incremental state from earlier versions. However, it types date, time, JSON and bytes columns. It carries nested schemas for STRUCT and ARRAY columns and reports primary keys. It also limits incremental cursors to supported column types and requires a service account key. These changes affect schemas for existing connections. You must refresh every connection’s source schema after upgrading. The migration guide lists every change, the required post-upgrade steps and the downstream impact.

Limits and troubleshooting

  • No change data capture. Deleted rows aren’t detected. Updated rows are detected only when the cursor column changes.
  • Discovered zero tables during connection testing means the service account can’t see any tables. This applies to the dataset, or the project when Dataset ID is empty.
  • Discovery of a whole project is slow when the project has thousands of datasets because tables are listed one dataset at a time. Set Dataset ID.
  • Access Denied, PERMISSION_DENIED or Not found errors indicate a missing role on the data or job project. Check the roles under Service account. Remember that Job Execution Project ID also needs the BigQuery Job User role.
  • The connection test fails with a message about the BigQuery Storage Read API when Use the BigQuery Storage Read API is on and the service account lacks a permission under Permissions for high-speed reads. It can also mean the API isn’t enabled in the project running the jobs. The message identifies the required fix. Apply it, or turn off the setting.
  • A warning about the Storage Read API at the start of a sync means the permission was lost after the connection test passed. The sync completes through the query path, but large tables take much longer.
  • Read sessions expire after six hours. A full refresh resumed after more than six hours reads the table again from the start. See State and resuming for incremental first syncs.
  • invalid_grant or No valid credentials provided errors mean Google rejected the service account key. The key was deleted or disabled, or the JSON wasn’t pasted completely. Create a new key and paste the entire file.
  • GEOGRAPHY, INTERVAL and RANGE cursors haven’t been validated. Use one of the supported cursor types.

IP allow list

If you use Airbyte Cloud and your organisation restricts access by IP address, add the Airbyte Cloud IP addresses to your allow list.

Changelog

Expand to review
VersionDatePull RequestSubject
1.0.22026-10-2289641Upgrade to Bulk CDK 1.2.3: fix critical CVEs in Apache MINA (mina-core 2.0.31), MINA SSHD 2.20.0 and BouncyCastle 1.86; pin httpcore5 5.4.4.
1.0.12026-10-0287654Fail the connection test with an actionable error when Use the BigQuery Storage Read API is on but the service account lacks the Read Session User role or the API is not enabled; fall back to the query API during a sync even when the catalog holds only views
1.0.02026-10-0286950Rebuild on the Bulk CDK: Storage Read API reads with resumable read streams (Airbyte Cloud only), typed date, time and JSON columns, nested schemas, primary keys, cursor incremental reads and speed mode. See the migration guide
0.4.52026-01-2172203Increase integration test timeouts from 1 to 10 minutes
0.4.42025-07-1062911Convert to new gradle build flow
0.4.32024-12-1949875Use a base image: airbyte/java-connector-base:1.0.0
0.4.22024-02-2335503Source BigQuery: replicating RECORD REPEATED fields
0.4.12024-02-0134453bump CDK version
0.4.02023-12-1833484Remove LEGACY state
0.3.02023-06-2927737License Update: Elv2
0.2.32022-10-1415535Update incremental query to avoid data missing when new data is inserted at the same time as a sync starts under non-CDC incremental mode
0.2.22022-09-2216902Source BigQuery: added user agent header
0.2.12022-09-1415668Wrap logs in AirbyteLogMessage
0.2.02022-07-2614362Integral columns are now discovered as int64 fields.
0.1.92022-07-2114574Removed additionalProperties:false from JDBC source connectors
0.1.82022-06-2113864Updated stacktrace format for any trace message errors
0.1.72022-04-1311484BigQuery connector escape column names
0.1.62022-02-1410256Add -XX:+ExitOnOutOfMemoryError JVM option
0.1.52021-12-238434Update fields in source-connectors specifications
0.1.42021-09-30#6524Allow dataset_id null in spec
0.1.32021-09-23#6051Handle NPE dataset_id is not provided
0.1.22021-09-17#6135🐛 BigQuery source: Fix nested structs
0.1.12021-07-27#4981🐛 BigQuery source: Fix nested arrays
0.1.02021-07-22#4457🎉 New Source: Big Query.
Advanced Analytica

Most businesses are sitting on information they can’t use at speed. The people who hold it are busy, and when they leave, the knowledge goes with them. The documents are static, and the processes around them weren’t designed for AI.

Advanced Analytica works with these businesses to turn that information into structured data assets. Once they're integrated, AI systems can govern it, and the business can use it. We bring in the strategy, the specification and the AI skills to rework how the business operates.

MOVE FAST. STAY SAFE.