I have a digital commerce system that we have built in-house and it has been working fine. We now want to implement OLAP to provide our customers with advanced reporting and BI features.
While looking for the needed components I came across several "immutable" data stores which seem popular choices as data warehouses. Ex : Apache Druid, Clickhouse etc. These seem to work pretty well with a visualization tool like Metabase (or other similar ones).
What I am unable to figure is handling updates to records in such immutable stores. Like in any order management system, orders in our system also get updated (status, line items, quantities etc) over the course of their lifetime. In such a case how would one use these immutable stores?
Even if I were to use something like a star schema, I can see needing updates to both fact and dimension tables. Ex if a sale is recorded as a fact with its total revenue, the amount might need an update or the order might get cancelled, items can change etc.
The only way i can think of is to only push a transaction to the data warehouse after it is CLOSED aka frozen. In this case all metrics and reporting for in-process orders will have to be based on the main DB (aka OLTP DB).
Is that the only way out? Is there a better approach?