### Apache Airflow version

Other Airflow 2 version (please specify below)

### What happened

We experienced this issue Airflow 2.6.1, but the problem exists in the Google provider rather than core Airflow, and were introduced with [these changes](https://github.com/apache/airflow/pull/30053/files).  We are using version 10.0.0 of the provider.

The [issue](https://github.com/apache/airflow/issues/29958) that resulted in these changes seems to be based on an incorrect understanding of how projects interact in BigQuery -- namely that the project used for storage and the project used for compute can be separate.  The user reporting the issue appears to mistake an error about compute (`User does not have bigquery.jobs.create permission in project {project-A}.` for an error about storage, and this incorrect diagnosis resulted in a fix that inappropriately defaults the compute project to the project named in destination/source (depending on the operator) table.

The change attempts to allow users to override this (imo incorrect) default, but unfortunately this does not currently work because `self.project_id` gets overridden with the named table's project [here](https://github.com/apache/airflow/pull/30053/files#diff-875bf3d1bfbba7067dc754732c0e416b8ebe7a5b722bc9ac428b98934f04a16fR512) and [here](https://github.com/apache/airflow/pull/30053/files#diff-875bf3d1bfbba7067dc754732c0e416b8ebe7a5b722bc9ac428b98934f04a16fR587).



### What you think should happen instead

I think that the easiest fix would be to revert the change, and return to defaulting the compute project to the one specified in the default google cloud connection.  However, since I can understand the desire to override the `project_id`, I think handling it correctly, and clearly distinguishing between the concepts of storage and compute w/r/t projects would also work.

### How to reproduce

Attempt to use any other project for running the job, besides the one named in the source/destination table

### Operating System

debian

### Versions of Apache Airflow Providers

apache-airflow-providers-google==10.0.0

### Deployment

Other 3rd-party Helm chart

### Deployment details

_No response_

### Anything else

_No response_

### Are you willing to submit PR?

- [X] Yes I am willing to submit a PR!

### Code of Conduct

- [X] I agree to follow this project's [Code of Conduct](https://github.com/apache/airflow/blob/main/CODE_OF_CONDUCT.md)

"ignoreUnknownValues": False,
                        "schema": {"fields": SCHEMA_FIELDS},
                        "skipLeadingRows": None,
                        "fieldDelimiter": ",",
                        "quote": None,
                        "allowQuotedNewlines": False,
                        "encoding": "UTF-8",
                    }
                },
                job_id=pytest.real_job_id,
                location=None,
                nowait=True,
                project_id=hook.return_value.split_tablename.return_value[0],
                retry=DEFAULT_RETRY,
                timeout=None,
            ),
            call(
                configuration={
                    "query": {
                        "query": f"SELECT MAX({MAX_ID_KEY}) AS max_value FROM {TEST_EXPLICIT_DEST}",
                        "useLegacySql": False,
                        "schemaUpdateOptions": [],
                    }
                },
                project_id=hook.return_value.project_id,
            ),
        ]
        hook.return_value.insert_job.assert_has_calls(calls)
    @mock.patch(GCS_TO_BQ_PATH.format("BigQueryHook"))
    def test_labels_external_table_should_execute_successfully(self, hook):