One thought I have is that it could be worth starting with an initial prototype that queries your SQL directly. If it turned out that the overheads of querying the DB and performing transforms weren’t that bad, then you’ve just saved yourself a lot of work. Also, if you see a lot of the same queries, caching the callback functions (with an expiry value on the cache) will save time on at least some requests.
But if it turns out that there is too much latency or other costs associated with this, then periodically storing the transformed summary statistics is probably a good idea. And yes, having another process is probably a good idea, otherwise you will block the process running Dash (or at least one of the worker processes) from handling requests while it’s busy. You could setup a cron job to run some other script to do this, or use a scheduler like Celery. There’s a discussion on this topic in this thrad. I also suggest there the perhaps somewhat hacky approach of just spinning up another thread to do the batch querying and transforms. Probably not as performant as the other approaches but if you happen to want to keep everything in the same script, it could do the job.