Materialized view of latest data with grouping + timestamp column

Question

I'm modelling traits (or attributes) in Bigquery. Here's a sample of the model

uid         string, link to owner id
uuid        string, unique among all rows
trait_name  string, name of the trait
trait_value string
added_at    timestamp, when the trait was added

I'm trying to build a materialized view that holds the latest trait for every trait of every uid. I'm able to get the result with this query:

WITH traits AS (
  SELECT 'u1' uid, 'uu1' uuid, 't1' trait_name, 't1v1' trait_value, timestamp("2021-10-01 10:00:00") as added_at UNION ALL
  SELECT 'u1' uid, 'uu2' uuid, 't1' trait_name, 't1v2' trait_value, timestamp("2021-10-02 10:00:00") as added_at UNION ALL
  SELECT 'u1' uid, 'uu3' uuid, 't2' trait_name, 't2v1' trait_value, timestamp("2021-10-03 10:00:00") as added_at UNION ALL
  SELECT 'u1' uid, 'uu4' uuid, 't2' trait_name, 't2v2' trait_value, timestamp("2021-10-04 10:00:00") as added_at UNION ALL
  SELECT 'u2' uid, 'uu5' uuid, 't1' trait_name, 't1v1' trait_value, timestamp("2021-10-05 10:00:00") as added_at UNION ALL
  SELECT 'u2' uid, 'uu6' uuid, 't1' trait_name, 't1v2' trait_value, timestamp("2021-10-06 10:00:00") as added_at UNION ALL
  SELECT 'u2' uid, 'uu7' uuid, 't2' trait_name, 't2v1' trait_value, timestamp("2021-10-07 10:00:00") as added_at UNION ALL
  SELECT 'u2' uid, 'uu8' uuid, 't2' trait_name, 't2v2' trait_value, timestamp("2021-10-08 10:00:00") as added_at
)
SELECT * FROM (
  SELECT *,
  MAX(added_at) OVER (PARTITION BY uid, trait_name) as latest_added_at FROM traits
) WHERE latest_added_at = added_at

#  Row  uid uuid    trait_name  trait_value added_at                 latest_added_at    
#  1    u1  uu2     t1          t1v2        2021-10-02 10:00:00 UTC  2021-10-02 10:00:00 UTC
#  2    u1  uu4     t2          t2v2        2021-10-04 10:00:00 UTC  2021-10-04 10:00:00 UTC
#  3    u2  uu6     t1          t1v2        2021-10-06 10:00:00 UTC  2021-10-06 10:00:00 UTC
#  4    u2  uu8     t2          t2v2        2021-10-08 10:00:00 UTC  2021-10-08 10:00:00 UTC

But I can't use it for a materialized views becauyse they don't support it:

Materialized views do not support analytic functions or WITH OFFSET.

I also tried using joins

SELECT * FROM traits t
JOIN (
    SELECT
    uid,
    trait_name,
    MAX(added_at) AS max_added_at 
    FROM traits GROUP BY uid, trait_name
) grouped
ON t.uid = grouped.uid 
AND t.trait_name = grouped.trait_name
AND t.added_at = grouped.max_added_at

But they are also not supported

Materialized views queries may not reference the same table more than once. Table traits was seen multiple times.

Is there a way to do it as a materialized view?

user_7931727 · Answer

Materialized views have limitations which may make this impossible to achieve (see this public documentation for additional information).

So currently it seems what you're trying to achieve is not fully supported and the use of ARRAY_AGG seems like the nearest possible, one of the disadvantages is the result is of array type, not the value itself.

I managed to retrieve the expected results with this query:

CREATE MATERIALIZED VIEW test_traits.mv_sample_traits as
SELECT uid,
       ARRAY_AGG(uuid IGNORE NULLS ORDER BY added_at DESC, uuid DESC, trait_value DESC LIMIT 1) as uuid,
       trait_name,
       ARRAY_AGG(trait_value IGNORE NULLS ORDER BY added_at DESC, uuid DESC, trait_value DESC LIMIT 1) as trait_value,
       ARRAY_AGG(added_at IGNORE NULLS ORDER BY added_at DESC, uuid DESC, trait_value DESC LIMIT 1) as added_at,
       MAX(added_at) AS latest_added_at,
FROM test_traits.traits
GROUP BY uid,trait_name


/*
SELECT * FROM test_traits.mv_sample_traits;
*/

Please note that the code is provided for reference and you will need to run tests to verify whether it fits your use case, there is no guarantee that this will work as expected therefore I cannot recommend it for use in production.

user_15113029 · Answer

I followed google documentation https://cloud.google.com/bigquery/docs/materialized-views to create materialized view for your requirement.

I inserted the data in a table as below

I ran below query to create a materialized view

CREATE MATERIALIZED VIEW  `graphical-reach-285218.test.traits1`
as select max(added_at) as latest_added_at,uid,trait_name from `graphical-reach-285218.test.traits`
group by uid, trait_name;

Materialized view gets created as below

On querying Materialized view, below is the data I get

Materialized view of latest data with grouping + timestamp column

2 Answers