Setting the file. One moment.
Chapter 03 · Clickhouse Architecture Advisor
Subchapter 3.2
examples/finserv-market-surveillance.mdMarkdown3 KBView on GitHub
This is not classic OLTP. It is a high-throughput analytical event pipeline with mutable business state derived from ordered events. The architecture should preserve append-only facts and compute latest-state views rather than forcing row-by-row transactional mutations.
What
Store all order lifecycle events immutably, then derive current order state.
Why
This preserves auditability and handles late-arriving business events without relying on heavy mutations.
Category
derived
Confidence
medium
Source
What
Maintain a latest-state table keyed by order identifier and version timestamp.
Why
If corrections naturally replace prior state, ReplacingMergeTree is often the cleanest documented pattern.
Category
official
Confidence
high
Source
What
Use dictionaries for symbol metadata, venue mappings, or account-tier lookups if they are read constantly and update slowly.
Why
Repeated runtime joins in hot surveillance logic are often more expensive than key-based dictionary lookup.
Category
official
Confidence
high
Source
CREATE TABLE order_events
(
event_time DateTime64(3),
trade_date Date,
order_id String,
account_id String,
symbol LowCardinality(String),
venue LowCardinality(String),
event_type LowCardinality(String),
qty UInt64,
px Decimal(18, 6),
version_ts DateTime64(3)
)
ENGINE = MergeTree
PARTITION BY toYYYYMM(trade_date)
ORDER BY (trade_date, symbol, order_id, event_time);CREATE TABLE order_state_latest
(
order_id String,
symbol LowCardinality(String),
venue LowCardinality(String),
status LowCardinality(String),
qty UInt64,
px Decimal(18, 6),
version_ts DateTime64(3)
)
ENGINE = ReplacingMergeTree(version_ts)
ORDER BY (order_id);This pattern is architectural, not transactional. If the requirement is strict OLTP locking semantics with many point updates per key, ClickHouse should not be the system of record for that path.