0

I have multiple time-series, for example, for different categories and products. So my data have the following columns: time, category, product, sales. Should I just create a normal table with time partition and category+product clustering, or should I create a nested structure such as

SELECT category, product, ARRAY_AGG(STRUCT(time, sales) ORDER BY time ASC) as series
FROM data_source
GROUP BY 1, 2

The end goal is to visualize the data in Looker, where I would like to see the trending for multiple category and product, i.e. (number of products x number of categories) time-series.

Khanetor
  • 11,595
  • 8
  • 40
  • 76
  • 1
    With nested structure, you cannot use clustering for time and sale. If I don't remember wrong, you'll have to flatten the structure in your queries in Looker Studio. – Roar S. May 18 '23 at 12:06
  • 1
    Thank you! I tried the above approach, and Looker can read the data just fine, with no difference from using a flat table. However, I think you are right about the clustering. It can make drill down more inefficient this way. – Khanetor May 19 '23 at 11:58

0 Answers0