Incremental materialized view - can't query more than once workaround

[Elior90]

I need to create incremental materialized view for reporting purposes having the following source table:

product	date	subproduct	sector
A	2024-01-01	AA	X
A	2024-01-01	AA	X
A	2024-01-01	AB	Y
A	2024-01-02	AA	X
B	2024-01-01	AA	X
B	2024-01-01	AB	X
B	2024-01-01	AB	Y
B	2024-01-01	AB	Y
B	2024-01-02	AA	X
B	2024-01-02	AA	Y
C	2024-01-02	AA	X
C	2024-01-02	AA	X

The aggregated result should be the following:

product	date	subproduct	count
A	2024-01-01	AA	2
A	2024-01-01	AB	1
A	2024-01-02	AA	1
B	2024-01-01	AA	1
B	2024-01-01	AB	3
B - target	2024-01-01	AB	2
B	2024-01-02	AA	2
B - target	2024-01-02	AA	1
C	2024-01-02	AA	2

In other words, some of the product B (these in sector = Y) need to be counted twice - first time as part of B products and one more time as "B - target", because they are part of sector = Y

I tried to create the view using the following query:

SELECT 
product,
date,
subproduct,
count(*)
FROM table a
GROUP BY product, date, subproduct 
UNION ALL
SELECT 
'B - target' as product,
date,
subproduct,
count(*)
FROM table a
WHERE product = 'B' and sector = 'Y'
GROUP BY product, date, subproduct 

But I receive the the message that in BigQuery I can't target the same table twice in incremental materialized view.

I tried with CASE

CASE
WHEN product = 'B' and sector = 'Y' THEN 'B -target'
ELSE product 
END AS product 

But this time they are not included in the total B count.

Then I tried to use CTE and then use it along with another table that I need to use for other portion of the data:

CREATE MATERIALIZED VIEW 'test_view'
AS
WITH cte as (
    select * from table a
),
aggregations(
SELECT 
    product,
    date,
    subproduct,
    count(*)
FROM cte a
GROUP BY product, date, subproduct 
UNION ALL
SELECT 
    'B - target' as product,
    date,
    subproduct,
    count(*)
FROM cte a
WHERE product = 'B' and sector = 'Y'
GROUP BY product, date, subproduct 
UNION ALL
SELECT 
    product,
    date,
    subproduct,
    count(*)
FROM table_2 b
GROUP BY product, date, subproduct)
SELECT * FROM aggregations

But getting the following warning - Incremental materialized view query contains unsupported features. 

Any ideas what can I do?

[Elior90]

Or it is not possible to use materialized view in this case?

[ms4446 · Accepted solution]

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.

[Elior90]

Thank you for the help!

[sm_gcp]

> 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.
Hi, does presence of the CTEs and joins (where both left and right tables change) in the Materialized view query is a restriction for incremental updates?

[gojoe]

UNION ALL and LEFT OUTER JOIN are both currently supported in preview. See https://cloud.google.com/bigquery/docs/materialized-views-create#left-union

CTE is supported subject to the same limitations as MVV queries without CTE.  See  https://cloud.google.com/bigquery/docs/materialized-views-create#with_clause_and_common_table_expres...