Skip to main content

Overview

Integrate.io uses BigQuery’s streaming insert API to ingest data into BigQuery. This requires billing to be enabled on the Google Cloud project.

Requirements

  • Billing must be enabled on the Google Cloud project.
  • Ensure you have permission to create a service account with the BigQuery Data Editor role on the project.

Setting up BigQuery for ELT & CDC

Create service account

Creating a service account for FlyData to use requires the BigQuery Data Editor role.
1
Go to your project’s service accounts page and click + CREATE SERVICE ACCOUNT.
Google Cloud service accounts page with Create Service Account button
2
Enter the Service account name and add a description.
Entering service account name and description
Click CREATE AND CONTINUE.
3
Select the BigQuery Data Editor role.
Selecting the BigQuery Data Editor role for the service account
4
Select the BigQuery Job User role.
Click CONTINUE.
5
Click DONE.
6
The service account should have been created successfully. Find it in the service accounts list and click it.
Service accounts list showing the newly created account
7
Go to the KEYS tab.
8
Click ADD KEY and select CREATE NEW KEY.
Keys tab with Add Key dropdown showing Create New Key option
9
Select JSON and click CREATE.
Selecting JSON key type for the service account credentials
10
A JSON file should have been downloaded to your system. Upload it.We will fetch the client_email and private_key properties from the JSON credential file.

Limitations

BigQuery doesn’t support deduplication. Instead, the following columns are added to the destination table for future deduplication on the customer’s side. __offset - Offset column. Keeps track of the offset in incremental order to determine which existing row is the latest.
__op - Operation column. Tells what kind of operation is performed for a particular row (0 - Created, 1 - Inserted, 2 - Updated, 3 - Deleted)
This query is to list the de-duplicated records:
This query is to upsert a staging table that Integrate.io is syncing to, into a production table used for querying:
After deleting tables on BigQuery, we have to wait (5+ minutes) to let the change propagate to all the machines in the BigQuery cluster. Otherwise, it might lead to some rows missing. This happens on resync requests. Please contact support@integrate.io to assist with the resync timing of user tables.

Connection validation

Integrate.io validates the BigQuery connection when you click Test Connection and each time a pipeline starts. Validation runs these checks with the service account credentials:
  • Checking if dataset exists
  • Checking for permissions to create table
  • Checking for permissions to delete table
  • Checking for permissions to insert data using streaming API
  • Checking for permissions to create Query Jobs
How Integrate.io reports a failed check depends on the error BigQuery returned:
  • Temporary errors. If BigQuery returns a temporary error, such as an internal error or a rate limit, the check retries for a few seconds. If it still fails, Integrate.io labels the check (Unverified) with a yellow warning icon. Unverified checks do not fail the connection test and do not stop the pipeline from starting. The pipeline retries temporary errors on its own when it writes data.
  • Permission errors. If BigQuery denies access, the check fails and the pipeline does not start. Verify that the service account has the BigQuery Data Editor and BigQuery Job User roles.
  • Billing errors. If billing is disabled on the Google Cloud project, the streaming insert check fails and the pipeline does not start. Enable billing on the project.
Failure messages include the HTTP status code and the BigQuery error reason, for example internalError or accessDenied, so you can tell a temporary outage apart from a configuration problem.

Table naming

Source table names are converted to snake case when the destination tables are created in BigQuery. The conversion follows these rules:
  • All letters are lowercased.
  • An underscore is inserted at a lowercase-to-uppercase boundary (for example, MyTable becomes my_table).
  • An underscore is inserted at a digit-to-letter transition (for example, A100B becomes a100_b).
  • No underscore is inserted at a letter-to-digit transition (for example, ITEM01 stays item01).
  • Non-alphanumeric characters such as spaces and hyphens are replaced with underscores.
Note that the digit rule is asymmetric: a name is split only where a digit is followed by a letter, never where a letter is followed by a digit. This mainly affects short, code-style table names. Because of the lowercasing, source tables whose names differ only by case (for example, Orders and ORDERS) map to the same BigQuery table name.

Data Type Mapping - BigQuery

IP Allowlist

Initial Sync Process

Last modified on October 27, 2026