ClickHouse CREATE MATERIALIZED VIEW ... ENGINE = ReplacingMergeTree(event_seq): 'Code: 16 ... Version column event_seq does not exist in table declaration (NO_SUCH_COLUMN_IN_TABLE)'
The version column of ReplacingMergeTree must be a column of the view's own target table, which means a column produced by the SELECT. It cannot be a column of the source table. Fix: alias it in the SELECT (max(event_seq) AS event_seq) or name the engine parameter after the output column (ReplacingMergeTree(last_seq)).
Failing DDL on clickhouse-server 24.3.18.7 (HTTP 500, Code: 16. DB::Exception: Version column event_seq does not exist in table declaration. (NO_SUCH_COLUMN_IN_TABLE)):
CREATE MATERIALIZED VIEW nessie.current_balances
ENGINE = ReplacingMergeTree(event_seq) ORDER BY account_id AS
SELECT account_id,
argMax(balance_cents, event_seq) AS balance_cents,
max(event_seq) AS last_seq
FROM nessie.balance_events GROUP BY account_idevent_seq exists in balance_events, but the view's implicit inner table only has account_id, balance_cents and last_seq.
Second gotcha with this pattern: a materialized view runs the GROUP BY per inserted block, not over the whole table. With a ReplacingMergeTree target you get one row per account per insert batch, and duplicates collapse only at background merges. Read it with SELECT ... FROM current_balances FINAL or argMax(...) GROUP BY account_id at query time. AggregatingMergeTree with argMaxState/argMaxMerge is the more rigorous alternative. For a ledger feed like Nessie v2 account.balance_changed events, keeping the version column as the event seq and reading with FINAL gives the latest balance per account.