Hi, we have the following use case: We want to agg...
# troubleshooting
j
Hi, we have the following use case: We want to aggregate some data monthly, but the aggregated state should be emitted daily. Furthermore, we want to keep the aggregated state for a full extra month, so that we can handle late events. This all well and good if I can use the java datastream api. I can use a custom window assigner to get monthly windows, I can use a custom window trigger to get the daily updates and I can use allowedLateness to handle the late events. But my architect insists on me doing this in SQL such that if the business analyst wants to change the aggregation logic he can "just do it himself"™️ In SQL I don't know if it's possible to get these requirements to work. I can see two ways of doing it: • Using the CUMULATE window TVF in sql to create a monthly window but with daily output. The problem here is that I don't see an option to keep a window open in sql for late events. • I can just do a normal group by, with the group by key including the timestamp truncated to start of the month without a window TVF. I can use an idle retention setting to ensure that the group by state stays around long enough for late events to be handled. The problem here is that without the window TVF this will emit updates on every single record, instead of once per day, which is really killing the performance. Is there a better way to go about this?