OLAP Database Selection for Risk and Fraud Detection Engines

A transaction hits the model serving layer, a set of queries fires against the database, and a decision has to come back before the payment window closes. That round trip is the whole job. Fraud detection software works by pulling in signals from transactions, devices, identities, and open-source intelligence, then scoring each event with machine learning models while the transaction is still live. The database sits inside that loop, not beside it. The actual query shape that produces a fraud model is plain: it generates queries close to a SELECT COUNT()/SUM() FROM a table GROUP BY a few columns. Nothing exotic about the SQL. What makes it hard is everything wrapped around it: single-digit millisecond responses, data that can't be more than a minute old, and a system that never gets a quiet period to catch up.

That combination separates a risk engine from a dashboard. A fraud engine can't, because the same database has to serve two different audiences at once. One is a machine: a model serving layer firing pre-defined queries and needing an answer in single-digit milliseconds. The other is a person: an investigator running a slower, open-ended query to understand a pattern after the fact. Most OLAP systems are built with one of those two users in mind, not both. That mismatch is the starting condition for everything that follows, and it's why picking an OLAP engine for a risk system can't be done the way teams normally shop for a database.

The four hard constraints, ordered by how quickly they eliminate candidates

Four constraints decide whether an OLAP engine is even a candidate for a fraud or risk workload: query latency, data freshness, ingestion throughput, and auditability. They're ordered here by how much room each one leaves for a clever workaround, and the order matters, because the first constraint eliminates candidates before the others even get a chance to apply.

Query latency comes first because it has zero tolerance built in. The machine-interaction path needs responses in the single digits to sub-100 milliseconds, measured at p99, not at the median. A system posting 20 milliseconds on average but spiking to 800 milliseconds under concurrent load has failed the constraint, full stop on the math: the slow tail is what blocks a real transaction. That bar by itself rules out any architecture where hot-path queries round-trip through object storage, since an S3 GET's latency profile simply doesn't fit inside that window at production scale. Druid's whole reason for existing is sub-second query latency at scale, and even that system strained against the requirement, which says something about how tight the bound really is.

Data freshness comes second. It comes in bursts, often driven by scripts, and a signal that's two minutes old by the time the model sees it is a signal the model effectively never saw. That rules out any architecture where data sits in a batch-compaction queue before it becomes queryable, and it narrows the field to engines built around continuous, streaming-native ingestion.

Ingestion throughput under concurrent query load is the third constraint, and it catches architectures that pass the first two tests in isolation but fail when writes and reads compete for the same resources. Observability pipelines offer a useful reference point for the kind of scale this constraint implies: production systems handling hundreds of millions of events a day, serving hundreds to thousands of queries per second, with aggregations running constantly rather than occasionally.

Auditability is listed fourth, but calling it the least important would be a mistake. It's non-negotiable the moment a regulated industry is involved. Investigators need to see which specific signals drove a decision, both to tune the rules going forward and to defend the outcome to a regulator, and that means the database has to log and reproduce every query that contributed to a risk score. This isn't a report generated after the fact. It has to live at the query-logging level, so any decision the model made can be reconstructed from the exact data state that existed at the moment the query ran. Research on layered fraud pipelines built on the PaySim dataset makes the stakes concrete: a plausible-sounding rationale from an investigation agent isn't the same thing as evidence that a better decision got made. Automated reasoning has to be traceable and open to review. It can't be allowed to certify itself.

Schema design as a latency decision, not a modeling decision

Picking the right engine only partly determines latency. The rest gets decided at schema design time, before a single production query runs. In columnar engines that store data by sort key, the sort order functions as the main latency control, because queries use that order to decide which blocks of data they can skip entirely. Every later optimization becomes marginal against a foundational mistake.

A workable fraud schema makes this concrete. The low-cardinality encoding matters for a specific reason: categorical fields stored as plain strings bloat the column and work against compression, while encoding them as low-cardinality types shrinks the blocks that have to be read and decompressed on every query. Smaller blocks mean less work per query, and less work per query is most of what single-digit millisecond responses are built on.

Materialized views carry a similar weight. Pre-aggregating high-cardinality metrics through a materialized view is the mechanism that lets machine-interaction queries, which tend to repeat the same shape over and over, hit single-digit millisecond responses even against large volumes of data, catching patterns like a sudden spike in failed payments, a cluster of duplicated logins, or a mismatch between a transaction's claimed location and its actual one immediately.

Teams under deadline pressure often defer this work, get data flowing first, and plan to optimize the schema once things are running. Getting the schema wrong raises costs later, when the system requires a rebuild rather than a small correction.

There's a second schema problem that has nothing to do with speed and everything to do with completeness. Legacy fraud systems tend to keep each product line in its own data silo. A modern approach needs to pull data from every product line into one view of an entity's risk. The schema has to support joins across product lines, not just aggregation within a single stream. A schema tuned only for speed on one stream of events will eventually hit the same wall that siloed legacy systems hit: a complete enough view of risk requires joining across tables, and that need has to be designed in from the start.

Latency and cost limits of object-storage-routed architectures

Routing production fraud queries through object storage tends to fail two constraints at the same time, and the two failures reinforce each other. Latency gets worse right when query volume peaks, and cost climbs in step with query volume instead of staying flat against it. That's a bad pairing for a workload that runs continuously and spikes unpredictably. S3 GET costs at scale are not a rounding error.

The cost side is bigger than people expect going in. At the query rates a fraud engine produces, those limits stop being theoretical and start being an operational risk that can actually interrupt service.

AWS S3 Tables offers a useful data point on what S3-backed architectures tend to miss. The design doesn't give writers a way to influence compaction controls to match the actual workload, which is a direct problem for any ingestion-heavy fraud pipeline where new data needs to become queryable within seconds rather than waiting on a compaction cycle. Teams trying to work around this end up adding batching layers and separate support databases just to manage S3 cost and atomicity, which adds real architectural complexity to what should have stayed a straightforward ingestion-and-query pipeline.

The pricing models at the warehouse layer compound the problem rather than fixing it. Snowflake's credit-based pricing and BigQuery's per-TiB scan pricing both charge in ways that penalize short, repeated, wide-scan queries. Neither pricing model is poorly designed for its intended purpose. The mismatch is that fraud query patterns and object-storage-centric pricing structures pull in opposite directions.

The way around this isn't to abandon object storage, since the durability and cost profile of S3 are still worth having. The fix is separating durability from query performance: keep S3 for object persistence, and put an NVMe or in-memory cache layer in front of it that keeps the hot data off the S3 read path entirely. That split preserves the cost advantage of object storage without ever putting a live fraud query at the mercy of an S3 GET's latency.

Apache Druid and Apache Pinot strengths

Apache Druid and Apache Pinot earn their place in this conversation under a specific set of conditions: when freshness, segment isolation, and millisecond-level dashboard response times are the priority over broad, ad-hoc SQL support. Under those conditions, both systems carry architectural advantages that a general-purpose columnar engine doesn't offer out of the box.

Druid's segment architecture is the clearest example. Druid ingests straight from Kafka and makes new segments queryable in near-real-time, with no wait for a compaction cycle before the data can be queried. WePay's engineers were told Druid could hit their latency target, but only with a sizable number of machines, and the size of that cluster scales directly with how aggressive the latency SLO is. Pinot, by contrast, provides pluggable indexing tailored to specific query patterns, a flexibility Druid does not offer.

Both systems ask something of the team running them. They demand real operational expertise, and their SQL support doesn't stretch as wide as a general-purpose columnar engine's does. The assumption both systems lean on, that most queries hit known dimensions with known aggregations, carries more weight in Druid and Pinot than it does in engines built for open-ended SQL. That's not a flaw so much as the price of the speed they deliver. A team whose queries are genuinely unpredictable will fight that assumption constantly.

A third path exists for teams whose query shapes are fully known ahead of time and whose data already lives in streaming infrastructure: a stream-processing system built around materialized view semantics. Ramp uses Materialize for exactly this kind of fraud detection work, and while it's not a drop-in replacement for Druid or Pinot, it's a legitimate option worth having on the list when the workload fits that narrow, predefined shape.

Graph features and investigative analytics beyond the transaction stream

Everything so far has been built around one job: catching a fraudulent transaction while it's still happening. That's a different job from tracing the network behind it, and most OLAP selection processes optimize hard for the first while giving almost no thought to the second. A complete risk engine needs to do both, and the two jobs want different things from the database.

In one documented case, investigators using graph analytics tools unraveled a $5.7 million fraud ring in two hours, a task that would have taken weeks using traditional methods. That speed only happens if the database underneath the investigation supports multi-hop graph traversal and cross-account joins, not just the single-stream aggregation that handles the real-time scoring side.

The data this layer needs to touch is also wider than a single transaction stream. Fraud rings take advantage of exactly the silos that keep those datasets apart. A synthetic identity might get used to open a personal loan successfully, and the fraud is noticed months later when the credit card division flags something unrelated. Closing that gap means the database architecture has to support joins across product lines without requiring a full data warehouse rebuild every time.

Whether graph features actually add detection value, separate from being a nice visualization for investigators, matters for deciding how much weight to put on them. Graph features didn't move the needle on Average Precision across the full test set, but they weren't decorative either. They carried real detection value precisely for the fraud ring cases that matter most, the ones a single-transaction model structurally can't see.

That leaves the architecture with a clear requirement. The OLAP layer serving investigators has to handle wide joins across high-cardinality entity tables, return results in a timeframe measured in seconds rather than milliseconds, and support ad-hoc queries whose exact shape nobody can predict at schema design time. There's no single right answer. The right one depends on how much the organization is willing to operate two systems versus how much latency and flexibility it's willing to trade away to keep one.

Why model drift and governance

A risk model that scored well at launch doesn't stay accurate on its own. Fraud patterns shift as fast as the people committing fraud adapt to whatever the model learned to catch, and a model trained on last quarter's behavior will quietly misjudge this quarter's. That drift is a database problem as much as a modeling problem, because catching it requires comparing live scoring behavior against the data the model was trained on, continuously, not as a quarterly audit.

Governance follows the same logic laid out under auditability earlier. A model decision has to be reconstructable from the exact data state that existed at the moment the query ran. That requirement doesn't ease up once a model goes into production. The database that can't hold onto that history, or can't reproduce a query against a past data state, leaves a gap that no amount of model accuracy can close.

The constraints covered across this piece, latency, freshness, throughput, auditability, schema discipline, and the investigative layer's wider reach, all converge on the same underlying demand: a risk engine's database has to be precise about what it knew and when it knew it. That's a harder bar than speed alone, and it's the one that separates a system built to pass an audit from one that merely happens to run fast.