Google BigQuery

BigQuery is a sink only in Data Sync — there is no BigQuery source. What makes it worth documenting in depth is that it is a true CDC sink rather than an append target: updates overwrite the row they belong to and deletes remove it, provided the target table declares a primary key.

Sink Storage Write API Upsert + delete sequence_dedup

Overview

Data Sync writes to BigQuery through the Storage Write API on the default stream. Rows are committed as they are appended, so there is no explicit stream finalisation step and no commit transaction to coordinate — a flush that returns successfully is durable, which is what lets the sync checkpoint advance.

The distinction that matters when you are designing around this connector: Data Sync does not stream a change log into BigQuery and leave you to collapse it downstream. It sends each change annotated with a change type and a change sequence number, and BigQuery applies that change to the target table. An UPDATE at the source becomes an updated row at the sink, not a second row you have to deduplicate with a window function.

That behaviour is conditional, and the condition is on the table, not on Data Sync. The next section is the part to read carefully.

How writes are applied

Every row Data Sync sends carries two CDC pseudocolumns that the Storage Write API interprets rather than stores:

Pseudocolumn	Value	What it does
`_change_type`	`UPSERT` for inserts and updates, `DELETE` for deletes	Tells BigQuery whether to write the row against its key or remove the keyed row.
`_change_sequence_number`	The source log position, as a zero-padded 16-character hex string	Orders concurrent mutations for the same key. BigQuery applies the change with the highest sequence number and discards anything older, so an out-of-order or replayed change cannot resurrect a stale row image.

The hex encoding is zero-padded on purpose: the sequence number is compared as a string, so a fixed width is what makes lexical order match numeric order across the whole log position range.

Applying changes requires a primary key on the target table. BigQuery only honours _change_type and _change_sequence_number when the table declares PRIMARY KEY (…) NOT ENFORCED. Data Sync adds that constraint for you when it creates the table and you have set primary keys for the mapping. If you point a sync at a table that already exists and that table has no NOT ENFORCED primary key, Data Sync cannot add one after the fact — updates and deletes will not be applied as you expect. Check the target's DDL before reusing an existing table.

Metadata columns

Alongside the pseudocolumns, which are interpreted and thrown away, every table Data Sync writes gets two stored metadata columns:

Column	Type	What it holds
`_pulse_lsn`	`INTEGER`	The source log position the row was read at.
`_pulse_op`	`STRING`	The operation that produced the row.

These two come first. Data columns follow, sorted by name, and every data column is nullable — Data Sync does not propagate source-side NOT NULL into the BigQuery schema, because a column that is non-nullable today may need to accept nulls after a schema change tomorrow.

Sorting by name means the column order in BigQuery will not match the column order at the source. Query by name, not by position.

Replay safety

Data Sync also filters on the client side: before a batch is sent, events at or below the last flushed log position are dropped. A runner that restarts and replays a window of changes therefore does not re-append anything BigQuery has already durably accepted.

Combined with _change_sequence_number, that gives the BigQuery sink the sequence_dedup idempotency class. The important property of sequence_dedup is that it is durable — deduplication is decided by comparing monotonic sequence numbers stored with the data, not by a time-bounded buffer. It does not matter whether the outage lasted thirty seconds or three days; a replay from the last durable checkpoint converges on the same table contents either way.

The per-sink matrix, and what the other idempotency classes cost you, is in Delivery guarantees.

Connection fields

Set once, on the Connection, in step 2 of the add-a-connection wizard. The Advanced settings hold the table-shaping options.

Field		Default	Description
`project_id`	Required	—	The Google Cloud project that owns the dataset.
`dataset_id`	Required	—	The dataset every table for this connection is written into.
`credentials_file`	Advanced	—	A service-account key file for the runner to use. Most connections attach the key in step 3 instead, as Credentials JSON. Leave both empty to use workload identity or application default credentials — the preferred option when the runner already has an identity in the same project.
`partition_field`	Advanced	Ingestion-time partitioning	A column to partition the created table on. See Partitioning.
`partition_type`	Advanced	`DAY`	Partition granularity: `DAY`, `HOUR`, `MONTH` or `YEAR`.
`max_staleness`	Advanced	Apply immediately	How stale a query result may be before BigQuery applies pending changes. Raising it trades read freshness for cheaper background apply work.

Project and dataset are the only two fields you must fill in. The defaults under Advanced settings are what Data Sync is tested against.

Sync fields

Set per sync, in the create-a-sync wizard. These are the fields that decide whether BigQuery upserts or appends.

Field	Where	Description
`primary_keys`	Sync	Comma-separated column names. This is what enables upsert and delete semantics. With primary keys set, Data Sync declares `PRIMARY KEY (…) NOT ENFORCED` on any table it creates and BigQuery applies changes against those columns. Without them, there is no key to apply a change to and rows accumulate.
`table_id`	Sync	The target table in the dataset.
`target`	Per table	The table name at the sink for one replicated source table. Set in step 4 of the wizard; defaults to the source table's bare name.
Key columns	Per table	Per-table override of the primary key. Overrides refer to output column names, so apply them after any rename.
Excluded columns	Per table	Columns dropped before they leave the source, so they are never written and never appear in the created table.

No primary key means no CDC semantics. A table configured without keys still receives every change, but each one arrives as a new row. Updates do not overwrite and deletes do nothing at all. If you want an append-style history table that is a legitimate choice — just make it a deliberate one.

Table creation and evolution

Each table's create mode, set in the sync wizard, decides who owns the DDL.

Use existing — Data Sync never issues CREATE TABLE. Every target must already exist, with a compatible schema and, if you want changes applied rather than appended, its own NOT ENFORCED primary key.
Auto-create — a missing table is created with the configured partitioning and, when primary keys are set, the PRIMARY KEY … NOT ENFORCED constraint.

When the table already exists, Data Sync's schema reconciliation is append-only: it adds the columns that are missing and never modifies, retypes or removes one that is already there. That is what makes the operation idempotent and safe to run before every write — reconciling a table that is already correct is a no-op, and reconciling one that has drifted only ever widens it. A column you dropped by hand in BigQuery will come back; a column you added by hand will not be touched.

When max_staleness is configured, Data Sync applies it as a separate statement against the table:

ALTER TABLE `project.dataset.table` SET OPTIONS (max_staleness = INTERVAL 300 SECOND)

Column names are sanitised to match BigQuery's identifier rules — [A-Za-z_][A-Za-z0-9_]*. A name that starts with a digit gets a leading _. If your source has columns that need sanitising, rename them explicitly in the mapping step so the name at the sink is one you chose rather than one that was derived.

Partitioning

Partitioning is set on the Connection and applies to the tables Data Sync creates. It has no effect on a table that already exists — BigQuery cannot repartition in place, and Data Sync will not try.

Set partition_field to a column and the created table is partitioned on that column's value, at the granularity given by partition_type (DAY by default, or HOUR, MONTH, YEAR). Leave partition_field empty and you get ingestion-time partitioning instead, which partitions on when Data Sync wrote the row rather than on anything in the data.

For a CDC target, ingestion time is often the less useful of the two: a row that is upserted repeatedly moves between ingestion partitions, so partition pruning on ingestion time does not help queries that filter on a business date. If your table has a natural event or created-at column, partition on that.

Batching and retries

The BigQuery sink advises the batcher to flush at roughly 500 rows or 5 MiB per request, whichever comes first. That sits comfortably under the Storage Write API's 10 MB per-request limit, leaving headroom for rows that are larger than the average without a batch having to be split at send time.

Those per-sink hints override the engine's default byte ceiling. The rest of the batching behaviour is the platform default described in Core concepts, including the immediate flush when the source goes quiet.

Transient errors — the retryable class the Storage Write API returns under load — are retried with exponential backoff and jitter. The jitter matters when many syncs write into the same project: without it, every runner that hit the same throttle would retry in lockstep and reproduce the burst that caused it.

There is no GCS staging step. Some warehouse loaders write files to object storage and then trigger a load job. Data Sync does not: rows go straight through the Storage Write API. There is no bucket to provision, no staging lifecycle policy to manage, and no window in which your data sits in a second system.

Type mapping

Arrow is the intermediate representation, so this table reads Arrow → BigQuery. The source-to-Arrow half is on each source connector's page.

Arrow type	BigQuery type
Boolean	`BOOLEAN`
All integer widths	`INTEGER`
Float32, Float64	`FLOAT`
Decimal128	`NUMERIC`
Binary	`BYTES`
Timestamp	`TIMESTAMP`
Date	`DATE`
Everything else	`STRING`

The STRING fallback is deliberate: a type Data Sync cannot map precisely is carried as text rather than dropped, so no column disappears silently on the way to the warehouse.