Hello everyone, I am working on a customer facing analytics application, and I am planning on using flinkSQL to join multiple kafka topics. The result will be in a ClickHouse table. The topics are CDC events produced by debezium, and I would like to join rows from topic A with rows from topic B when the creation date of rows B are always inferior than the update date of rows A. What is the impact of the state size if I just did the following?

SELECT
...
FROM A
JOIN B ON B.aid = A.id
AND A.created_datetime <= B.updated_datetime

I can maybe use temporal joins on a 1-year period but it has a risk of data loss in case some entities have updates that span over more than 1 year. Anyone with experience on this or just ideas on how to properly design such a pipeline?

What is the impact of the state size if I just did the following?

Unless you use a temporal join the state will keep growing, since Flink would need to persist records for both sides of the join. This is not necessarily a huge problem, it just means you’d need more resources to store it. I gave a talk about a similar problem we tackled at Shopify, it involved joining 10+ CDC topics with similar requirements. The talk targets the DataStream API though: • • https://www.slideshare.net/sap1ens/storing-state-forever-why-it-can-be-good-for-your-analytics