Fix data inconsistency issue in case of retries in BigQuery replication plugin
Description
In case of BigQuery load/merge job failures, the retry handling in the plugin can lead to duplicate data in the target table.
The plugin runs following BQ jobs as part of replication pipeline
Load job to insert snapshot events directly to the target table
Load job to insert CDC events directly to the staging table
Query job to merge CDC events from staging to target table
In case of transient network errors just after the create job request reaches BQ, the create or check status calls for job can fail and a retry would be triggered after the retry interval (90 sec). Retry would run a new job which would lead to duplicate data in staging/target table. Duplicate data in staging table can intern lead to duplicate data in target table or pipeline failure due to error UPDATE/MERGE must match at most one source row for each target row
Release Notes
For replication jobs, fixed an issue where retries for transient errors from BigQuery might have resulted in data inconsistency.
Activity
Show:
Sumit Jain January 13, 2023 at 12:28 PM
Fixed
Pinned fields
Click on the next to a field label to start pinning.
In case of BigQuery load/merge job failures, the retry handling in the plugin can lead to duplicate data in the target table.
The plugin runs following BQ jobs as part of replication pipeline
Load job to insert snapshot events directly to the target table
Load job to insert CDC events directly to the staging table
Query job to merge CDC events from staging to target table
In case of transient network errors just after the create job request reaches BQ, the create or check status calls for job can fail and a retry would be triggered after the retry interval (90 sec). Retry would run a new job which would lead to duplicate data in staging/target table. Duplicate data in staging table can intern lead to duplicate data in target table or pipeline failure due to error
UPDATE/MERGE must match at most one source row for each target row