Dataform roles to access BigQuery in another project

[sysph]

Hi there,
Hope someone would be able to help with Dataform roles to access BigQuery in another project.

I have GA4 raw data stores in BigQuery in "raw data project".  Now, i would like to execute transformation SQLs pipeline in Dataform in "pipeline project" to query raw data in "pipeline project".  I've assigned  BigQuery Data Editor, BigQuery Job User, and BigQuery User to the dataform service account of "pipeline project" in "raw data project".

So far, Dataform execution and transformation models generating in BigQuery works well. However, we want that service account only query & save but NOT edit and delete. Which roles / permissions I should assign to that dataform service account of "pipeline project" in "raw data project"?

Also, If i use Workflows (with Pub/Sub Topic trigger in "pipeline project"; Log sink collects GA4 raw data ready logs from "raw data project") to execute Dataform repo actions. Workflows(in "pipeline project") didn't execute at all when the raw data ready in "raw data project". What roles/ permissions should i assign to?

Thanks

[VishalBulbule · Accepted solution]

You can give  the Dataform service account in the "pipeline project"  following roles:

• BigQuery Data Viewer
• BigQuery Job User

and related to 2nd question , you can give pubsub publisher and Workflow invoker roles.

[sysph]

Hi @VishalBulbule , 

Thank you for replying.  It's very helpful.  

Regards to the 2nd questions, I've assigned pubsub publisher and workflows invoker roles to the service account i created and added in "raw data project".  The log sink and destination to a Pub/Sub topic in "pipeline project"  won't directly allow me to view the logs from "raw data project" in the Log Explorer of "pipeline project". 

The same log query works in "raw data project" but not in "pipeline project".   Here's the log query i'm using in "pipeline project" trying to query logs from "raw-data-project" but not showing anything (I've given corrector date time range)

resource.type="bigquery_project"
resource.labels.dataset_id="analytics_xxxxx"
resource.labels.project_id="raw-data-project"
protoPayload.metadata.tableCreation.reason="JOB"
protoPayload.authenticationInfo.principalEmail="test@system.gserviceaccount.com"
protoPayload.methodName="google.cloud.bigquery.v2.JobService.InsertJob"
protoPayload.resourceName:"tables/events_"
NOT
protoPayload.resourceName:"tables/events_intraday"

[sysph]

Regards to the 2nd question, i might have to conduct another approach by setting GCP "raw data project" log sink destination to logging bucket in "pipeline project".  However,  how do i trigger Workflows? is it  by pub/sub topic or logging bucket event?  thanks

[sysph]

It looks like you could set the "raw data project" log sink destination to cross-project pub/sub topic of "pipeline project". I guess i'll assign pubsub publisher role to service account of "raw data project" in "pipeline project"