BigQuery - Create or Replace Table possible race condition when quota (concurrently) exceeded [bug?]

[dan-man]

If you do a `create or replace table ...` when the quota has already been exceeded then as expected the table doesn't change (this i have double checked), however...

If you are running multiple queries concurrently (e.g. via dbt), and one of the queries exhausts the quota it seems that it's possible that any concurrent `create or replace table ...` queries can fail,  but rather than simply abort safely, they can end up deleting the old version of the table that they were set to replace.

I have not attempted to reproduce this race condition, but right now that seems the most likely issue with what we have observed.

Has anyone else seen this? We've only noticed it as of today (2024-Nov-12), so wonder if it is a new bug.

Thanks

[NorieRam]

Hi @dan-man,

Welcome to Google Cloud Community!

Thanks for sharing this issue. It does sound like a potential race condition when running concurrent create or replace table queries in BigQuery, especially when hitting quota limits. This could indeed lead to unexpected behavior, such as the deletion of the old table version instead of a safe abort.

To address this, you might want to consider implementing some form of error handling and retry logic in your queries. For example, you could catch quota-exceeded errors and implement exponential backoff before retrying the operation. Additionally, monitoring your BigQuery quotas and usage patterns might help identify and mitigate these issues before they cause problems.

It's also a good idea to reach out to Google Cloud Support or check if there have been any recent updates or known issues related to this behavior. They might be able to provide more specific guidance or confirm if this is a new bug.

I hope this helps.

[dan-man]

Thanks for the reply @NorieRam.

While exponential backoff and retry logic is a good idea in many contexts, I don't see how it's relevant here.

In terms of avoiding hitting quota - given how easy it is to rack up a large bill for BigQuery, we actually prefer to set the quota fairly low so that we have to manually intervene if we do hit the quota.