OLAP vs OLTP Schema Design Decisions

A dashboard query ran for 47 seconds one afternoon and paged an on-call engineer out of a meeting. The schema underneath it was perfectly normalized, textbook-correct for the transactional database it was built for, and that correctness is what made the dashboard crawl.

Why OLTP and OLAP schemas are structurally opposed

The term OLAP comes from a 1993 paper by E.F. An order gets inserted, a status gets updated, inventory gets decremented, thousands of times a second, each change wrapped in ACID guarantees and expected back in milliseconds. OLAP systems exist to answer questions about the history that all that mutation leaves behind: revenue by region, retention curves, loss ratios across policy cohorts, the kind of query that scans millions of rows to produce a single number.

The gap between those two jobs isn't about which system is faster or which handles more data. An OLTP system touches one row, or a small cluster of related rows, and changes it. An OLAP system touches one or two columns and reads them across an enormous range of rows. A transactional database asked to do the second job produces the 47-second query. Neither failure is a bug. Both are the system behaving exactly as designed, just for the wrong job.

How row-oriented and columnar storage make each schema optimal for one workload

The reason schema rules diverge so sharply traces back to how the bytes sit on disk. Reading one row returns the whole entity in a single pass. That's what most transactions want: pull up an order, grab every field attached to it, done. It's the OLTP happy path.

That same layout, pointed at an analytical question, becomes the worst case on the shelf. An aggregation that only needs 3 of 8 columns still has to walk every row and pull all 8 columns off disk, because that's how row storage groups data. That's a lot more I/O than an analytical query actually needs, and compression hasn't even entered the picture yet.

Columnar storage flips the arrangement. Columns are stored together instead of rows, so a query touching 3 of 50 columns reads only those 3. Columnar engines like ObsessionDB are purpose-built for exactly this kind of workload, where a query touches a fraction of the columns across millions of rows and needs the vectorized execution that row-oriented systems can't match.

Running the comparison in the other direction makes columnar storage the OLTP worst case. Each engine is, in practice, bad at the other's job in a specific and predictable way. The layout that makes one operation cheap makes the other expensive, and no amount of configuration changes which bytes sit next to which on disk, so no faster disk or smarter index closes that gap. Every schema rule that follows, in both directions, is downstream of that one physical fact.

Why OLTP schemas normalize to third normal form

Third Normal Form says any given fact lives in exactly one table, referenced everywhere else by a foreign key. A customer's address gets stored once and pointed to from wherever it's needed. That rule isn't a style preference handed down by database theory for its own sake. It protects write correctness in a system where thousands of concurrent transactions are changing rows every second.

Picture an insurance system that stores agent_name directly on a policies table instead of normalizing it out. ACID guarantees enforce this at the transaction level: an operation either commits fully or doesn't happen at all, because the transactional database is the system of record and can't afford to land in a half-finished state.

Index strategy follows the same logic. The query optimizer's job in this world is to keep execution plans stable under heavy concurrent load, so the same lookup behaves the same way regardless of how many other transactions are running at the same moment.

Normalization also keeps the working set small. The cost is small, predictable, and nothing like what happens when the same join pattern gets asked to do analytical work.

Why OLAP schemas denormalize by default

The correct schema flips when the workload flips. That changes the math entirely: instead of normalizing to avoid duplication, the analytical schema denormalizes on purpose to avoid joins.

Columnar compression is what makes the duplication affordable. A normalized schema turns every analytical query into exactly that kind of multi-way join, and it degrades as the data grows.

The star schema is the standard answer. That extreme works well when dimension cardinality is low and those dimensions rarely change.

None of this means normalization has no place in analytics. The real decision facing a data team is where to draw the line between normalizing and denormalizing, weighing storage cost, query cost, update cost, and how clear the resulting schema stays for the next engineer who has to read it. OLAP schemas also tend to use surrogate keys, synthetic integers on dimension tables rather than natural business keys, to keep join columns narrow and compression high, another place where the analytical convention runs opposite to transactional practice.

The obvious objection: doesn't denormalizing make updates expensive? New state gets appended and re-derived instead of overwritten, and change data capture from the transactional source handles keeping that source-of-truth value current, a pattern covered in more detail later.

Star schema vs. snowflake schema in columnar engines: where further normalization of dimensions still costs

A natural question follows: if denormalizing the fact table helps, why not normalize the dimension tables a bit further? That's the snowflake schema, where a dimension hierarchy like product → subcategory → category → department gets split into separate tables joined at query time. In a columnar engine, it costs more than it saves.

Dimension tables are already small compared to the fact table they surround, and columnar compression already handles repeated values aggressively on its own. A star schema turns an analytical query into a scan with filters, the exact shape columnar engines are built to execute well. A snowflake schema turns the same query into a chain of joins, the exact shape that degrades as row counts climb.

That gap is visible directly in BI tooling: dashboards built on star schemas load measurably faster than equivalent dashboards built on snowflake schemas, because both DirectQuery and Import mode benefit from having fewer joins to resolve. Some engines remove the choice. Engines that don't support row-level UPDATE or DELETE, or that have limited JOIN support, require all the data a query needs to already be joined together at ingest time; the snowflake option isn't really available, and the schema has to be denormalized before it ever lands.

What write-optimized joins in OLTP look like

Looking closely at the transactional side shows what a join actually looks like there. A typical OLTP join is a primary key lookup on one table paired with a foreign key lookup on another, two indexed seeks returning a handful of rows in well under a millisecond. The optimizer keeps the plan stable, the data is hot in the buffer pool, and the cost stays small and predictable no matter how many times that join runs per second.

Pointing an analytical query at that same normalized schema collapses the pattern. The index overhead adds write cost without buying back any of that cost on the read side at scan scale. Analytical engines can't use OLTP-style indexes effectively for this reason, and their query planners, built around distributed scan execution, were never designed to run a transactional workload either; drop one into that role and it would cripple just as fast.

Concurrency makes it worse. The 47-second dashboard query from the opening is the predictable outcome of running an analytical pattern against a system tuned for transactional concurrency. No index, no query hint, and no amount of tuning changes that outcome, because the mismatch starts in the storage architecture, well before any particular query gets written. If the two workloads are genuinely incompatible on one system, the answer is to run two systems and keep them in sync.

How the CDC-driven dual-engine architecture resolves the opposition

Given everything above, it would be reasonable to ask whether some single system could just do both jobs well. Production systems answer that question fairly consistently: the industry's preference is to keep OLTP and OLAP as separate, purpose-built engines and close the gap between them with change data capture, rather than trying to merge the two into one.

CDC works by tailing the transactional database's write-ahead log and streaming every insert, update, and delete into the analytical store within seconds. The transactional system never slows down to serve an analytical scan, and the analytical system never gets asked to perform a point update.

The schema transformation that resolves the whole opposition happens right at that CDC boundary. Rows leave the OLTP source in normalized, 3NF form and arrive at the analytical store denormalized, pre-joined, or shaped into star schema form. The conflict between normalization and denormalization gets settled during ingest, not argued over again at query time. Each engine keeps doing the one job it was built for: row storage for concurrent mutation, columnar storage for scan-heavy analysis. That division reflects a deeper point about database architecture: systems built for mutation and systems built for analysis at scale make opposite tradeoffs at every layer, from storage format to query planning to how they're priced, and the industry's revealed preference going into 2026 is still to pair a genuine OLTP engine with a genuine OLAP engine rather than ask one system to be both.

HTAP engines, which promise to handle transactional and analytical workloads on one system, are a real category. The live question for a team is which replication pattern fits the specific workload in front of them, not "OLTP or OLAP.

Those replication decisions carry real schema consequences once they reach a columnar engine built for sub-second query latency at high concurrency. Wide, pre-joined tables, careful selection of sort keys, and denormalization happening at ingest time rather than query time are the specific demands that fall out of this architecture, and they differ from both the normalized OLTP schema upstream and the looser star-schema conventions common in general-purpose data warehouses. Materialized views are one way engines encode that decision for recurring query patterns: instead of re-joining or re-aggregating every time a query runs, the engine maintains a pre-computed result that updates as new data arrives, so the schema decision gets made once and reused across every query that follows. A real-time analytics platform running on a managed service built around columnar storage is one place this whole chain of tradeoffs gets handled concretely, with schema rules built around columnar storage as the foundation rather than bolted on afterward.

ObsessionDB's architecture is a specific example of how those decisions get implemented operationally. ObsessionDB is built around workloads like real-time analytics platforms, observability pipelines, risk engines, and AI agents that query data relentlessly and in parallel, and the schema decisions described throughout this piece, wide tables, sort keys, ingest-time denormalization, are the ones its engineers support and benchmark directly with customers. Teams can also describe, validate, and apply schema migrations and run ingestion pipelines as code through a companion tooling layer, which turns schema decisions into versioned, reproducible work rather than something documented after the fact and hoped to stay accurate.

A decision framework for engineers choosing schema patterns across the OLTP-OLAP boundary

Everything above reduces to three questions, and the answers to those questions point to the right schema pattern without needing a universal rule to memorize.

First: is the primary operation mutating current state, or querying historical state? If the answer is genuinely both, the right move is running two engines and replicating between them with CDC, not trying to force one engine to cover both jobs at scale.

Second: how often do dimension values actually change, and what does it cost to propagate that change everywhere it needs to land? Data that updates rarely or only gets appended to is a case for denormalizing aggressively, since the redundancy costs little and the savings at query time are large.

Third: what's the dominant query shape, a point lookup by key, or a scan-and-aggregate across a large range of rows? And when both shapes show up at once, user-facing analytics that also need to handle OLTP-level concurrency, the answer is a real-time OLAP engine built for sub-second aggregation under heavy concurrent load, fed continuously by CDC from the transactional source.

Where the line sits between the normalized staging layer and the denormalized analytical layer is itself a schema decision, shaped by update cost, query cost, and how clear the resulting design stays over time. None of these three questions are independent of what's been built up through this piece. Each one is a direct application of a principle developed section by section: storage layout dictates what's cheap to read, write-correctness dictates what must stay normalized, and join cost dictates what should be pre-joined before a query ever runs. The opposition between OLTP and OLAP schema design is a reflection of how storage and computation physically work, not a problem the industry is on its way to solving, and the job for anyone designing these systems is to understand that opposition clearly enough to build around it on purpose.