[v2 Bug] BigQuery Job IDs for Fusion being reused

[achang52]

Is this a new bug in dbt v2.x compared to the latest version of dbt 1.x?

• I believe this is a new bug in dbt v2.x
• I have searched the existing issues and could not find a duplicate

Current Behavior

We are seeing 409 errors when reaching out to BigQuery in our dbt ops:
-- error message: Unknown: [BigQuery] googleapi: Error 409: Already Exists: Job discord-data-vault-prd:US.FHgd4oEZfOAzBB4dmmoyJo6aVon, duplicate

We have validated that these jobIDs have indeed been used by other dbt ops reaching out, but they were on different days and with different assets. I'm wondering if this has to do with the jobID being generated differently compared to core and fusion as fusion uses the arrow ADBC to connect to BQ, as we don't see these jobID collisions when we revert our assets to running on core again. I noticed that this was an issue seen in core for retries of the same job - dbt-labs/dbt-adapters#1756 - but this isn't our particular case either.

Expected Behavior

We don't expect to see jobID collisions for BigQuery job submissions.

Steps To Reproduce

Not sure on how to forcibly reproduce, we see these when we have a large number of assets migrated to fusion.

Relevant log output

`-- error message: Unknown: [BigQuery] googleapi: Error 409: Already Exists: Job discord-data-vault-prd:US.FHgd4oEZfOAzBB4dmmoyJo6aVon, duplicate`

Environment

- OS:
- CPU: (x86 or ARM)
- dbt distribution and version: (`dbt --version`)

Which database adapter are you using?

other (describe in Additional Context)

Is this a discrepancy vs. dbt 1.x?

• Yes — this works in dbt 1.x but not in dbt v2.x

Additional Context

No response

[achang52]

@xuliangs @serramatutu Hi! I've reached out to google and they've actually updated their library to use a better random number generator, I believe all that would be needed from the dbt side would be to bump the google-cloud-go version in this file (https://github.com/dbt-labs/arrow-adbc/blob/main/go/adbc/go.mod) though feel free to correct me if I'm wrong! Here is the bump that they made on their end - googleapis/google-cloud-go#20111 - Thanks so much for your help!

[serramatutu]

@achang52 We're actually going to move to a newer BigQuery driver soon, which uses the updated Google Cloud SDK. We're in the process of rebasing some dbt tweaks to it, and it'll soon be available under a feature flag/env var for testing. We're just testing it carefully to make sure there won't be any surprises.

The driver will be based on: https://github.com/adbc-drivers/bigquery
The dbt fork: dbt-labs/bigquery-adbc#1

For more context: ADBC drivers used to live in the apache/arrow-adbc monorepo, but the community has decided that that repo will only contain the standard spec and vendor-agnostic components such as the driver manager, adbc.h and some other stuff. All vendor-specific drivers now live under the ADBC Foundry, which is not overseen by Apache. dbt was still using the old BigQuery driver, but it was starting to drift. By moving to the new one we expect to solve a lot driver/GCP SDK issues. The other advantage will be that any upstream community changes to the BigQuery ADBC driver can be easily backported into dbt.

[achang52]

@serramatutu A quick question here - my understanding is that the adbc reaches out to the bigquery api regardless? Is the change going to be so that it doesn't hit that endpoint any longer?

[serramatutu]

Yes, it will keep reaching out to the same Storage API endpoint. It's just a different (newer, more feature rich) piece of code that does it.

[achang52]

Gotcha, would there also be a version bump then to the endpoint ver? (e.g. v1.72.0 -> ≥v1.78.0)?

[serramatutu]

Yes, that driver is using v1.78.0 of the BigQuery SDK :)

[jeremyyeo]

Additional info: https://dbtlabs.atlassian.net/browse/FUSCSE-39

Most likely tl;dr:

1. We submit a job to BQ with a certain job id abcd.
2. There's a transient issue (network, etc). (hard to repro on demand).
3. To retry, we resubmit the job with the same id abcd.
4. Error 409.