[source-bigquery] Support cross-project access, custom queries, and partition filters

[Ilja Herdt (Airbyte) (iherdt-airbyte)]

The BigQuery source connector has several limitations that prevent it from supporting common enterprise BigQuery access patterns. These are related and would ideally be addressed together in a connector revamp.

Request: Improve the BigQuery source connector to support:

1. Cross-project credential access (service account from Project A accessing datasets in Project B)
2. Custom query support (similar to source-google-ads custom_queries_array, allowing users to define their own SQL with SELECT, WHERE, and destination table name)
3. Partition filter / WHERE clause support on all syncs, including the initial sync

Context: Enterprise environments commonly use cross-project BigQuery access where a vendor issues credentials from a central project, and data lives in a separate project. These environments also frequently enforce mandatory partition filters or WHERE clauses as security/cost controls. Without custom query support, users cannot control what data is read or how tables are queried, which blocks adoption in environments with strict data governance requirements.

A working custom connector using Google's Python BigQuery client demonstrates all three capabilities are technically feasible and align with Google's officially supported patterns.

Expected behavior:

• Service account from one GCP project can access datasets in another project via standard cross-project IAM
• Users can optionally define custom SQL queries (SELECT with WHERE, JOIN, etc.) that map to named destination tables
• Partition filters / WHERE clauses are honored on all syncs including initial full sync
• The connector follows Google's recommended pattern: separate credential validation from project targeting using bigquery.Client(project=<target>, credentials=<creds>)

Related issues:

• https://github.com/airbytehq/oncall/issues/10189 (cross-project credentials, P1, has dev image fix)
• Source: Bigquery - Response too large #6533 (BigQuery "Response too large", related to unbounded queries)