How to read data from table from another project from different region?

Question

We have a FACT1 table from project1 located in US-Region & FACT2 table from project2 located in Asia Region.

We want to do a union/join between 2 tables and persist into table in Project2. But GCP is not allowing to querying tables in 2 different regions.

1. What is the ideal approach recommended for the same?
2. If we wanted to read data from FACT1 (US) and replicate as a persistent table in project2 (FACT1_COPY) how can we setup a sync service in GCP?
3. How will the sync process work - whether only new records will be synced or it will always transfer entire table as truncate load? Any cost implications? Note: We don't know the implementation details of other project and just want to read data as is. FACT table has around 30 GB of data.

Kindly advise on what should be correct architecture.

Thanks, Kiran

Answer 1

You can use BigQUery data tranfer and select a dataset copy.

https://cloud.google.com/bigquery-transfer/docs/locations

You can choose to overwrite your data or not, and to set a custom schedule to run it at the frequency that you want.

The feature is free, but you will pay inter-region traffic from asia to us to transfer your data. And because you replicate the data, you will also pay the storage on the asia data in the US location.