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

The default value is NONE.
        This setting is ignored for Google Cloud Bigtable,
        Google Cloud Datastore backups and Avro formats.
    :param create_disposition: The create disposition if the table doesn't exist.
    :param skip_leading_rows: The number of rows at the top of a CSV file that BigQuery
        will skip when loading the data.
        When autodetect is on, the behavior is the following:
        skip_leading_rows unspecified - Autodetect tries to detect headers in the first row.
        If they are not detected, the row is read as data. Otherwise, data is read starting
        from the second row.
        skip_leading_rows is 0 - Instructs autodetect that there are no headers and data
        should be read starting from the first row.
        skip_leading_rows = N > 0 - Autodetect skips N-1 rows and tries to detect headers
        in row N. If headers are not detected, row N is just skipped. Otherwise, row N is
        used to extract column names for the detected schema.
        Default value set to None so that autodetect option can detect schema fields.
    :param write_disposition: The write disposition if the table already exists.
    :param field_delimiter: The delimiter to use when loading from a CSV.
    :param max_bad_records: The maximum number of bad records that BigQuery can
        ignore when running the job.
    :param quote_character: The value that is used to quote data sections in a CSV file.
    :param ignore_unknown_values: [Optional] Indicates if BigQuery should allow
        extra values that are not represented in the table schema.
        If true, the extra values are ignored. If false, records with extra columns
        are treated as bad records, and if there are too many bad records, an
        invalid error is returned in the job result.
    :param allow_quoted_newlines: Whether to allow quoted newlines (true) or not (false).
    :param allow_jagged_rows: Accept rows that are missing trailing optional columns.
        The missing values are treated as nulls. If false, records with missing trailing
        columns are treated as bad records, and if there are too many bad records, an