OLTP vs OLAP: Transactional vs Analytical Systems

OLTP and OLAP describe two kinds of database work. Online transaction processing runs the business: taking an order, recording a payment, updating an address, thousands of small reads and writes a second, each one correct and immediate. Online analytical processing studies the business: revenue by region by quarter, churn over three years, a few large queries over millions of rows. The same data serves both, but the best structure for one is poor for the other, which is why most organisations keep them apart.

What each is optimised for

Microsoft's architecture guides summarise the traits:

OLTP systems normalise data, splitting it into small, non-redundant tables, which lets them process many transactions independently (Microsoft Learn: OLTP). Analytical stores are built so complex queries run without disrupting transactional systems, keep history, and usually use star or snowflake schemas (Microsoft Learn: OLAP).

The same question, both ways

Chinook is a sample database for a digital music store, normalised in the OLTP style: invoices, invoice lines, tracks, albums, artists, genres and media types in separate tables (Chinook on GitHub). The script asks one analytical question, revenue by genre and country in 2012, of the operational schema, then builds a small star schema from the same data and asks again:

import shutil
import sqlite3
import urllib.request
from pathlib import Path

url = "https://github.com/lerocha/chinook-database/releases/download/v1.4.5/Chinook_Sqlite.sqlite"
if not Path("chinook.sqlite").exists():
    urllib.request.urlretrieve(url, "chinook.sqlite")
shutil.copy("chinook.sqlite", "work.sqlite")                  # leave the download untouched
con = sqlite3.connect("work.sqlite")
tables = [r[0] for r in con.execute("SELECT name FROM sqlite_master WHERE type = 'table' ORDER BY name")]
print(f"OLTP schema: {len(tables)} normalised tables")

# the analytical question against the operational schema
oltp = """
SELECT g.Name AS genre, i.BillingCountry AS country, ROUND(SUM(il.UnitPrice * il.Quantity), 2) AS revenue
FROM InvoiceLine il
JOIN Invoice i ON i.InvoiceId = il.InvoiceId
JOIN Track t   ON t.TrackId = il.TrackId
JOIN Genre g   ON g.GenreId = t.GenreId
WHERE strftime('%Y', i.InvoiceDate) = '2012'
GROUP BY genre, country ORDER BY revenue DESC, genre, country LIMIT 5"""

# a small star schema derived from it: one fact table, denormalised dimensions
con.executescript("""
DROP TABLE IF EXISTS dim_track; DROP TABLE IF EXISTS dim_customer; DROP TABLE IF EXISTS fact_sales;
CREATE TABLE dim_track AS
  SELECT t.TrackId AS track_key, t.Name AS track, al.Title AS album, ar.Name AS artist, g.Name AS genre, m.Name AS media
  FROM Track t JOIN Album al USING (AlbumId) JOIN Artist ar USING (ArtistId)
  JOIN Genre g USING (GenreId) JOIN MediaType m USING (MediaTypeId);
CREATE TABLE dim_customer AS
  SELECT CustomerId AS customer_key, Country AS country, City AS city FROM Customer;
CREATE TABLE fact_sales AS
  SELECT date(i.InvoiceDate) AS date_key, i.CustomerId AS customer_key, il.TrackId AS track_key,
         i.BillingCountry AS billing_country, il.Quantity AS quantity, il.UnitPrice * il.Quantity AS amount
  FROM InvoiceLine il JOIN Invoice i USING (InvoiceId);
""")
olap = """
SELECT d.genre, f.billing_country AS country, ROUND(SUM(f.amount), 2) AS revenue
FROM fact_sales f JOIN dim_track d USING (track_key)
WHERE substr(f.date_key, 1, 4) = '2012'
GROUP BY d.genre, country ORDER BY revenue DESC, d.genre, country LIMIT 5"""

a, b = con.execute(oltp).fetchall(), con.execute(olap).fetchall()
print("joins in the query: OLTP", oltp.count("JOIN"), "| star", olap.count("JOIN"))
print("same answer:", a == b)
for row in a:
    print(row)

# the other side of the trade-off: renaming one genre
print("rows to update if 'Rock' is renamed: OLTP",
      con.execute("SELECT COUNT(*) FROM Genre WHERE Name = 'Rock'").fetchone()[0],
      "| star", con.execute("SELECT COUNT(*) FROM dim_track WHERE genre = 'Rock'").fetchone()[0])
con.close()

OLTP schema: 11 normalised tables
joins in the query: OLTP 3 | star 1
same answer: True
('Rock', 'USA', 29.7)
('Latin', 'Brazil', 22.77)
('TV Shows', 'USA', 17.91)
('Metal', 'USA', 14.85)
('Rock', 'Canada', 12.87)
rows to update if 'Rock' is renamed: OLTP 1 | star 1297

What the comparison shows

Analytical questions are simpler on a star. On the normalised schema, the question needs three joins, and it would need more if it grouped by artist or album. On the star, the track dimension already carries genre, album, artist and media type, so the same question needs one join. Both return identical results: denormalising changed the shape, not the answer. For business users writing their own queries, or a BI tool generating them, fewer joins means fewer mistakes and usually faster queries.

Normalisation is what makes writes cheap. Renaming the Rock genre is a one-row update in the operational schema. In the star, "Rock" is copied onto all 1,297 Rock tracks in the dimension, so the same change touches 1,297 rows. That is acceptable in a warehouse, which is reloaded in bulk, and unacceptable in an application database handling thousands of small updates, where redundant copies also risk disagreeing with each other.

The workloads also compete. Microsoft's OLTP guide notes that aggregations over millions of transactions are resource-intensive, can slow other transactions, and that normalised data makes analytical queries complex. Keeping analytics off the operational database protects the application.

Storage and indexing follow the workload

The difference goes below the schema. OLTP databases usually store data row by row and rely on B-tree indexes, so fetching or updating one order touches a handful of pages. That is ideal for "show me invoice 1042" and wasteful for "sum every invoice line in 2012", which must read whole rows to use one column.

Analytical engines usually store data column by column. A query that sums one amount column reads only that column, and values of one type sit together and compress well; Parquet vs ORC vs Avro measures the effect on real files. Instead of many narrow indexes, analytical tables lean on partitioning, sorting and per-block statistics, so whole ranges of data can be skipped. The upshot is simple: tune an OLTP system for the rows it touches, and an OLAP system for the columns and ranges it scans.

How data moves from OLTP to OLAP

The usual path is a pipeline: extract from the operational database, by batch queries or change data capture, load into the warehouse, and transform into fact and dimension tables. How to build a data warehouse walks through the modelling steps, and dimensional modelling explains the design choices. The cost is latency: the analytical copy is as fresh as the last load, which is why the OLAP guide describes analytical stores as refreshing more slowly than transactional ones.

HTAP: doing both in one place

Some workloads need analytics on live data. Hybrid transactional and analytical processing runs both on one platform; Microsoft cites columnstore indexes in SQL Server and Azure SQL Database as one way, avoiding a separate copy of the data. HTAP suits operational reporting on recent data; it does not replace a warehouse that integrates many systems and keeps long history.

Choosing the right side

• Serving an application, such as orders, bookings or accounts: OLTP.
• Dashboards, trends and cross-system analysis: OLAP, fed from the OLTP systems.
• Near-real-time metrics on one system's data: consider HTAP or a read replica before building a pipeline.
• Several systems, long history, shared definitions: a warehouse, because no single operational database holds the whole picture.

Data warehouses explained covers the analytical side in full.

OLTP versus OLAP, data warehouse concepts and star and snowflake schemas are part of the data warehousing module of the Data Engineering course.

Sources

• Online transaction processing (OLTP) (Azure Architecture Center, Microsoft Learn) — Microsoft Learn
• Online analytical processing (OLAP) (Azure Architecture Center, Microsoft Learn) — Microsoft Learn
• Chinook sample database (GitHub) — GitHub