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

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:

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.