Updated November 28, 2023
BigQuery is a fully-managed, serverless data warehouse and analytics platform provided by Google Cloud. It is designed to handle and analyze large datasets using SQL queries. The Fivetran BigQuery connector allows you to sync tables and views from your BigQuery dataset to your destination.
TIP: If you want to sync multiple BigQuery datasets, we recommend that you create a connector for each dataset.
|All tables and fields
|All tables, views and fields
|Connector table & view level
|Fivetran data models
|Private Google Access
Follow our step-by-step BigQuery setup guide to connect your BigQuery dataset with Fivetran.
Once Fivetran is connected to your BigQuery dataset, we pull a full dump of all selected data from your dataset. We then use Fivetran Teleport Sync to pull all your new and changed data at regular intervals. If data in your source dataset changes (for example, you add new tables/views or change a data type), Fivetran automatically detects and persists these changes into your destination.
Fivetran tries to replicate the exact schema, tables and views from your BigQuery source dataset to your destination.
We name the source schema with the dataset name you provided in the connector setup form. We replicate the source's tables and views to the destination schema. We name the destination schema with the connector name appended by the dataset name. For example, if the connector name is
bigquery_db and the dataset name is
fivetran_schema, the destination schema name is
Type transformations and mappinglink
As we extract your data, we match BigQuery data types to types that Fivetran supports. If we don't support a data type, we automatically change that type to the closest supported type or, in some cases, don't load that data at all. Our system automatically skips columns with data types that we don't accept or transform.
The following table illustrates how we transform your BigQuery data types into Fivetran-supported types:
Fivetran performs incremental updates of any new or modified data from your source dataset. We use Fivetran Teleport Sync, our proprietary dataset replication method, to fetch only the data that has changed since our last sync. Learn more in our Fivetran Teleport Sync documentation.
Fivetran Teleport Sync's queries perform the following operations on your dataset:
- Do a full table scan of each synced table
- Perform calculations on all values in each synced table's rows
- Aggregate a compressed table snapshot in the dataset's memory
NOTE: Fivetran Teleport Sync captures deletes differently than log-based replication mechanisms. Learn more in our Changes to data between syncs documentation.
BigQuery does not have primary keys, so we designate the Fivetran-generated
_fivetran_id column as the primary key.
We merge the following changes into the corresponding tables in your destination:
- An INSERT in the source table generates a new row in the destination with
- A DELETE in the source table updates the corresponding row in the destination with
- An UPDATE in the source table updates the existing row in the destination with
TRUEand generates a new row in the destination with
Supported data source typeslink
The BigQuery connector supports the following data source types:
- regular tables
- regular views
- materialized views
- partitioned tables (that do not require a partition filter)
The BigQuery connector does not support the following data source types:
- tables exported from Google Analytics
- partitioned tables (that require a partition filter)
- temporary and transient tables
- external tables