Why OLTP and OLAP Differ: Understanding Data Warehouses and Star Schemas

Early databases linked writes to commercial transactions such as sales or orders. Although the term "transaction" persisted, it now denotes a logical unit of read‑write operations, not necessarily ACID‑compliant.

Transactional workloads (OLTP) involve low‑latency reads and writes of a few records identified by a key, typical for interactive applications.

Analytical workloads (OLAP) scan large volumes of records, read only a few columns, and compute aggregates like counts, sums, or averages to support business‑intelligence reporting.

A comparison highlights differences in read/write patterns, data scale, latency requirements, and typical use cases between OLTP and OLAP systems.

Historically a single database could serve both workloads, but since the late 1980s companies began separating analytical processing into dedicated data warehouses, which store read‑only copies of OLTP data.

Data Warehouse

Data warehouses aggregate data from dozens of OLTP systems, providing high availability and low‑latency transaction processing while keeping analytical queries isolated.

The ETL (Extract‑Transform‑Load) process extracts data from source OLTP databases, transforms it into an analysis‑friendly schema, and loads it into the warehouse.

OLTP vs Data Warehouse

Data warehouses are optimized for analytical access patterns, using relational models and SQL interfaces, but differ internally from OLTP databases.

Many commercial databases (e.g., Microsoft SQL Server, SAP HANA) support both workloads, though they increasingly separate storage and query engines.

Star and Snowflake Schemas

Analytical models often use a star schema: a central fact_sales table surrounded by dimension tables such as dim_product and dim_store. Facts capture events; dimensions describe context (product, time, location).

Snowflake schemas further normalize dimensions into sub‑dimensions, but star schemas remain preferred for analyst simplicity.

Typical warehouses have very wide fact tables (hundreds of columns) and dimension tables that store extensive metadata.