Add billing project option to BigQuery source connector

[cw-david-robinson]

The BigQuery source connector (here) requires bigquery.jobs.create role in the project that data is being fetched from (see screenshot). We have some use cases where we need to fetch data from a third party - in such cases our service account has bigquery.dataViewer role in the source project, but it is not possible to assign bigquery.jobs.create role in the same project (the project is owned by a 3rd party after all and they don't want to incur the cost of us submitting jobs to run in their project).

To get around this, we would like to be able to optionally specify a billing project (seperate to the source project) so that we can control which project the BigQuery job gets created in (right now the job is always created in the same project as where the data is stored, and there doesn't seem to be any way around this). This is already supported by the BigQuery API jobs.query method where the projectId parameter allows you to control the billing project where the job is created in.

[justbeez]

Agree that this should be supported—this model is very common among agencies as well, where clients are only sharing read-only access of things, and in many cases it's impossible due to policy constraints to get a project-level permission added.

It's also helpful for billing isolation in a larger enterprise, as teams may have to pay for their own compute, and specifying the billing project allows you to share data without passing costs along to other teams/clients/data owners.

[cw-david-robinson]

For now I have a work around:

• Create a service account with bigquery.dataViewer role in the project with the source table, and bigquery.dataEditor + bigquery.jobUser role in the billing project
• Using the service account, create a view in the billing project that selects from the source table
• Configure the BigQuery source connector to select from the newly created view in the billing project