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

self,
        source_format: str,
        src_fmt_configs: dict,
        valid_configs: list[str],
        backward_compatibility_configs: dict | None = None,
    ) -> dict:
        """
        Validates the given src_fmt_configs against a valid configuration for the source format.
        Adds the backward compatibility config to the src_fmt_configs.
        :param source_format: File format to export.
        :param src_fmt_configs: Configure optional fields specific to the source format.
        :param valid_configs: Valid configuration specific to the source format
        :param backward_compatibility_configs: The top-level params for backward-compatibility
        """
        if backward_compatibility_configs is None:
            backward_compatibility_configs = {}
        for k, v in backward_compatibility_configs.items():
            if k not in src_fmt_configs and k in valid_configs:
                src_fmt_configs[k] = v
        for k, v in src_fmt_configs.items():
            if k not in valid_configs:
                raise ValueError(f"{k} is not a valid src_fmt_configs for type {source_format}.")
        return src_fmt_configs
    def _cleanse_time_partitioning(