Handling updates in Apache Druid for data from a commerce system

Question

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. I am considering Apache Druid as it seems to work pretty well with a visualization tool like Metabase (or other similar ones).

What I am unable to figure out is handling updates to records in such an immutable store. 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 an immutable store?

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 push a transaction to the data warehouse only 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) (which kind of goes against the "Real Time Analytics" capability of Druid).

Is that the only way out? Is there a better approach?

This has been asked and partially answered here. The approach mentioned there can get unwieldy if I have to maintain separate lookup tables for order total amount, individual line items, etc as they all could change during the course of the order's lifecycle.

P.S : I posted the same question here first only to realise that Apache Druid recommends Stackoverflow. If this double posting is not in good taste then mods can archive this question.

Answer 1

Many implementations of Druid have similar circumstances. There are two ways in which these updates are resolved:

• In real-time streaming ingestion, multiple changes to the row are ingested as individual events and queries use LATEST aggregation function on such status columns to see the latest value.
• In batch, immutable files can be replaced with a new ingestion by using:

REPLACE INTO tableX OVERWRITE WHERE <time condition>
SELECT <your fields with or without transformations>
FROM TABLE(EXTERN( <the source of your data for reingestion>))
PARTITIONED BY <segment granularity>

Take a look at some examples here: https://druid.apache.org/docs/latest/multi-stage-query/examples#replace-for-replacing-a-specific-time-segment