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.

Setup guide

Follow our to connect your BigQuery data warehouse with Fivetran.

Private connectivity to BigQuery

Fivetran's Google network is configured with , which means traffic between Fivetran and BigQuery is always private when Fivetran runs on Google Cloud (GCP). You can ensure a private connection by .

Type transformation mapping

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

We use the following data type conversions:

Fivetran Data Type	Destination Data Type	Notes
BOOLEAN	BOOLEAN
SHORT	INTEGER
INT	INTEGER
LONG	INTEGER
BIGDECIMAL	BIGNUMERIC	For source connectors created before August 4, 2021, we convert BIGDECIMAL to NUMERIC.
FLOAT	FLOAT
DOUBLE	FLOAT
LOCALDATE	DATE
LOCALDATETIME	DATETIME
INSTANT	TIMESTAMP
STRING	STRING
JSON	STRING
BINARY	BYTES

Fivetran represents nested JSON (including arrays) as STRING columns in BigQuery. This happens with semi-structured data sources (for example: webhooks, MongoDB, JSON files).

Parse JSON data

Use BigQuery's native 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"
       }  
     ]
 }   
content_copy

To parse the data:

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;
content_copy

Row	json_id	first_name
1	1	john

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;
content_copy

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.

If there are some datasets you don't want Fivetran to access, we recommend that you create separate BigQuery project for them.

For more information, see .

Data load costs

Fivetran uses SQL queries to merge data into the tables in your data warehouse. Because BigQuery bills for , they charge you when Fivetran loads data in your data warehouse. 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

Partition modification limitations

. When we detect a weak primary key in a table, we update the data in the destination table using the following steps:

Create a copy table that contains all the data of the main table.
Delete the rows that are present in the staging table
Copy with append all the data from the staging table to the copy table.
Copy with truncate all the data from the copy table to the main table.
NOTE: 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