BigQuery

BigQuery is Google's serverless, petabyte-scale data warehouse for analytics. With BigQuery, you can run analytics over large amounts of data using standard SQL queries.

Fivetran can sync to BigQuery as frequently as every five minutes.

Supported table types

Fivetran supports two table types for BigQuery destinations. You can load data directly into BigQuery native tables, or store data as Apache Iceberg™ tables that we manage in a Managed Data Lake destination and query the tables from BigQuery.

To use Iceberg tables, you must associate the BigQuery destination with exactly one Managed Data Lake destination that stores its data in Google Cloud Storage (GCS) and has the Update BigQuery Metastore toggle set to OFF. You can select an existing destination or create a new one during setup. One Managed Data Lake destination can be associated with multiple BigQuery or Snowflake destinations that use Iceberg tables. The BigQuery destination provides access to the data stored in the data lake; it does not create another copy of the data in BigQuery storage.

You can't change the table type or the associated Managed Data Lake destination after you set up the BigQuery destination. To do that, create a new BigQuery destination.

Supported deployment models

The supported SaaS and Hybrid deployment models depend on the table type:

Setup guide

Follow our step-by-step BigQuery setup guide to connect your BigQuery data warehouse with Fivetran.

Metadata Sync for Iceberg tables Beta

For BigQuery destinations that use Iceberg tables, Fivetran loads data into the associated Managed Data Lake destination. After the connection sync completes successfully, we run a separate step, called Metadata Sync, to make the latest data available in BigQuery. During Metadata Sync, we create any missing external Iceberg tables in BigQuery and refresh existing tables with the latest metadata from the Fivetran Catalog.

Metadata Sync behavior

Metadata Sync runs automatically after each successful connection sync to the Managed Data Lake destination. It does not have a separate schedule and cannot be run manually.

The destination uses the standard Extract, Process, and Load stages. Each connection's sync history also displays a Metadata Sync stage to show the status of the subsequent Metadata Sync step.

In the BigQuery project you specified in the destination setup form, we create each external table in a dataset named after the connection's schema, with the same table name as in the data lake. If the dataset doesn't exist, we create it in the Data Location you selected for the destination. BigQuery detects the column types of each external table from the Iceberg table schema.

Architecture

• Setup: You link a BigQuery destination to a Managed Data Lake destination by selecting it when you set up the BigQuery destination. This link is tracked at the Managed Data Lake destination level.
• Multiple destinations: A Managed Data Lake destination can be associated with multiple BigQuery or Snowflake destinations that use Iceberg tables. Metadata Sync runs per connection. When a connection completes a successful sync, we sync metadata for that connection's tables to all associated BigQuery and Snowflake destinations.
• Existing tables: When you create a BigQuery destination that uses Iceberg tables and associate it with an existing Managed Data Lake destination, we don't sync existing tables until each connection's next successful sync. The first Metadata Sync for a connection creates external tables for all of the connection's tables, not only the tables updated in that sync.
• Connections: Connections can't belong directly to a BigQuery destination that uses Iceberg tables. If you add a connection from this destination in the Fivetran dashboard, we create the connection in the associated Managed Data Lake destination instead.

Error handling

• If the load to the data lake fails, the sync fails and we skip the Metadata Sync.
• If the load to the data lake succeeds but Metadata Sync fails, the sync remains successful because we successfully loaded the data into the data lake, and the connection continues to sync to it. The connection's sync history indicates that Metadata Sync failed.
• When Metadata Sync fails, we don't run the Quickstart transformations that depend on the affected connection and BigQuery destination.

Deletion

Deleting a BigQuery destination that uses Iceberg tables stops Metadata Sync to that destination. However, deleting the BigQuery destination does not delete:

• The data stored in the data lake.
• The external Iceberg tables and datasets in BigQuery.
• The ongoing data loads from connections into the associated Managed Data Lake destination.

Transformations for Iceberg tables

Iceberg tables support Quickstart transformations. dbt Core transformations aren't available for BigQuery destinations that use Iceberg tables.

Private connectivity to BigQuery

Fivetran's Google network is configured with Private Google Access (PGA), which means traffic between Fivetran and BigQuery is always private when Fivetran runs on Google Cloud (GCP). You can ensure a private connection by configuring your Fivetran destination to run on GCP.

Type transformation mapping

The data types in your BigQuery data warehouse follow Fivetran's standard data type storage.

We use the following data type conversions:

Parse JSON data

Use BigQuery's native JSON functions in Standard SQL to retrieve data stored in JSON-formatted strings.

For example, if you have a table in your BigQuery destination TEST.JSON_PARSING_EXAMPLE (id INT64, json STRING), with the following nested JSON structure:

 {
    "id":1,
     "name": {
     "first_name": "john",
     "last_name": "smith",
     "full_name": "john_smith"
     }, 
     "addresses":[
       {
         "name": "home",
         "city": "oakland",
         "state": "california",
         "country": "usa"
       }, 
       {
         "name": "work",
         "city": "denver",
         "state": "colorado",
         "country": "usa"
       }  
     ]
 }   

To parse the data:

1. Query the data. Execute the following commands:

      select 
           json_value (json, '$.id') as json_id,
           json_value (json, '$.name.first_name') as first_name
      from test.json_parsing_example;
   

   Row	json_id	first_name
   1	1	john

2. Flatten the data. Execute the following commands:

      select 
           json_value(json, '$.id') as json_id,
           json_value(json, '$.name.first_name') as first_name,
           json_value(n, '$.name') as addresses_name,
           json_value(n, '$.city') as addresses_city,
           json_value(n, '$.state') as addresses_state,
           json_value(n, '$.country') as addresses_country
      from test.json_parsing_example,
      unnest (json_query_array (json, '$.addresses')) n;
   

   Row	json_id	first_name	addresses_name	addresses_city	addresses_state	country
   1	1	john	home	oakland	california	usa
   2	1	john	work	denver	colorado	usa

Roles and permissions

The minimum role required for Fivetran to function is BigQuery User. This role provides permissions to:

• run, enumerate, and cancel our own jobs (updates)
• read, query, and enumerate all datasets within a project
• create new datasets within the project and grant the bigquery.dataOwner role for these new datasets

These permissions are required because Fivetran creates its own datasets and modifies them as changes are made to the source. They don't allow Fivetran to modify datasets it does not own.

Roles and permissions required for Iceberg tables Beta

For Iceberg tables, BigQuery reads the data from the GCS bucket of the associated Managed Data Lake destination. In addition to the BigQuery User role, grant the service account that your BigQuery destination uses (the Fivetran service account or your own service account) the Storage Object Viewer role on that bucket.

Roles and permissions required to sync data into pre-existing datasets

To sync data to an existing dataset in your BigQuery project (created by another user or service account), you must grant the Fivetran service account the bigquery.dataOwner role for that dataset.

To grant the bigquery.dataOwner role for an existing dataset, do the following:

1. Log in to the BigQuery console.
2. In the Explorer pane, locate the dataset where you want to sync your data.
3. Click View actions > Share > Manage Permissions for the dataset.
4. Click Add principal.
5. In the New principals field, enter the service account you want to use for your destination.
6. In the Role drop-down menu, select BigQuery Data Owner.
7. Click Save.

If you have datasets that you do not want Fivetran to access, we recommend creating a separate BigQuery project for those datasets.

Data load costs

Fivetran uses SQL queries to merge data into the tables in your destination. Because BigQuery bills for query usage, they charge you when Fivetran loads data in your destination. Partitioned tables significantly reduce costs because they allow Fivetran to quickly identify where to insert data in a table without scanning every row.

Column names

Fivetran ignores the case of column names in your destination tables, as BigQuery is case-insensitive. If your database connection is configured to preserve source naming, we sync the column names from the source without any modification. However, BigQuery does not support duplicate column names, even if the case is different.

BigQuery only supports column names that contain letters (a-z, A-Z), numbers (0-9), or underscores (_). We skip the columns that don't comply with this convention.

Hidden datasets

In BigQuery, a hidden dataset is a dataset whose name begins with an underscore (_). You cannot view the hidden datasets on BigQuery's UI. However, you can query the tables and views in hidden datasets the same way you would in any other dataset.

If your source connection's schema name starts with an underscore (for example, _hubspot_test), then the schema or dataset will be a hidden dataset in your BigQuery destination.

For more information about hidden datasets, see BigQuery's documentation.

Transaction concurrency

If a transaction mutates (updates or deletes) rows in a table, then other transactions or DML statements that mutate rows in the same table cannot run concurrently. BigQuery cancels the concurrent jobs. For more information, see BigQuery's documentation.

To avoid sync failures, make sure no job runs on the datasets and tables while Fivetran is syncing data into them.

Limitations

Column limitations

BigQuery allows a maximum of 10,000 columns per table.

Row size limitations

BigQuery enforces a 100 MB maximum row size. Fivetran evaluates the total row size before applying column-level limits.

• If a row is under approximately 100 MB, we sync variable-length column values without truncation.
• If a row exceeds approximately 100 MB, we apply an 8 MB limit to each variable-length column (STRING, JSON, BINARY, and XML) to help reduce the total row size.

For information about how Fivetran handles values when it applies the column-level limit, see Long value truncation.

Partition modification limitations

BigQuery limits the maximum number of partition modifications. When we detect a weak primary key in a table, we update the data in the destination table using the following steps:

1. Create a copy table that contains all the data of the main table.

2. Delete the rows that are present in the staging table

3. Copy with append all the data from the staging table to the copy table.

4. Copy with truncate all the data from the copy table to the main table.

   If the main table is a column partitioned table, the copy with truncate operation truncates the destination table and copies all data from the copy table to the main table. The operation modifies all the partitions in the main table.

If the sync fails with the QuotaExceededException error, we delete the main table and then copy it back from the copy table to reset the partition quota for the day. You can also resolve this error by:

• Increasing your BigQuery quota
• Reducing your sync frequency

Unstructured files Beta

By default, we store unstructured files in a GCS bucket within the connection's destination schema. You can also specify the folder name to store the unstructured files. The files are stored in the gs://<gcs-bucket-name>/<optional-folder-name>/<schema-name>/<table-name>/ folder path. For more information, see our Unstructured File Replication feature documentation.