Hello everyone 👋 Looking for some guidance on something I’d like to do, and if it is feasible to do so with the features available in Pinot. I have a table which sees a lot of traffic, and this table is also one of the most popular tables for time-based aggregations. However, all the aggregation queries want to get a “snapshot” for a customer during certain days, not necessarily only what has happened on that day. So for example, if I wanted to summarise the state of that customer every week, I would want to have a snapshot of the most recent state for that organisation every Monday, meaning all changes since then. Will I need to create a separate table to snapshot state to accomplish this, or does someone have an idea for how I could accomplish this?

yes. you better to create another table with type=refresh instead of default append mode

That makes sense.. and then for “appending” new weekly entries, I simply need to make sure I don’t discard the old segments?

yes, new segment should have the same name

Do I just override them in S3 then?

table_<partitionid> is a good convention to use

Gotcha thank you, this is helpful

but you will have to call the pushsegment api again

👍 How do I recover from failure in that case?

pinot will check that CRC is different and reload it

if the api call fails for some reason

its idempotent, you can retry

Yes, but not if I override them in S3 myself..

yes

👍 And pinot will move the segment to the s3 bucket where it should live?

yes

Ah awesome, it makes sense now

its very similar to regular upload flow, the trick is really in naming the segment

that should be fine

and ensuring you have the same number of partition

And sorting for the sorted index

ah ok

Is that an append table then? But just effectively overwriting previous days?

correct

That makes sense

nope

👍 thanks for answering all these questions

we dont do any retention

Ah right

for refresh

that makes sense

more for alerting