We using memsql columnar-store database.
We create pipeline for each “even-type” in our backend architecture and we create a dedicated table for it (we call it “source table”). so each pipeline writes to one table on mem
We have different business reports for customers that consist data from multiple source tables (not necessary tables that we can join because some fields can be existed on one table and some not)
So the only way to overcome this is to create huge and complicated queries which consists UNION functions making things much hard to maintenance and understand
What would be best practice to maintain and keep such architecture which will allow us to create multiple reports on multiple source tables and to reduce query complexity.
-
one way we thought is doing dedicated views per report. but we are not sure if we shall go that way. we need to create external ETA’s that will add more and more data to that view (a daily process)
what’s your thoughts regarding this? is that the way? -
second way just to manipulate tremendous queries with UNION functions
what we do today. we dont like this way because it run complexity on run-time on query time
I want to mention that those reports dont need to be updated on real-time. we can have buffer (hours) from one update to another
thoughts?