BigQuery source for ELT & CDC

​

Overview

​

Required Roles

​

Required Configuration

​

Enabling Change History

ALTER TABLE \`project.dataset.your_table\`
SET OPTIONS (enable_change_history = TRUE);

DECLARE stmt STRING;

FOR tbl IN (
  SELECT table_name
  FROM \`project.dataset.INFORMATION_SCHEMA.TABLES\`
  WHERE table_type = 'BASE TABLE'
)
DO
  SET stmt = FORMAT("""
    ALTER TABLE \`project.dataset.%s\`
    SET OPTIONS (enable_change_history = TRUE)
  """, tbl.table_name);

  EXECUTE IMMEDIATE stmt;
END FOR;

​

Continuous Sync Modes

​

Limitations

• Pipeline downtime: If your pipeline is stopped for more than 7 days, you may need to perform a full resync. BigQuery only retains change data for up to 7 days by default.
• Change history required for updates/deletes: To capture updates and deletes, you must enable change history on your tables. Without it, only new rows (inserts) will be replicated.
• Nested data: Complex nested fields (STRUCT, ARRAY) are converted to JSON strings in the destination.
• Pipeline restart required: If you enable change history on a table that’s already syncing, restart the pipeline for the changes to take effect.

​

Tables Without Primary Keys

• _iio_id is a deterministic SHA-256 hash computed from all column values in each row
• No user configuration is required; surrogate key generation is enabled by default