Almost every data platform has two halves: the databases that run the business and the systems that analyse it. This page explains why those halves need different storage layouts and schema designs, and how data moves from one to the other.
OLTP (online transaction processing) is the database work behind an application: placing an order, debiting an account, updating a profile. Each operation touches a handful of rows, must finish in milliseconds, and must be correct even when thousands of users write at once. OLAP (online analytical processing) is the work behind reports and dashboards: total revenue by region for the last three years, churn by cohort, average basket size by weekday. Each query reads millions or billions of rows but only a few columns, and it is acceptable for it to take seconds.
The left panel shows the OLTP pattern: point lookups and single-row writes keyed by a primary key, arriving concurrently. The right panel shows the OLAP pattern: one query that scans a large slice of history and aggregates it. The two patterns pull storage design in opposite directions, which is why most organisations run them on different systems and move data between them.
Running heavy analytics directly on the production database is the classic mistake. A long aggregate scan competes with checkout traffic for CPU, memory and I/O, holds snapshots that bloat MVCC storage, and can slow the application for every customer. In the other direction, an analytical warehouse is a poor home for an application: per-row updates are expensive, and many warehouses offer only limited enforcement of primary and foreign keys.
Understanding the split tells you where each new requirement belongs. "Show the customer their last five orders" is OLTP. "Show the finance team monthly revenue by product line" is OLAP. "Recommend products on the checkout page using last week's behaviour" is a hybrid: computed analytically, served transactionally.
A row store writes each record contiguously, so fetching or updating one order touches one page. That is ideal for OLTP. A column store writes each column contiguously, so a query that sums amount reads only that column and skips the rest. With 50 columns and a query that needs 2, a column store reads roughly 2/50 of the bytes a row store would, before compression.
Compression amplifies the gap. Values within a single column share a type and often repeat (regions, statuses, dates), so run-length and dictionary encoding shrink them dramatically. Column engines also process batches of values with vectorised CPU instructions, and keep min/max statistics per block so whole blocks can be skipped when a filter rules them out. The cost is that inserting one row means touching every column file, which is why column stores prefer bulk loads and batch micro-files.
OLTP schemas are usually normalized to third normal form (3NF): each fact lives in exactly one place, so an update cannot leave copies inconsistent. The price is joins; reconstructing "revenue by country" walks from order lines to orders to customers to cities to countries.
OLAP schemas are usually denormalized into a star schema: a central fact table of measurements (quantity, revenue) surrounded by wide dimension tables (date, customer, product) that already contain every descriptive attribute. Redundancy is acceptable because the warehouse is loaded by a controlled pipeline, not edited by users, and it buys simpler SQL and fewer joins. Some warehouses go further and flatten everything into one wide table, which works well on columnar engines because unused columns cost nothing to skip.
The script below uses SQLite for the transactional side and DuckDB for the analytical side, both installable with pip install duckdb (SQLite ships with Python). It writes orders the OLTP way, one transaction at a time, then copies them into a columnar store and runs an aggregate.
import sqlite3, random, duckdb
# --- OLTP side: normalized tables, row-at-a-time writes ---
oltp = sqlite3.connect(":memory:")
oltp.executescript("""
CREATE TABLE customers (id INTEGER PRIMARY KEY, name TEXT, region TEXT);
CREATE TABLE orders (id INTEGER PRIMARY KEY, customer_id INTEGER REFERENCES customers(id),
amount REAL, ts TEXT);
""")
regions = ["West", "East", "North", "South"]
oltp.executemany("INSERT INTO customers VALUES (?,?,?)",
[(i, f"cust{i}", random.choice(regions)) for i in range(1000)])
for i in range(50_000): # each order is its own small transaction
with oltp:
oltp.execute("INSERT INTO orders VALUES (?,?,?,date('2025-01-01', ?))",
(i, random.randrange(1000), round(random.uniform(5, 500), 2),
f"+{random.randrange(365)} days"))
# point lookup: the OLTP access pattern, served by the primary-key index
print(oltp.execute("SELECT * FROM orders WHERE id = 4242").fetchone())
# --- OLAP side: denormalize once, store by column, scan and aggregate ---
rows = oltp.execute("""SELECT o.id, o.ts, o.amount, c.region
FROM orders o JOIN customers c ON c.id = o.customer_id""").fetchall()
olap = duckdb.connect()
olap.execute("CREATE TABLE fact_sales (order_id INT, ts DATE, amount DOUBLE, region VARCHAR)")
olap.executemany("INSERT INTO fact_sales VALUES (?,?,?,?)", rows) # use COPY/Parquet at scale
print(olap.sql("""
SELECT region, date_trunc('month', ts) AS month, SUM(amount) AS revenue
FROM fact_sales GROUP BY ALL ORDER BY month, region LIMIT 8
"""))Notice the shape of each query. The SQLite lookup uses the primary key and returns one row. The DuckDB query touches three of four columns for every row and returns a small summary. In production the copy step would be a CDC stream or a scheduled ELT job writing Parquet, rather than an in-memory transfer.
| Dimension | OLTP | OLAP | HTAP (hybrid) |
|---|---|---|---|
| Typical query | Point read/write by key | Scan + aggregate over history | Both, on one copy of data |
| Storage layout | Row-oriented, B-tree indexes | Column-oriented, block statistics | Row store plus column replica |
| Schema | Normalized (3NF) | Star / snowflake / wide tables | Usually normalized |
| Concurrency | Thousands of short transactions | Tens of long queries | Mixed, with resource isolation |
| Data freshness | Current state | Minutes to a day behind | Near real time |
| Examples | PostgreSQL, MySQL, Oracle, SQL Server | Snowflake, BigQuery, Redshift, ClickHouse, DuckDB | TiDB, SingleStore, SQL Server columnstore indexes |
HTAP systems keep a row store for writes and maintain a columnar replica for analytics, reducing the pipeline between them. They are attractive when freshness matters, but they do not remove the need for historical modelling, conformed dimensions, or data from other systems, so most organisations still run a separate warehouse or lakehouse.
UPDATE and DELETE on a column store is slow and creates many small files. Batch changes and use MERGE.SELECT * on a column store. It forfeits the main advantage of columnar storage and inflates cost on pay-per-byte engines.