When to Route Real-Time Event Data Away From OLTP Into OLAP

Routing real-time event data from OLTP into OLAP is a decision about workload shape. The crossover comes when broad scans, multi-dimensional aggregations, and concurrent analytical queries compete with transactional work. Query plans reveal the mismatch, operational metrics confirm it, and routing will solve it only if the destination meets the workload’s freshness, isolation, and recovery requirements.

Why OLTP's consistency-first design becomes a liability for analytical workloads

OLTP prioritizes low latency, data consistency, and high write throughput for individual transactions. Those transactions are small, take little time, and touch little data, so row-based storage works well for them. Retrieving or updating a record is the central operation. Analytical queries ask the storage engine to read across many records to work out a result. The tension starts with that change in access pattern.

For example, aggregating one metric across millions of records makes a row store load columns that the calculation does not need. That extra I/O competes with the work required to serve individual transactions. Columnar storage instead scans the columns the query needs, while pre-aggregation, massively parallel processing, and query optimization help it do the work in the scan-and-aggregate pattern. ScyllaDB’s workload coexistence analysis shows the consequence of leaving both patterns together, with analytical work consuming throughput that the real-time workload depends on and pushing transactional latency up.

How mixed workloads degrade in practice: contention, throughput collapse, and latency drift

ScyllaDB’s monitoring trace shows real-time latency climbing sharply when an analytics job starts, while OLTP throughput visibly falls. Requests then accumulate because the transactional path cannot process them at its previous rate. The backlog clears only after the analytics job finishes.

The mechanism is straightforward when requests share resources without isolation. Analytical jobs consume much more throughput than short transactional operations, but arrival-order processing does not distinguish their priority. A scan can therefore starve transactional reads and writes even when no single query looks unreasonable on its own. In ScyllaDB’s food-delivery example, the ordinary reporting question of how much a merchant sold this week takes a full table scan in a wide-column OLTP store, whose resource demands conflict with concurrent transactional traffic.

Physical isolation and scheduled isolation can soften that conflict. ScyllaDB identifies separate clusters as a significantly more expensive option, while off-peak scheduling postpones the problem until analytics must use fresher data. With either approach, the analytical query is still a poor fit for transactional storage. For a customer-facing product, the constraint is tighter still because sub-second responses must hold under concurrent use. That makes the overlap between scans and transactions as important as the time needed to complete either workload alone.

The four query-pattern signals that mark the architectural crossover point

The first check is whether slow queries scan across time. A month of events or a trend over a quarter can require touching a large fraction of stored rows. An index does not remove the cost of reading those rows when the requested window includes them. In query plans, the warning is the breadth of the read needed to answer the question. A read that broad points to analytical work even when the result has very little data.

The second check looks at whether those queries also aggregate across more than one dimension. A GROUP BY over region, product, and time asks the engine to organize the same records in several ways at once. Columnar storage and pre-aggregation suit this work, whereas row storage reads and discards columns outside the calculation. The combination of time-range scanning and multi-dimensional aggregation is more telling than either pattern alone. It shows that the workload repeatedly needs the access pattern OLAP was built to serve.

The third check looks at whether several users or processes run those queries at the same time. Long analytical queries hold connections open and can add to lock waits in systems built for short transactions. Reporting windows that coincide with connection pool exhaustion or growing lock waits make this signal visible in dashboards. Transactional p99 latency pulling away from p50 provides another clue. Concurrent queries can consume excessive resources even without one exceptionally large query.

The fourth check is whether the underlying records are immutable events, including logs, telemetry, clickstream, and audit records. Data written once and never updated does not need OLTP’s mutation-consistency guarantees in the same way as changing transactional records. Real-time and batch ingestion make that append pattern a natural fit for OLAP. This signal may appear before serious contention, when a team notices that its event tables are never updated but are increasingly slow to query. Treat one signal as a warning and two or more together as the architectural crossover, then use operational metrics to establish its present cost.

Contention indicators and latency thresholds that confirm the crossover has arrived

The first threshold to instrument is the gap between transactional p50 and p99 latency during recurring analytics windows. If p99 is more than an order of magnitude above p50, the workloads are contending. A fast median can otherwise hide the small share of requests that wait several seconds. ScyllaDB’s monitoring trace shows a sharp p99 rise tied to the analytical job’s start and finish.

Alert on transactional throughput falling during analytics windows, because that decline means analytical work is reducing transactional serving capacity. Monitor connection wait time and connection refused errors that appear during those windows but not otherwise. Long-running queries can take up all the connections in a bounded connection pool, leaving none for transactional requests. When lock waits rise during reporting jobs, they add to the evidence for the same cause, so each delayed request does not need its own explanation.

The final threshold is the product’s own interactive latency requirement. Queries expected to finish in under a second but taking tens of seconds, or failing to complete, have crossed that boundary. Read this alongside the query-pattern checks rather than treating every timeout as a routing problem. When scan-heavy queries repeatedly miss the SLA while transactional throughput falls and tail latency rises, the mismatch is measurable.

Where HTAP architectures defer the routing decision — and where they do not

HTAP can help when getting current transactional data into an analytical store is the main hurdle. By replicating live OLTP data into a columnar store in near-real time, it can remove the separate ETL pipeline. This addresses freshness and simplifies access to data that must still support mutations. TiDB with TiFlash is an example of this approach, with no ETL required and moderate OLAP concurrency. It fits teams that value operational simplicity over maximum analytical throughput.

However, freshness does not settle the scan-throughput or concurrency question. The earlier signals can reappear as limits on the columnar replica rather than on the row store, with less tuning surface than a purpose-built OLAP engine. HTAP fits strong mutation requirements, tight freshness needs, and moderate analytical complexity. When there are many events, analytical concurrency is high, or queries need more work, a dedicated OLAP layer may still be needed for its fuller optimization stack.

How agentic workloads amplify every OLTP routing signal

A dashboard user typically runs known queries at predictable intervals, while an agent first probes the schema and then formulates SQL dynamically. That probing can include broad scans and metadata reads before the main data query begins. An agent may then issue dozens of queries in parallel to explore a hypothesis. Those parallel queries put earlier pressure on the connection pools and shared resources identified by the concurrency signal.

This changes the economics of the analytical destination as well as its load. Per-query pricing charges directly for the exploration that makes agents useful. Compute-hour pricing lets agents probe without accumulating a separate charge for each query, though their work still requires capacity. The relevant comparison is therefore the cost of the expected exploration pattern. Agentic workloads make concurrency a routine requirement.

Autonomous access also makes accountability part of the routing decision. Every agent query against production data should be logged in full, and agents should be read-only and use isolated compute. Sandboxed analytical compute lets them explore real data without sharing the transactional path. Full logging provides the record needed to account for that exploration.

What the routing decision requires from the OLAP layer: freshness, concurrency, and recovery posture

The destination must first meet the product’s freshness SLA, measured from event generation to query availability. For customer-facing analytics, that generally means seconds to low minutes. Support for real-time ingestion matters only if the chosen pipeline delivers that gap. Snowflake’s OLAP overview describes hourly-to-daily updates as an option depending on organizational need. That range can work for internal BI but fall short of what an interactive product needs.

Concurrency must be specified with isolation. Customer-facing and multi-tenant products need analytical work from one tenant not to disrupt another. Without isolation, a shared OLAP cluster can have the same contention problem at a different scale. Layers SaaS kept costs from rising as much as projected by giving each tenant its own isolated analytical environment. The example makes isolation an economic requirement as well as a performance one.

Recovery requirements depend on whether the analytical store is a copy or the authoritative record. Reloading from source can be sufficient for internal BI, but it is not a complete recovery plan when the OLAP layer holds billing, compliance, or audit records. Append-only data still needs explicit snapshot and retention policies. Enterprise expectations as of 2026 include application-consistent snapshots, point-in-time recovery, and near-zero RPO. Those capabilities must be designed into the OLAP deployment.

The hot query path also needs scrutiny. Architectures that route every read through S3 add object-storage latency that compounds under analytical concurrency. For interactive SLAs, check whether hot data stays in memory and whether p99 latency stays flat as the amount of data grows. This is a specific architectural check rather than a general preference about managed services. A destination that moves contention away from OLTP but introduces an unacceptable read path has not met the requirement.

Finally, routing must be sustainable as event schemas change. Ad-hoc changes can cause ingestion failures and data quality problems in analytical pipelines. Event schemas therefore need to be versioned, validated, and deployable as code. Structured tooling should cover both schemas and pipelines, so changes are managed as operational work.

A decision checklist: applying the signal framework to a real workload

Start by pulling the slowest OLTP queries and checking them against the four workload signals. Identify time-range filters, multi-column GROUP BY, broad scans, concurrent execution, and immutable event tables. If two or more signals apply, the query shape points toward OLAP. Then check how transactional p50 and p99, throughput, connection waits, and lock waits line up with the analytics windows. A recurring order-of-magnitude latency gap, throughput drop, or pool saturation confirms active contention.

Next, account for AI agents and automated pipelines in the expected concurrency profile. Use read-only isolation and full query logging when agents read production data. Evaluate the destination against freshness, tenant isolation, recovery, hot-data placement, and schema tooling requirements. Verify that its architecture avoids routing every query through S3 and keeps hot data in memory.

Finally, benchmark the destination on the same query shapes and concurrency load the product will generate. Verify that latency stays flat under that load, validate schemas, and plan the handoff. Treat the move as an architectural migration with operational requirements. The routing decision identifies where the workload belongs. Running it there reliably requires the migration project.