Load SQL table data into Azure Redis Cache

Question

We have Product data in SQL table & any changes on Product will be written onto Kafka and then into Redis Cache.

Some Users will be using Kafka events to get the latest events for products & some users have batch process that runs daily who needs to run queries on entire Product data, they don't want to maintain the product data locally in their systems as well after getting from Kafka events. So they need access to our table data, as we do not want to give access to our OLTP tables directly we choose to spin up Azure Redis Cache where all events will be written into it & they can get the data from there.

Questions:

1. How to load existing data from SQL table into Azure Redis Cache ? After initial load Kafka will write onto Redis Cache.
2. If we have 1M data do we need to create 1 million key value pairs for Products in Redis ?
3. How to refresh whole Redis Cache when something is out of sync from Product table ?
4. Should we use Redis Cache for this purpose ? or Kafka writing onto Azure Data Lake is good approach and users need to retrieve data from data lake ?

Answer 1

they don't want to maintain the product data locally in their systems as well after getting from Kafka events.

This is the pattern I'm most used with when dealing with Kafka. For users that don't want to store all of this data in their own DBs, they usually rely on BigQuery (similar to Azure Data Lake, I think) to run analysis or even some operational scripts (probably not good to do this though). I wouldn't recommend building entire apps on top of it.

I'm no expert on Redis but I feel like it would be a good solution if you were looking into creating a cache with a subset of the data you want to expose, instead of just replicating all of your DB. Otherwise I think you'll end up having to write some logic to keep it consistent with your DB, while exposing it to other teams. One more thing to support/monitor. It feels to me that this is a poor replacement of just exposing a read replica of your DB to them (pretty bad as well).

If they really don't want to use Kafka and store the data locally then I'd go with exposing an API and let them query it as they want, without having to spin a Redis instance. Probably a simple project that won't need a whole lot of maintenance.

There's also other things you users could explore such as ksqlDB, this would remove the burden of maintenance from you and keep things clean on your side (ie relying on Kafka as your sole output medium)