ReplacingMergeTree vs AggregatingMergeTree for maintaining up-to-date order state from event streams?

[coderaing]

Contemplating on what is the best way to regenerate an orders table from our raw_events MergeTree table.

Basically maintain an up-to-date orders table that reflect the current state of the order based on incoming change events. Use case is for various analytical queries like "revenue from cancelled orders" etc.

Context

• We have a raw_events MergeTree table that receives order-related events
• Events types include: order_created, order_updated, order_fulfilled, order_cancelled
• Different event types update different properties:
  • order_created: Most of the properties that we need come from this event, including the order_date
  • order_updated: Can modify any order property
  • order_fulfilled/order_cancelled: Only update specific columns (e.g., status, cancelled_at)

What I have tried

I'm considering two approaches:

1. AggregatingMergeTree:

CREATE TABLE orders (
    `order_id` String,
    `total_items` AggregateFunction(argMax, Nullable(Int64), DateTime64(3)),
    `total_price` AggregateFunction(argMax, Nullable(Float64), DateTime64(3)),
    `currency` AggregateFunction(argMax, Nullable(String), DateTime64(3)),
    `status` AggregateFunction(argMax, String, DateTime64(3)),
    `version` UInt64
) ENGINE = AggregatingMergeTree()
ORDER BY order_id;

With a materialized view to maintain it:

CREATE MATERIALIZED VIEW orders_mv 
TO orders 
AS
SELECT
    JSONExtractString(properties, 'order_id') AS order_id,
    argMaxState(
        if(JSONHas(properties, 'total_items'), JSONExtractInt(properties, 'total_items'), null),
        event_time
    ) AS total_items,
    -- Similar patterns for other fields
    argMaxState(event, event_time) AS status,
    count() AS version
FROM raw_event
WHERE event IN ('order_created', 'order_updated', 'order_shipped', 'order_cancelled')
  AND JSONExtractString(properties, 'order_id') != ''
GROUP BY order_id;

Querying requires merging the aggregate functions:

SELECT 
    order_id,
    argMaxMerge(total_items),
    argMaxMerge(total_price),
    argMaxMerge(currency),
    argMaxMerge(status)
FROM orders
GROUP BY order_id
ORDER BY order_id;

2. ReplacingMergeTree:

• ORDER BY order_id
• Using event_time as version

Questions

• Which approach would be the most efficient for our use-case?
• Are there any performance implications of using AggregateFunction(argMax, ...) for every column?
• Would ReplacingMergeTree be simpler to maintain while achieving similar results?

[qoega]

• If you can popoulate correct last value ReplacingMergeTree may be more efficient.
• There are obvious performance implications as you need to read all granules that may have values for your key. I do not expect Replacing to use more resources vs Aggregating