### 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)

of the last account in the list, which will be impersonated in the request.
        If set as a string, the account must grant the originating account
        the Service Account Token Creator IAM role.
        If set as a sequence, the identities from the list must grant
        Service Account Token Creator IAM role to the directly preceding identity, with first
        account from the list granting this role to the originating account (templated).
    :param result_retry: How to retry the `result` call that retrieves rows
    :param result_timeout: The number of seconds to wait for `result` method before using `result_retry`
    :param job_id: The ID of the job. It will be suffixed with hash of job configuration
        unless ``force_rerun`` is True.
        The ID must contain only letters (a-z, A-Z), numbers (0-9), underscores (_), or
        dashes (-). The maximum length is 1,024 characters. If not provided then uuid will
        be generated.
    :param force_rerun: If True then operator will use hash of uuid as job id suffix
    :param reattach_states: Set of BigQuery job's states in case of which we should reattach
        to the job. Should be other than final states.
    :param deferrable: Run operator in the deferrable mode
    """
    template_fields: Sequence[str] = (
        "source_project_dataset_table",
        "destination_cloud_storage_uris",
        "export_format",
        "labels",
        "impersonation_chain",
        "job_id",
    )
    template_ext: Sequence[str] = ()
    ui_color = "#e4e6f0"
    operator_extra_links = (BigQueryTableLink(),)
    def __init__(