Hi Team, we're planning to have multiple fact tables as part of Pinot data model for streaming use-case with timestamp based incremental segments of 30 min. Before presenting, there is a requirement in final dashboard to join the data from these fact tables. Does Pinot support segment based lookup ? eg. lookup on fact2 from fact1 on key columns on selected fact2 segments (2 days) ?

can you tell a bit more about the query pattern?

Below is left outer join (to be implemented as lookup) select fact1., fact2.* from fact1 left outer join fact2 on fact1.key1=fact2.key1 and fact2.time_created between T1 and T2. fact2 segments are on timestamp (with segment width of 30 mins)

this should work in V2 @Rong R to confirm. If the number of keys between T1 and T2 is small you can probably convert this into subquery

Thanks @Kishore G In this use-case fact2 gets incremental inserts in time T2 and fact1 in time T1 (where T2 > T1). can Pinot support view on top of these 2 tables ? View query will trigger during dashboard refresh and latest attribute would be picked up from fact2 if available

is the

and fact2.time_created between T1 and T2

a join condition (as is T1 and T2 coming from fact1) ? • if so then the condition is not partition key-based and the plan will reduce into a nested-loop join and won't be efficient ◦ you can still run the query but the performance might be poor • if T1 and T2 are constant then the plan should become a distributed hash join and should result in reasonable performance

@Rong R - this is not a join condition but partition pruning on fact2 and similar partition pruning will be done on fact1 as well. fact2 and fact1 both are segmented on time with partition size of 30 mins. Question is can it be implemented as lookup where partition data is broadcasted ? OR like a join ?

Yeah it should be able to run it as a broadcast join

Thanks @Rong R and can Pinot support 'view' on top of these 2 ?

you meant

create view

syntax? or do you mean materialized view? do you have a more detail example on how the view is being used?