Is PIVOT Operator supported in Big Query Materialized view?

Question

Trying to create a materialized view in Big Query using PIVOT operator as below

CREATE MATERIALIZED VIEW ****
PARTITION BY <COLUMN1>
AS 

WITH TEST AS
(
  SELECT * FROM (SELECT * FROM TABLE1 WHERE COLUMN1 between 'DATE1' and 'DATE2')
  PIVOT (SUM(AMOUNT) AS AMOUNT, SUM(QUANTITY) as QUANTITY FOR METRIC_NM IN ('SALES'))
)
SELECT * FROM TEST ;

Below is the error I am facing where it says subquery or UDF is not suuported. The query contains an feature that is not supported in incremental materialized views (possibly a subquery or UDF).

Is PIVOT not supported while creating a materialized view? I couldn't find this for sure in Big Query documentation even in limitations of materialized view

Answer 1

PIVOT won't work in a materialized view. Try to make a view on top of the subset materialized view.

CREATE MATERIALIZED VIEW test_materialized_view
PARTITION BY COLUMN1 AS
SELECT COLUMN1, METRIC_NM, AMOUNT, QUANTITY
FROM TABLE1
WHERE COLUMN1 BETWEEN 'DATE1' AND 'DATE2';

CREATE VIEW test_pivot_view AS
SELECT *
FROM my_materialized_view
PIVOT (
    SUM(AMOUNT) AS AMOUNT,
    SUM(QUANTITY) AS QUANTITY
    FOR METRIC_NM IN ('SALES')
);