BigQuery Betalink
Updated 11 days ago
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.
NOTE: Fivetran supports BigQuery as both a database connector and a destination.
TIP: If you want to sync multiple BigQuery datasets, we recommend that you create a connector for each dataset.
Featureslink
Feature Name | Supported | Notes |
---|---|---|
Capture deletes | check | All tables and fields |
Custom data | check | All tables, views and fields |
Data blocking | ||
Column hashing | check | |
Re-sync | check | Connector table & view level |
History | check | |
API configurable | check | API configuration |
Priority-first sync | ||
Fivetran data models | ||
Private networking | check | Private Google Access |
Setup guidelink
Follow our step-by-step BigQuery setup guide to connect your BigQuery dataset with Fivetran.
Sync overviewlink
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.
Schema informationlink
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 bigquery_db_fivetran_schema
.
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:
BigQuery Type | Fivetran Type | Supported |
---|---|---|
ARRAY | STRING | Yes |
BIGNUMERIC | BIGDECIMAL | Yes |
BOOL | BOOLEAN | Yes |
BYTES | BINARY | Yes |
DATE | LOCALDATE | Yes |
DATETIME | LOCALDATE | Yes |
FLOAT64 | BIGDECIMAL | Yes |
GEOGRAPHY | JSON | Yes |
INT64 | BIGDECIMAL | Yes |
INTERVAL | STRING | Yes |
JSON | JSON | Yes |
NUMERIC | BIGDECIMAL | Yes |
STRING | STRING | Yes |
STRUCT | JSON | Yes |
TIME | STRING | Yes |
TIMESTAMP | INSTANT | Yes |
Updating datalink
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.
Table updateslink
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
_fivetran_deleted
=FALSE
. - A DELETE in the source table updates the corresponding row in the destination with
_fivetran_deleted
=TRUE
. - An UPDATE in the source table updates the existing row in the destination with
_fivetran_deleted
=TRUE
and generates a new row in the destination with_fivetran_deleted
=FALSE
.
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
TIP: To sync Google Analytics export tables, use our Google Analytics 4 or Google Analytics 360 connector.