Hi 👋 Please advice a better solution for a pretty standard case. I have a table:

dim1, dim2, dim3, dim4, daysSinceEpoch, hoursSinceEpoch, metric

I need to query

sum(metric)

grouped by days OR by hours with optional dimensions. Cardinality of dimensions is pretty low: from 3 to few hundreds unique values. I decided to use star-tree index for that. Is having both

daysSinceEpoch

and

hoursSinceEpoch

in

dimensionsSplitOrder

a good approach here?

"dimensionsSplitOrder": [dim1, dim2, dim3, dim4, daysSinceEpoch, hoursSinceEpoch]

TTL of data is 3 months.

This sounds similar to my question: https://apache-pinot.slack.com/archives/C01H1S9J5BJ/p1681538176648699?thread_ts=1681538176.648699&cid=C01H1S9J5BJ Interested to hear what suggestions you get

Since the TTL is only 3 months the max number of unique values would be around 2k which is low so should be safe to add both Hours/Days as dimensions in ST index.

got it! Thank you

Explain Plan (https://docs.pinot.apache.org/users/user-guide-query/explain-plan) should be able to help

Would be great to see an example of how

daysSinceEpoch

is calculated. Is this done using an Ingestion Transformation?

Yes you can use ingestion transformation to calculate this field using this function (https://docs.pinot.apache.org/configuration-reference/functions/toepoch)