BigQuery Authorised Views copy between two projects

[avindia]

I would like to copy BigQuery Views from one Project or Account to another Project and then automate the copy so changes in Source BQ views  can only be copied to Target BQ Views on regular basis. 

Both Projects are in Same GCP region and Zone

Need help to understand type of data transfer mechanism used by BQ ( Copy,Clone,Snapsjot)

2. Authentications mechanism used between source and target 

3. Cost of data transfer if any 

[ms4446 · Accepted solution]

BigQuery views are logical representations of SQL queries that do not store data themselves but reference underlying tables. When copying views between projects, typical data transfer methods like Copy, Clone, or Snapshot are not applicable since views only store SQL definitions. Instead, synchronizing views involves recreating the view in the target project using the same SQL definition and automating this process to keep target views updated.

To automate the synchronization of views, use scripts and scheduling tools like Cloud Scheduler. The process involves retrieving view definitions from the source project using the bq command-line tool or BigQuery API, and applying these definitions to update or create corresponding views in the target project. This can be automated with a Cloud Function or Cloud Run service triggered at regular intervals.

To facilitate secure access between projects, use a service account with appropriate permissions:

• Source Project: Assign the bigquery.metadataViewer role to read view definitions.
• Target Project: Assign bigquery.dataEditor or bigquery.user to create or update views.

The service account credentials should be securely managed using Cloud Functions or Cloud Run settings, adhering to the principle of least privilege.

Copying view definitions incurs no storage costs. However, querying views in the target project that reference source project tables will incur query processing costs based on data processed. As long as both projects are in the same region, there are no data egress charges. Additional costs may arise from monitoring and logging if implemented.

Best Practices and Alternatives

• Ensure cross-project access permissions for users and service accounts querying the views.
• Consider using authorized views to control access without exposing underlying data.
• If using materialized views, manage storage and refresh policies to control costs.

For alternative approaches, consider sharing datasets between projects or using Google Cloud Data Catalog to manage and discover data assets centrally.

[avindia]

I really appreciate your detailed  response on this 

[NorieRam]

Hi @avindia,

In addition to what @ms4446 has mentioned, you may want to explore the following references for future purposes:

• Cloud Scheduler documentation
• Cloud Run documentation
• BigQuery IAM Roles and Permissions

I hope the above information is helpful.

[avindia]

Thanks this is useful !