When you execute workflow that uses federated query on dataform, fail:
reason:"invalidQuery" location:"query" message:"Access Denied: Connection 'your_conn': User does not have bigquery.connections.use permission for connection 'your_conn'.
What's confusing most is when you run *.sqlx indivisually from editor right side above, does NOT cause error but run correctly and can get data.
This happens following this official instruction.
https://cloud.google.com/dataform/docs/required-access#grant_required_roles_to_a_service_account_used_in
They said you need to give these roles for Dataform Service Account.
- BigQuery Job User
- BigQuery Data Editor
- BigQuery Data Viewer
However, if you want to access Cloud SQL using federated query, Another role needed.
- bigquery.connections.use
It is written here.
https://cloud.google.com/bigquery/docs/cloud-sql-federated-queries
あまり日本語英語ともに情報がなかったので、英語で書きました。いつか検索に引っかかって誰かのためになりますように。