Move MySQL rows into BigQuery on a schedule.

Before you start

• Working connections to both MySQL and BigQuery, added under Databases.
• Pipelines tier. Data Sync isn't part of Studio.
• A key column in the target table if you're doing an Update or Upsert.

Steps

1. Open Pipelines → Build and click New Sync.
2. On the left, pick the source Connection (MySQL) and Table, or write SQL.
3. On the right, pick the target Connection (BigQuery) and Target Table.
4. Drag from a source field to a target field, or click AI Map.
5. Pick a MODE: Insert, Update or Upsert.
6. For Update or Upsert, choose a MATCH ON field whose values are unique per row.
7. Click Dry Run to preview without writing.
8. Save it as a job, or run it now.

A worked example

Keeping a BigQuery orders table current from a MySQL source that gets updated in place as order status changes:

SELECT order_id, customer_id, status, updated_at
FROM shop.orders
WHERE updated_at >= NOW() - INTERVAL 1 DAY;

Target my-gcp-project.warehouse.orders, MODE Upsert, MATCH ON order_id. QueryFlow handles the MERGE into BigQuery on that key, so an order that changed status yesterday updates the existing row in BigQuery instead of adding a duplicate. Save it as a job running every hour to keep the warehouse table current without a full reload each time.

A type gotcha worth knowing about

MySQL commonly represents a boolean as TINYINT(1), a plain integer that happens to only ever hold 0 or 1, while BigQuery has a real BOOL type. If a field like is_active lands in BigQuery as an integer instead of true/false, check how the target column is typed and adjust the mapping, this is a MySQL-to-BigQuery quirk specifically, not a Data Sync bug.

Check it worked

Run Dry Run first and read the preview before anything writes. After a real run, check the job's history: a clean run shows rows synced with no errors; a partial one lists which rows failed, usually a type mismatch.

Troubleshooting

An alternative for a log-style table: Insert only

Not every MySQL table gets edited in place. An append-only events or audit-log table never changes an existing row, so Insert is the right MODE, not Upsert, there's no key to match on because nothing needs updating:

SELECT event_id, user_id, event_type, created_at
FROM shop.audit_log
WHERE created_at >= NOW() - INTERVAL 1 HOUR;

Run that hourly with MODE Insert into a matching BigQuery table. Since the source query already filters to the last hour, re-running it on schedule naturally appends only new rows, without needing a MATCH ON column at all.

Keeping schemas aligned on both sides

Data Sync maps the fields you tell it to map; it won't add a new MySQL column to the BigQuery target automatically. If the source table gains a column you want mirrored, add it to the BigQuery table first and update the mapping.

Where Upsert earns its keep here

MySQL-to-BigQuery syncs are a common case for Upsert specifically: operational rows change in place in MySQL (a status field, an updated timestamp), and Insert alone would just accumulate duplicates in BigQuery on every run. Match on the primary key you already trust in MySQL and let the MERGE handle the rest.

Picking a sync interval that matches the source

An hourly sync fits an operational table like orders, where a report needs status changes reflected within an hour. A slower-moving reference table, a product catalog that changes a few times a week, doesn't need hourly runs; a nightly sync is plenty and avoids running a MERGE against BigQuery for a table that never actually changed since the last check.

A note on running this alongside a BigQuery job destination

Data Sync and a scheduled job's BigQuery destination solve different problems and can run side by side on the same source: use Data Sync for a mapped table that needs Update or Upsert logic, and a job's BigQuery destination (Append or Replace) for a simpler case where you're just appending a query's output or replacing a table wholesale. Both use the same underlying MySQL and BigQuery connections, so there's no double setup.

Related syncs

See also: Integrations Load a CSV into BigQuery on Mac Sync Databricks to BigQuery.