Can't create materialized view based on a query to dataset replica

[cheyuriy]

Hello,

Introduction.

We've got our main GCP project (let's call it A) with BigQuery processing in EU multi-region. And we're using on-demand pricing model with it. I decided to move some "volume intensive" transformations to another project (let's call it B) with capacity compute pricing model. I've created a reservation for "europe-west4" region so all computations are done in it.
Everything was fine until the moment when I tried to move final tables back from project B to project A. All ideas with copying failed due to the quotas on partition changes (yes, tables are partitioned and yes, we need to copy them whole). So I started to look for other options. Data transfer is not the option, because we need to update tables too frequently (hourly) and it will be too costly. Another idea was to create a materialized view in the project A so it will query a table in project B, but once again - different regions prevented me from doing this.
But there was a cheap solution to move table in project B between different regions using dataset cross-region replication. So I've created a read-only replica in EU region. 

Actual problem
And here comes actual problem. After that I tried to create materialized view in project A:

CREATE MATERIALIZED VIEW `project-a`.my_dataset.table_x_view
AS 
SELECT * FROM `project-b`.my_dataset_with_replica.table_x

Now both datasets are in EU region, so no complains from BigQuery until I run it. And receive an error:

The dataset replica of the cross region dataset 'project-b:my_dataset_with_replica' in region 'EU' is read-only because it's not the primary replica.

But why? Is it necessary to have a write access to table to create materialized view? It works fine with a standard VIEW. Is there any workarounds?

[ms4446]

The error message indicates that the replica is read-only because it's not the primary replica.

Potential Solutions:

1. Explore Regional Materialized Views (if available): BigQuery's regional materialized views, currently in beta, allow replication of materialized views across regions. It's important to verify the current availability and limitations of this feature to see if it fits your specific scenario.

2. Consider Scheduled Queries and Regular Views: Implement scheduled queries to periodically copy data from Project B to a regular view in Project A. While this method provides updated data, be aware that it may impact query performance and costs due to the lack of pre-computed results.

3. Revisit Data Transfer Service: Re-examine the Data Transfer Service, focusing on strategies for incremental updates and partitioning to manage costs and data volume. This approach is particularly relevant if your data changes are manageable and not too extensive. Keep an eye on the cost implications of these strategies.

Additional Considerations:

• Quota Management: Explore possibilities for increasing quota limits or optimizing data operations to stay within existing limits. Efficient quota management can often alleviate operational challenges.

• Architectural Review: Conduct a comprehensive assessment of your data architecture. This can uncover alternative approaches that align more closely with BigQuery's capabilities and limitations, potentially leading to more efficient and cost-effective solutions.