[ADAP-1062] [Bug] Retries on wait for result step is recreating the whole job

[github-christophe-oudar]

Is this a new bug in dbt-bigquery?

• I believe this is a new bug in dbt-bigquery
• I have searched the existing issues, and I could not find an existing issue for this bug

Current Behavior

Retries on wait for result step is recreating the whole BigQuery job which means it's duplicating the run.

It can lead to few problems:

• if for some reason, the model is not 100% idempotent, it will lead to unexpected results like duplicated data
• The incurred cost can be doubled
• For long model, it can double the time to process and therefore add unexpected downstream latency/delay

It took us few hours to 2-3 engineers actually pinpoint the origin of the problem as we didn't expect that kind of behavior.
To workaround the issue, we use retries: 0 on the production connection be sure we always rerunning the whole models fully to be safe.

Expected Behavior

If a job has been started successfully, we should retry to connect to the actual job status and not create a new one.

Steps To Reproduce

This bug is hard to reproduce as it depends on potential Google's being in error or the network to fail on the request.
It might be mimicked by changing the code the code return an error instead of waiting for the result.

Relevant log output

2023-12-02T04:43:31.713+01:00	[0m03:43:31 BigQuery adapter: https://console.cloud.google.com/bigquery?project=prodcution&j=bq:US:11111111-2222-3333-4444-aaaaaaaaaaaa&page=queryresults

... 1,25 hour latter

2023-12-02T05:55:12.921+01:00	[0m04:55:12 BigQuery adapter: Retry attempt 1 of 3 after error: ConnectionError(ProtocolError('Connection aborted.', RemoteDisconnected('Remote end closed connection without response')))

2023-12-02T05:55:12.922+01:00	[0m04:55:12 BigQuery adapter: Reopening connection after ConnectionError(ProtocolError('Connection aborted.', RemoteDisconnected('Remote end closed connection without response')))

2023-12-02T05:55:14.347+01:00	[0m04:55:14 BigQuery adapter: https://console.cloud.google.com/bigquery?project=teads-prod-analytics-etl&j=bq:US:11111111-2222-3333-4444-bbbbbbbbbbbb&page=queryresults

Environment

- OS: Ubuntu 20.04
- Python: 3.10
- dbt-core: 1.7.0
- dbt-bigquery: 1.7.0

Additional Context

No response

[McKnight-42]

Closing as this ties with #1042 and #977 which has a continuing conversation going on.

[github-christophe-oudar]

Note that #977 approach could work too but only if you're rerunning the dbt command a second time.
I'm also wondering if it works or if it would fail saying there's an existing job with the provided id (since the tests are using mocks).