[destination-bigquery] Missing tables in the destination during the final steps of sync

[killthekitten]

Connector Name

destination-bigquery

Connector Version

2.6.3

What step the error happened?

During the sync

Relevant information

We have been seeing empty/missing destination tables more often, and the frequency of such incidents increases. During the debug, we narrowed the issue down to these BigQuery jobs:

DROP TABLE IF EXISTS `project-name`.`dataset`.`table_name`;
ALTER TABLE `project-name`.`dataset`.`table_name_airbyte_tmp` RENAME TO `table_name`;

The DROP statement executes instantly, but the ALTER TABLE is taking 10+ minutes to complete and there is no table to query during this time. I am not sure why the ALTER TABLE takes so much time, as this can happen even with small tables, but my guess is that BigQuery's Fair Scheduling policy deprioritizes our queries because of too many interactive jobs.

1. Is there a reason that DROP-ALTER duo is not atomic? Can it be wrapped into a transaction?
2. How do long waiting times on this operation affect the job and the instance status? We are seeing intermittent failures on the sync jobs more and more. Could it be that the jobs run into timeouts and get cancelled?
3. Because there are a lot of failures, we also see inconsistent data state (see my comment here) quite often that is only resolved after two sync attempts.

Relevant log output

No response

Contribute

• Yes, I want to contribute