How we detect outliers in real time using ClickHouse®

A single oversized order can trigger cascading liquidations across multiple pairs. Detecting those trades before the volatility hits matters. But defining what's "too big" or "unusual" is not easy when you process 15 million events per day.

Every day, new assets and pairs appear, volumes shift, and prices swing. What was considered a big trade a couple of weeks ago is irrelevant now that the market cap has increased. And the reverse: a trade that looked small last month can shake the market after a crash. Defining one fixed threshold that fits such an evolving environment is the perfect recipe for disaster. Either you'll be drowned in alerts or you'll receive so few that you'll think the markets are closed.

We needed a moving baseline that could flag the biggest trades no matter the market conditions. And we built it: two chained materialized views, a rolling quantile, and nothing outside ClickHouse. 21ms from insert to detection.

This is part of the ClickHouse Deep Dive series. If you haven't read the posts on materialized views and AggregatingMergeTree, those cover the building blocks we use here.

The ClickHouse architecture

The pipeline has three pieces: a source table where trades land, and two materialized views that fire on every insert in the source table.

Source trades flow through two materialized views, one computes the rolling quantile baseline, the other flags outliers

The source table

Trades from multiple exchanges land in a single table:

CREATE TABLE trades(    trade_timestamp DateTime64(3),    user_id         String,    asset           String,    amount          Float64)ENGINE = MergeTree()PRIMARY KEY (asset)ORDER BY (asset, user_id, trade_timestamp);

Computing the daily quantile state

Before we can detect outliers, we need to know what "normal" looks like. Every time a new trade arrives, this MV updates the quantile state for the asset and day of the trade. This state captures the distribution of trade sizes without storing every individual value, which is what allows us to compare new trades against 30 days of history, roughly 450 million events, without re-scanning a single raw trade.

CREATE TABLE trades_quantile(    day      DateTime,    asset    String,    quantile AggregateFunction(quantile(0.99), Float64))ENGINE = AggregatingMergeTree()ORDER BY (asset, day); CREATE MATERIALIZED VIEW trades_quantile_mv TO trades_quantile ASSELECT    toStartOfDay(trade_timestamp) AS day,    asset,    quantileState(0.99)(amount)   AS quantileFROM tradesWHERE amount > 0GROUP BY day, asset;

A few things happening here.

The target table is an AggregatingMergeTree. The quantile column has type AggregateFunction(quantile(0.99), Float64), which stores an intermediate state, not the final computed value. This is similar to how we stored OHLC candle states using argMinState/argMaxState. The pattern is the same: store partial aggregates, finalize on read.

quantileState(0.99) in the MV computes the state from the raw amount values. One detail worth noting: the 0.99 here doesn't lock you into p99. The state stores the underlying distribution, not a single percentile result. When you finalize it later with quantileMerge, you can query whatever percentile you need: 0.1, 0.5, 0.95, 0.99. The state is the same regardless. So if you decide next month that p95 is a better threshold, you just change quantileMerge(0.95) in large_trades_mv and you're done. No backfill, no rebuild.

If multiple batches of trades arrive for the same day and asset, AggregatingMergeTree merges their states during background compaction. The result is one row per asset per day. Even with hundreds of assets over months, you're looking at tens of thousands of rows.

The detector

Now that we have a baseline of what "normal" looks like, we need to compare every incoming trade against it. That's what this second MV does: it reads the last 30 days of quantile states from trades_quantile, merges them into a single threshold per asset, and checks if the new trade exceeds it. If it does, the trade lands in the large_trades table. If not, it's discarded before it ever hits disk. This MV also fires on every insert to the source trades table:

CREATE TABLE large_trades(    trade_timestamp DateTime64(3),    user_id         String,    asset           String,    amount          Float64,    detected_at     DateTime DEFAULT now())ENGINE = MergeTree()PRIMARY KEY (asset)ORDER BY (asset, user_id, trade_timestamp); CREATE MATERIALIZED VIEW large_trades_mv TO large_trades ASWITH thresholds AS (    SELECT        day,        asset,        quantileMerge(0.99)(quantile) OVER w AS threshold    FROM trades_quantile    WINDOW w AS (        PARTITION BY asset        ORDER BY day ASC        ROWS BETWEEN 29 PRECEDING AND CURRENT ROW    ))SELECT    t.trade_timestamp,    t.user_id,    t.asset,    t.amount,    now() AS detected_atFROM trades AS tINNER JOIN thresholds AS th    ON toStartOfDay(t.trade_timestamp) = th.day    AND t.asset = th.asset    AND t.amount >= th.threshold;

Let's break it down.

The CTE thresholds reads from the trades_quantile table (trades_quantile_mv's target) and uses quantileMerge(0.99) as a window function. ROWS BETWEEN 29 PRECEDING AND CURRENT ROW tells it to merge the quantile states from the last 30 days. The result is a single p99 value per asset per day, computed from 30 days of data. 450 million trades compressed into 30 rows of quantile state per asset.

The INNER JOIN then compares each incoming trade against the asset's current threshold. Only trades where amount >= threshold survive the join. Everything else is filtered out before it reaches the large_trades table.

One thing to keep in mind: when a brand new asset appears, there's only one day of quantile state for it. The threshold is computed from just that first day's trades. As more days accumulate, the 30-day window fills up and the threshold stabilizes. For most use cases this is fine, the first day's p99 is still a reasonable baseline. If you need a minimum history before detection kicks in, add a count() OVER w AS days_of_data column to the CTE and filter on it.

The full pipeline

When a batch of trades lands in the source table:

1. trades_quantile_mv fires, computes quantileState from the new trades, and writes the result to trades_quantile. AggregatingMergeTree handles merging with existing state for the same day/asset.
2. large_trades_mv fires, reads the last 30 days of quantile states from trades_quantile, merges them with a window function, joins against the incoming trades, and writes outliers to large_trades.

Both MVs trigger on the same insert.

Design decisions

Why quantile

Whether a percentile threshold works as outlier detection depends on your data's shape. On a uniform distribution, p99 just means "top 1%," nothing special. But trade sizes are not uniform.

We looked at 145,270 trades from our pipeline. The standard deviation is 9x the mean. P99 is 50x the median. Skewness of 42.8, kurtosis of 2,557.

Trade size distribution from 145,270 trades showing a power law shape, 90% of values cluster below P10 while the tail extends 500x further

Most trades are tiny. Anything at p99 is orders of magnitude away from where the data concentrates. The distribution itself makes a percentile threshold meaningful as outlier detection. And as market conditions change, the quantile moves with them.

This is a filter, not an oracle

A p99 threshold on trade size won't tell you if a trade is suspicious, manipulative, or worth acting on. It tells you it's unusually large relative to recent history. That's it. The pipeline is a first step that reduces 15 million daily events to a manageable set of candidates. A second process takes over from large_trades and does the actual analysis: context, counterparties, timing, whatever your use case requires.

This is by design. We'd rather flag too many and let the downstream process discard the noise than miss the one trade that mattered.

Choosing a quantile function

ClickHouse offers several quantile variants. We use the simplest one, quantile. Here's how each works and why it matters for this pipeline:

• quantile keeps a random sample of 8,192 raw values. Merging is just probabilistic array swaps with no sorting. The fastest option, accurate enough for our use case.
• quantileTDigest compresses the distribution into centroids. Smaller state, better accuracy (~0.5% error), but merging requires periodic sorting.
• quantileGK maintains a sorted structure with rank metadata. Smallest state, but every merge requires compression and sorted interleaving. On heavy-tailed data like ours, it needs very high accuracy values (1000+) to produce useful results at extreme percentiles.
• quantileExact stores every single value. Exact results, but O(n) memory. For our dataset that's 3.14 GB. It can't keep up with the insert rate.

We benchmarked all four on our production cluster:

The race condition that doesn't matter

Both materialized views trigger on the same insert. ClickHouse doesn't guarantee the order they execute in. So large_trades_mv might evaluate the threshold before trades_quantile_mv has updated the quantile state with the current batch.

The 30-day window contains hundreds of thousands of trades per asset. Adding or removing one trade shifts the p99 rank by one spot out of hundreds of thousands. The quantile function's inherent approximation error (~1%) already covers a much larger range of positions than that. The race condition's effect is noise within noise.

No single trade, no matter how large, can meaningfully shift a quantile computed over hundreds of thousands of observations. If your window is very small (a few hundred observations), you'd want to think about this more carefully. At our scale, it's not a concern.

Taking it further: HTTP webhooks

The large_trades table is useful on its own. But you can also turn it into a real-time alerting system. ClickHouse's URL table engine lets you use an HTTP endpoint as a materialized view target:

CREATE MATERIALIZED VIEW alert_webhookENGINE = URL('https://your-endpoint.com/alerts', 'JSONEachRow')AS SELECT    asset,    amount,    detected_atFROM large_trades;

Every outlier that lands in large_trades fires an HTTP POST to your endpoint: Slack, PagerDuty, a custom alerting service, anything that accepts JSON.

What we covered

Two chained materialized views, one AggregatingMergeTree table, and a window function. That's the entire detection pipeline. No external services, no application code between the database and the result.

trades_quantile_mv pre-computes daily quantile states using quantileState. large_trades_mv merges 30 days of those states with quantileMerge in a window function and joins against incoming trades to flag outliers. 21ms from insert to detection on our cluster.

The quantile function choice affects latency significantly (21ms vs 795ms depending on the variant), but all variants share the same State/Merge combinator pattern and the same architecture.

Previous posts in the series: Materialized Views and AggregatingMergeTree cover the building blocks. OHLC Candles shows another production pipeline built on the same foundations.

If you're running ClickHouse at scale and want to skip the infrastructure work, ObsessionDB is the managed ClickHouse service we built from this experience.

ClickHouse® is a registered trademark of ClickHouse, Inc.