OLAP Database Selection for Risk and Fraud Detection Engines

Fraud detection lives or dies on a single question: can the database answer "has this card shown up in two countries in the last 10 minutes?" fast enough to matter. That's not a generic analytics workload. It's a specific set of demands, sub-second time-window math, high-concurrency scoring, ingestion that doesn't lag, and latency that holds steady even under load, that narrows the field of usable database architectures down to almost nothing.

Think about what the question actually asks. "Has this account made more than 20 transactions in the last hour?" is not a row lookup. It's an aggregation over a moving window, computed across potentially millions of records, and it needs to finish before the payment authorizes or declines. That's the frame for everything that follows.

Row-oriented databases, the kind built for OLTP, fall apart here. They're built to read and write single records fast: pull one customer, update one balance, commit one transaction. Asking that same database to aggregate across millions of rows in real time makes it scan far more data than it needs to, because it wasn't designed to skip irrelevant columns or rows efficiently.

Batch-oriented warehouses fail for a different reason. They're often excellent at crunching huge datasets, just not on the clock a fraud decision runs on. A query that takes two minutes to return an answer is a postmortem. It's a postmortem. By the time it resolves, the transaction already went through, or didn't, and the analysis is now historical rather than protective.

A columnar OLAP engine like the ones this piece focuses on is not a system of record. It doesn't replace the ledger that holds the actual, authoritative transaction history. Its job is narrower and, in this context, more urgent: sit alongside that system of record as the analytical layer that evaluates signals fast enough to act on them. That boundary matters, because overselling what an analytical database is for leads to bad architecture decisions later.

The canonical pipeline architecture: Kafka ingestion, materialized views, and the decision engine

A workable fraud pipeline tends to follow the same shape, regardless of vendor: a payment API feeds events into Kafka, Kafka streams into the OLAP engine through a native ingestion table, materialized views pre-aggregate that data continuously, and a decision engine queries those aggregates to produce a score. One 2026 guide on fraud detection built around a column-oriented OLAP engine lays this out almost as a reference blueprint, and it's worth understanding why each piece sits where it does.

Kafka sits in front because fraud rarely appears as one bad row. It appears as a pattern in repeated failed logins from the same IP, a high-value account hit from several devices within minutes, and replayed events that look identical except for a timestamp. Streaming, not batch, is how those patterns become visible while they're still happening.

Materialized views are where the real engineering payoff sits. Instead of recalculating "how many transactions has this account made in the last hour" from scratch on every scoring call, an AggregatingMergeTree table keeps a running, incrementally updated aggregate. Account-level counters track transaction count, total amount, distinct countries, and distinct devices, typically over a rolling one-hour window. Card-level counters do the same over 24 hours, adding distinct IPs into the mix.

That precomputation is what turns a "scan millions of rows" query into a sub-millisecond lookup. It's the entire reason to invest in schema design up front instead of leaving aggregation logic to query time.

The decision engine itself doesn't run the scoring math. It pulls scores that the database has already computed and asks a simpler question: approve, decline, or flag for review. That division of labor only works if the database can be trusted to keep up. If ingestion lags, or a batch insert takes significantly longer than expected, the materialized views go stale, and the decision engine starts making calls on outdated information. The SLA doesn't stay local to the database. It propagates straight back through the pipeline: a 200 millisecond delay at the database layer becomes a 200 millisecond delay on every transaction waiting for a verdict.

Schema design choices that make or break fraud query performance

Table engine choice isn't a detail to skim past. It decides whether the whole pipeline holds up under load.

Base transaction event tables generally use a MergeTree engine: data lands in immutable parts, sorted by primary key, and gets merged in the background over time. Velocity counter tables use AggregatingMergeTree, where state functions like countState, sumState, and uniqState accumulate values incrementally instead of recalculating from scratch on every insert. Fraud labels, which often arrive well after the original transaction (a chargeback confirmed weeks later, for instance), get their own table using ReplacingMergeTree, ordered by transaction ID and versioned by the label timestamp. That keeps label updates from ever touching, or mutating, the core events table, which is expensive to rewrite. CollapsingMergeTree exists too, useful for tracking state changes over time, but it adds enough query complexity that most fraud schemas avoid it unless the use case specifically calls for it.

Primary key ordering affects query speed: for account-centric fraud queries, ordering by account ID first and then by timestamp (ORDER BY account_id, occurred_at, per the schema pattern described in the OneUptime guide) lets the database locate relevant rows faster. For account-centric fraud queries, ordering by account ID first and then by timestamp lets the database locate a given account's recent activity without scanning unrelated rows.

Partitioning should stay strictly time-based, using something like a year-month key. Partitioning by campaign, channel, or user ID instead seems intuitive but backfires: time-based partitions let the database skip entire chunks of data outright when a query only cares about, say, the last hour. Partition by anything else and that skip logic breaks down.

For columns like merchant ID, merchant country, merchant category, currency, channel, and status, a LowCardinality encoding cuts storage substantially, on the order of 10x according to the ClickHouse Query Performance Optimization guide, and speeds up GROUP BY queries in the process. It's a small setting with an outsized effect on both cost and speed.

On the subject of fraud labels again: the pattern worth repeating is a separate labels table, joined at query time, rather than mutating the core events table every time a label comes in late. Mutations in an engine using a merge-based storage layout are expensive operations, and running them constantly against a high-write fraud events table is asking for trouble.

Lightweight deletes offer a middle path. They update an internal bitmap marking rows as deleted, and the actual physical removal happens later during normal background merges. That's fast to execute, but it can slow down subsequent SELECT queries slightly, since the database has to check that bitmap. For a workload that's read-heavy, like fraud scoring, that trade-off deserves a second look before it becomes the default.

TTL settings need care too. TTL rules run during background merges, not at insert time, so there's a lag between when data should expire and when it actually disappears. Aligning partition boundaries with the TTL time unit, and setting ttl_only_drop_parts=1, lets the database drop entire parts cleanly instead of triggering a slow, mutation-style rewrite of partial data.

And then there's ingestion hygiene. Too many small parts landing in short succession triggers what's often called a merge storm, background merge operations competing for resources faster than they can complete. Inconsistent insert latency combined with that failure cascades: velocity counters fall behind, materialized views go stale, and the fraud engine starts scoring against old data without anyone necessarily noticing right away.

How ClickHouse executes the fraud scoring queries that matter most

The queries a fraud engine actually runs, over and over, in production, tend to fall into a handful of patterns.

Velocity checks merge aggregate states (countMerge, sumMerge, uniqMerge) over a one-hour window for a given account. This is the exact scenario materialized views were built for: instead of scanning raw events, the query merges pre-computed partial aggregates, which is dramatically cheaper.

Multi-country card detection works differently. It's effectively a self-join against the transaction events table, checking whether the same card ID appears under different merchant countries within a tight window, something like 10 minutes. That's a classic card-not-present fraud signal, and it's computationally heavier than a velocity check because it involves matching rows against each other rather than just summing values.

Composite fraud scoring pulls several of these signals together at once: high transaction count, unusually high amount, multiple countries, a device switch, a large online purchase. Rather than running five separate queries, a well-designed system evaluates all of them in a single pass over recent pending transactions, producing one fraud_score per transaction.

Longer-lookback queries also matter, just on a different clock. Something like historical fraud rate by merchant category, using countIf over a 90-day window grouped by category, informs how rules and thresholds get tuned. It doesn't need to run in real time, and it shouldn't compete with real-time scoring for resources.

What makes all of this fast enough to use is data skipping. Primary key indices let the engine skip entire blocks of rows, commonly grouped in fixed-size row blocks, that can't possibly match a query's filter. A useful sanity check here: the number of rows actually read by a query should track closely with the number of rows that genuinely match the WHERE clause. If those two numbers diverge a lot, something in the schema or index design isn't pulling its weight. Skip indices, often bloom filters, extend that same logic to columns that aren't part of the primary key, which matters because fraud rules frequently filter on things like device ID or IP address that sit outside the primary key ordering.

JOIN performance used to be a real weak spot in this class of database. Updates through 2024, 2025, and into 2026 have narrowed that gap substantially, and benchmark comparisons now show competitive, sometimes better, performance against other major analytical platforms on join-heavy workloads. A production demo at a meetup in August 2025 put a number on what this looks like at extreme scale: a single query scanned 96 trillion events over a one-hour window and returned in less than two seconds, with a margin of error under 1%. That figure exceeds a typical fraud workload's data volume, offering a useful signal that the underlying architecture holds up even when pushed toward the event counts seen at another large-scale vendor.

The latency floor imposed by S3-routed architectures and why it matters for fraud SLAs

Most managed analytical databases in 2025 and 2026 separate storage from compute: data sits in cheap, durable object storage like S3, and compute clusters spin up and down independently to process it. That separation is genuinely good for durability and for elastic scaling. It's not free, though, and the cost appears specifically on the read path when data isn't already sitting in memory.

Cold-query latency against S3 metadata runs, per reported benchmarks, around 10 milliseconds at the median and 17 milliseconds at the 90th percentile. Google Cloud Storage is in a similar range, with reported figures of roughly 12 to 18 milliseconds at the median and 15 to 25 milliseconds at the 90th percentile. Those numbers describe metadata access alone. Actually rehydrating real data from cold object storage adds more on top, and at the far tail of the distribution, cold-storage query latency can stretch into the hundreds of milliseconds.

That's a real problem for a fraud SLA sitting under 250 milliseconds. The database's query latency is a direct component of the SLA. It's a direct component of the SLA. And the queries most likely to hit a cold path, ones involving a pattern the cache hasn't seen recently, are exactly the novel fraud patterns the whole system exists to catch. The tail latency raises the stakes hardest on the cases that matter most.

Object storage pricing did move in a useful direction. Price cuts in April 2025, reported as roughly 85% on GET requests, 55% on PUT requests, and 31% on storage itself, made a faster storage tier, priced around $0.11 per gigabyte per month (several times the cost of the standard tier), viable for keeping frequently accessed data on faster infrastructure. That's a meaningful economic shift. But cheaper fast storage doesn't automatically mean a given managed service uses it well.

The real dividing line between managed offerings isn't pricing, it's architecture: does every query route through object storage regardless of how "hot" the data is, or does the service maintain a memory or NVMe cache layer that keeps frequently accessed fraud data off the object storage read path? That question deserves a direct answer from any vendor before a fraud team commits to it.

A separate, related lesson comes from a production incident involving Cloudflare's infrastructure, where a replica accumulated 30,000 parts and triggered a lock contention bug during query planning. Parts later grew to 160,000 as the contention issue progressed. The point generalizes beyond that one incident: uncontrolled part growth degrades query performance regardless of what storage tier sits underneath it. Storage architecture and part management are two separate failure modes, and both need attention.

What concurrency and ingestion performance thresholds a fraud engine should demand

Vendor evaluations tend to run one query at a time and call it a benchmark. Fraud engines don't work that way. A single incoming transaction can trigger a dozen or more concurrent velocity lookups, so concurrency, not single-query speed, is the number that actually predicts production behavior.

A few thresholds, drawn from a 2026 performance guide, function less like best practices and more like pass or fail lines:

Dashboard and scoring queries taking longer than 3 seconds flag a problem that needs immediate investigation, and fraud scoring needs to sit well under that ceiling, not near it. Concurrent throughput below 100 queries per second on simple aggregations points to resource contention or weak indexing, and since velocity checks are simple aggregations by nature, a database that struggles at 100 QPS should be disqualified. Batch insert latency above 500 milliseconds signals partition key or merge tree configuration trouble, which matters because Kafka batches have to land and become queryable before the next scoring cycle starts. More than 100 active parts per partition warns of merge storms forming, the same failure mode that degrades both ingestion and query speed at once. Memory use climbing above 80% of available RAM during queries suggests partitioning or query design needs rework. And background merges that take longer than an hour point to oversized parts or poor merge tree tuning, a quiet failure that eventually appears as query slowdowns under load.

None of these are aspirational targets to work toward. They're boundary conditions. Crossing them stops the fraud pipeline from meeting its own SLA, quietly at first, then obviously.

Evaluating managed ClickHouse options: what the pricing and operational trade-offs look like

Self-hosting runs somewhere between $2,435 and $30,720 a month in infrastructure costs alone, according to available research, and that figure doesn't include the people needed to run it. Budget something like a quarter to half of a full-time engineer per cluster just for upgrades, maintenance, and incident response. For a production-critical fraud system, a cluster that a fraction of one engineer is responsible for is, by definition, a reliability risk.

Managed offerings price differently: somewhere in the range of $0.22 to $0.75 per compute unit-hour, with storage running $25.30 to $50 per terabyte per month. Entry-level development tiers start around $67 a month, while production tiers scale up past $100,000 a month depending on compute needs and cloud provider. That's a wide range, and where a given fraud workload lands on it depends heavily on transaction volume and query concurrency.

The calculus for a fraud team isn't simply "managed is safer." It's whether the managed service's underlying architecture preserves the latency behavior the SLA actually needs. Some managed offerings run on a shared object-storage model, similar to what was described earlier, where compute and storage stay cleanly separated. Others use a cache-mesh approach that deliberately keeps hot, frequently queried data in memory and off the object storage path. That distinction matters more than the price sheet does, because a fast p50 with an ugly tail latency is different from consistent performance across the board.

Cost comparisons against other major data warehouse platforms tend to favor columnar, compression-heavy engines for latency-sensitive workloads at scale, roughly a 4x total cost of ownership advantage, with the performance edge most relevant for teams processing more than 50 billion events a month, according to available research. Capacity commitments on Snowflake can bring costs down 15% to 40%, but cold-start delays and continuous replication billing on those platforms work against exactly the kind of low-latency guarantee a fraud SLA depends on.

This entire architecture is overkill for small datasets. Anything under 100 gigabytes probably doesn't justify the operational overhead this class of system demands. A 50 gigabyte fraud dataset is a fundamentally different evaluation than the one this piece is describing, and pretending otherwise wastes engineering time that could go somewhere more useful.

ML model integration, geospatial signals, and the limits of pure rule-based scoring

Rule-based scoring, velocity thresholds, multi-country checks, device switching, catches the fraud patterns that are already known and well understood. What it can't do is catch the pattern nobody's written a rule for yet. That's where machine learning earns its place alongside rules rather than instead of them.

One practical pattern: run an IsolationForest model through a user-defined function that pulls the last 30 minutes of transaction data, up to around 10,000 rows, straight from the database. From there, the model engineers a handful of features, transaction amount, the log of the amount (which helps normalize for the wide range of transaction sizes), and per-user historical averages, among others, to score how anomalous a given transaction looks relative to that account's normal behavior.

That's a genuinely different kind of signal than a velocity counter or a country-mismatch flag. A velocity rule catches an account that suddenly makes 30 transactions in an hour. It won't catch an account making five transactions that are individually unremarkable but collectively don't match that user's established pattern, a slightly different amount, a slightly different time of day, a merchant category the account has never touched before. That's the gap ML closes.

Neither approach replaces the other. Rules are fast, explainable, and easy to audit, which matters enormously in fraud, where a declined transaction sometimes needs a clear justification. ML models catch what rules miss, but they're harder to explain and need retraining as fraud patterns shift over time. A fraud engine built on just one of these is incomplete by design. The strongest architecture treats the database as the substrate underneath both: fast enough to feed velocity rules in milliseconds, and fast enough to hand a model the recent data it needs to score an anomaly before the transaction window closes.