In your case, creating an incremental materialized view in BigQuery becomes tricky due to the limitations on how BigQuery handles UNION ALL queries and certain conditions in materialized views.
Unfortunately, materialized views in BigQuery have specific restrictions, particularly around complex queries such as those involving CTEs, UNION ALL, and aggregations. In your case, this prevents the materialized view from functioning as expected.
One option is to switch to a regular view. Regular views in BigQuery can handle complex queries, including CTEs and UNION ALL. While they do not provide the performance benefits of materialized views, they allow you to structure your query as needed. In this case, you can aggregate your data with a UNION ALL clause for the “B - target” product by creating a standard view with the required logic.
If performance is critical, a better approach might be to use a scheduled query. This involves running a query periodically (e.g., daily) to materialize the results into a new table. This avoids the restrictions on materialized views while still maintaining query performance. You can write your query to aggregate the data and save the results to a table that can be used for reporting purposes.
Finally, if the query complexity is too great for BigQuery, consider preprocessing outside BigQuery. Tools like Dataflow or Dataprep can transform and prepare the data before loading it into BigQuery, making the final table easier to query and process.
These alternatives—using a regular view, scheduling queries to materialize data, or preprocessing outside BigQuery—allow you to handle complex data aggregations without running into materialized view limitations.