Use the correct tool for the job: hook an analytics DB up to the Kafka pipe and store the data for future queries. Kafka was never intended to support your use case.

If your inbound data that you'd like to put to Kafka isn't large, by the way, just write straight to the DB. It's irritating to see Kafka used where it's not necessary. It adds complexity to an infrastructure and the cost for doing so has to be justifiable.

I think you can use kafka streams to create whatever projections you want. Kafka can handle millions of small records per second. You can use the Kafka streams api to process all your data either into local rocks db stores or write the data to whatever database you want. This requires some work but then you have a good thing going if it works.

As a side note, we provide tooling to maintain Amazon Athena/Hive,Presto support over your Pyrostore archived data in S3, while maintaining its ability to be streamed in-order.