Skip to content
Tutorials Center
My learning

Topics

Lesson 68 of 79 ยท Distributed Databases, NoSQL and Analytics

OLTP vs OLAP, Columnar Storage and Data Warehouses

Compare transactional and analytical workloads, build a star schema, see why column stores compress so well with a live run-length demo, and learn ETL, ELT and data lakes.

A database that runs a shop's checkout and a database that answers "how did sales change by region over five years?" face opposite demands. The first handles thousands of tiny reads and writes per second, each touching a few rows, and every one must be correct right now. The second reads millions of rows to compute a handful of totals, and nobody minds if the data is an hour old. Trying to serve both from one design hurts both, so the industry split them into OLTP (online transaction processing) and OLAP (online analytical processing).

This lesson compares the two workloads, shows the classic analytical schema (the star), explains why column-oriented storage makes analytics fast, and finishes with the pipeline that feeds a warehouse and the newer ideas around it. For the SQL-level view of warehouses see Star Schema and Warehousing. The row layout we compare against was introduced in File Organization.

Two kinds of work

OLTP Many small reads and writes.
OLAP Few huge scans and aggregates.

Different workloads reward different designs.

1 / 2

OLTP versus OLAP

OLTP OLAP
Typical request "Insert this order", "show this customer" "Revenue by month and region for 5 years"
Rows touched A few Millions or billions
Columns touched Most of the row A few columns
Access path Index lookups Full or partial scans
Writes Constant, small, concurrent Periodic bulk loads
Schema Normalized (3NF) Denormalized (star)
Data Current state History
Measured by Transactions per second, latency Query time over huge data
Users Applications and customers Analysts and dashboards

A normalized OLTP schema avoids redundancy so that updates are cheap and safe (see 1NF, 2NF and 3NF). The same schema is painful for analytics: a simple report needs six joins. Warehouses therefore use a simpler, deliberately redundant layout.

The star schema

A star schema has one big fact table in the middle and several smaller dimension tables around it. The fact table holds one row per event (a sale, a click, a shipment) with numeric measures (quantity, amount) and foreign keys to the dimensions. Each dimension describes one angle of the event (when, what, where, who) with plenty of descriptive columns, and is deliberately denormalized: a product row carries its category name directly instead of pointing to a category table. A query then needs only one join per dimension, and each join is a simple key lookup.

erDiagram
    FACT_SALES }o--|| DIM_DATE : "sold on"
    FACT_SALES }o--|| DIM_PRODUCT : "of"
    FACT_SALES }o--|| DIM_STORE : "at"
    FACT_SALES {
        int date_key
        int product_key
        int store_key
        int quantity
        int amount_cents
    }

A star schema: the fact table references three dimensions, so queries are one join per dimension.

The next example builds a tiny star and answers a typical analytical question: slice the facts by attributes from two dimensions and total them.

SQL A star schema and a roll-up report

Press Run (or Ctrl + Enter) to see the result here.

Going from the first report to the second is a roll-up (fewer grouping columns, coarser view); going back is a drill-down. Choosing a subset such as only 2025-01 is a slice or dice. These operations on a "cube" of measures and dimensions are the vocabulary of OLAP tools. Some server databases offer ROLLUP, CUBE and GROUPING SETS to compute several levels in one statement; SQLite does not, so here you write one GROUP BY per level.

Row stores and column stores

An OLTP engine is a row store: all the values of one row sit next to each other on a page. That is ideal for "fetch order 4711", which wants the whole row. Now imagine the warehouse query SELECT SUM(amount_cents) FROM fact_sales on a table with 50 columns. A row store has to read every page in full, 50 columns of data, to use one.

A column store (Vertica, ClickHouse, Redshift, BigQuery, DuckDB and others) keeps each column in its own contiguous region. The same query reads only the amount_cents region, roughly 1/50 of the bytes. Four more advantages follow:

  • Compression works far better. A column holds values of one type and often many repeats or small ranges, so it compresses by large factors.
  • Vectorized execution. The engine processes thousands of values of one column at a time, which suits modern CPUs.
  • Skipping. Per-block minimum and maximum values let the engine skip blocks that cannot match a filter.
  • Late materialization. Filters run on single columns, and full rows are rebuilt only for the survivors.

The price is that single-row writes and "give me the whole row" reads get more expensive, because one row is spread over many places. That is why column stores take data in bulk batches and OLTP engines remain row stores.

Why columns compress: run-length encoding

One simple technique is run-length encoding (RLE): replace a run of equal values with the value and a count. It does nothing for a row layout, where neighbouring values differ in type, but it can shrink a sorted or low-variety column dramatically. The Python demo below stores the same small table in both layouts, then compresses each. It counts "cells", the number of stored items, as a stand-in for bytes.

Python Run-length encoding: rows versus columns

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

The row layout has 24 cells and RLE cannot shrink them at all (24 stay 24), because no two neighbouring cells are equal. The column layout compresses the same data to 10 runs in total: country becomes 2 runs, category 4 and month 4. Real engines combine RLE with dictionary encoding (replace strings by small integers), delta encoding and bit packing, and they decompress in blocks as they scan.

Snowflake schemas

A star keeps each dimension in one denormalized table, so dim_product repeats the category name on every product row. A snowflake schema normalizes the dimensions: products point to a separate category table, and a store could point to a city table and then to a country table. Drawn out, the dimension tables branch like the arms of a snowflake.

Star schema Snowflake schema
Dimension tables One denormalized table per dimension Normalized into several linked tables
Joins per query One per dimension One per level of every dimension used
Redundancy and storage Some repeated text Less repetition, a little less space
Updates to a hierarchy Change many rows Change one row
Query speed and simplicity Faster, simpler SQL More joins, slower, harder for analysts

Most warehouses prefer the plain star because storage is cheap and query simplicity matters more; snowflaking is chosen when a dimension is huge or a hierarchy is shared by several dimensions. A fact constellation (galaxy schema) has several fact tables, for example sales and shipments, that share dimension tables.

OLAP server types: ROLAP, MOLAP and HOLAP

The cube of measures and dimensions can be implemented in three ways, which differ in where the data and the aggregates live:

  • ROLAP (relational OLAP) keeps everything in relational tables, usually a star or snowflake schema. The OLAP tool turns each slice, dice or roll-up into SQL, so it scales to very large data and loads quickly, but queries that aggregate a lot of detail take more time, and results depend on the database's optimizer. Summary tables and materialized views help.
  • MOLAP (multidimensional OLAP) loads the data into a specialized multidimensional array store and precomputes aggregates for the combinations of dimensions. Answers are very fast, because a query becomes a cell lookup, but the cube takes time and space to build, grows quickly as dimensions are added (the sparsity problem: most cells are empty), and handles very large detail data poorly.
  • HOLAP (hybrid OLAP) combines them: aggregates sit in a multidimensional store for speed, while detailed data stays in the relational database and is reached by drill-through when the user goes below the aggregate level.

The example builds a small snowflake, runs the same report with its extra join, then imitates the MOLAP idea: it computes every aggregation level once into a cube table (the word ALL marks a dimension that has been rolled up) so that later questions are single-row lookups.

SQL Snowflake joins and a MOLAP-style precomputed cube

Press Run (or Ctrl + Enter) to see the result here.

The first result needs three joins; the second answers "France in total" and "everything" without touching the fact table. With two dimensions the cube has four aggregation levels; with ten dimensions it would have 1,024, which is why real MOLAP engines precompute selectively.

Other OLAP flavours

Besides the three storage models, you will meet OLAP names that describe where the engine runs or what it analyzes:

  • DOLAP (desktop OLAP): a small cube is downloaded to the analyst's computer and explored offline. Portable, but limited by the size of the file.
  • WOLAP (web OLAP): the OLAP server answers requests from a browser, so no client software is needed.
  • TOLAP (transparent OLAP): the user queries ordinary relational tables, and the tool transparently rewrites the work onto aggregated summaries.
  • SOLAP (spatial OLAP): adds geographic dimensions, so you can roll up by city, region and country and draw the result on a map.
  • COLAP (cloud OLAP) and IOLAP (in-memory OLAP): the cube is hosted as a cloud service, or kept entirely in RAM for fast answers at the cost of capacity.

These are deployment variants of the same ideas: dimensions, measures and pre-aggregation.

The three-tier warehouse architecture

A warehouse is usually drawn in three tiers. The bottom tier is the warehouse database itself, fed by ETL from source systems (with a metadata repository and data marts alongside). The middle tier is the OLAP server (ROLAP, MOLAP or HOLAP). The top tier holds the front-end tools: query and reporting tools, dashboards and data-mining tools. Keep the tiers in mind when a vendor says "BI platform": it is the top tier plus an OLAP server.

Designing the warehouse: Inmon, Kimball and data marts

A data mart is a warehouse subset focused on one subject or department, such as sales or finance. There are two classic ways to organize the whole:

  • Inmon (top-down): first build one enterprise-wide, normalized (3NF) central warehouse that integrates all sources into a single source of truth. Departmental data marts are then derived from it, usually as star schemas. The result is consistent, but it takes a long time and a big up-front investment before anyone sees a report.
  • Kimball (bottom-up): start with the business processes that matter most and build a star-schema data mart for each one, using conformed dimensions (the same customer, date and product dimensions shared by every mart). The warehouse is the sum of the marts. Users get value early and the cost is spread out, but without strict discipline over the shared dimensions the marts drift apart.

In practice many organizations mix the two: a central integrated layer, star-shaped marts on top, and iterative delivery. Data marts are called dependent when they are fed from the central warehouse and independent when they are fed directly from source systems, the latter being quicker to build and harder to keep consistent.

Loading the warehouse: ETL and ELT

Data reaches the warehouse from many sources: the OLTP databases, logs, files, partner feeds. Two orders of work exist.

  • ETL (extract, transform, load): pull data out, clean and reshape it on a separate processing server, then load the finished tables. Good when the warehouse is expensive or sensitive data must be removed before it arrives.
  • ELT (extract, load, transform): load raw data into the warehouse first, then transform it with SQL inside the warehouse. Modern cloud warehouses are powerful enough that this is now the common choice, and the raw copy stays available for re-processing.

Either way, the transform step does the same chores: fixing types, removing duplicates, mapping codes to names, and assigning surrogate keys (the product_key above) so that the warehouse is independent of the source systems' ids. A dimension such as a customer changes over time (a customer moves city). The standard answer is a slowly changing dimension: for example, add a new row with validity dates ("type 2") so old facts still point to the address that was true then.

The last runnable example shows a small ELT step: raw text lands in a staging table, then SQL cleans it into a dimension.

SQL ELT: land the raw data, transform with SQL

Press Run (or Ctrl + Enter) to see the result here.

Four raw rows become three clean ones. Be aware that this tiny transform only capitalises the first letter, so "Ana berg" is not a perfect name. Real cleaning is a long list of such rules, which is why transformation is the bulk of warehouse engineering work.

Data lakes, lakehouses and NewSQL

A data lake is cheap, scalable storage (object storage such as S3) holding raw files in open formats like Parquet, which is itself a column format. It stores structured, semi-structured and unstructured data without a fixed schema up front ("schema on read"). Its risk is the "data swamp": without catalogs, quality checks and ownership, nobody knows what the files mean. A lakehouse adds table features (schemas, transactions, versioning) on top of lake files so one copy of the data serves both BI dashboards and data science.

NewSQL systems (Google Spanner, CockroachDB, TiDB and similar) take the opposite direction from NoSQL: they keep SQL and ACID transactions but spread the data over many nodes automatically. They aim at OLTP workloads that outgrow a single server. HTAP engines try to serve both OLTP and OLAP from one system, usually by keeping a row copy and a column copy of the same data. Both are real, but the trade-offs are important: distributed transactions cost latency (see Replication, Sharding and Distributed Transactions), and no engine removes the need to design for your workload.

Check your understanding

Quick check ยท 8 questions

of 8 answered You scored / 8 ยท perfect!

  1. 1. Which statement describes an OLAP query best?

    OLAP workloads are scans and aggregations over large amounts of history. The other three are typical OLTP operations.
  2. 2. What is in the middle of a star schema?

    The fact table records events with numeric measures and points to each dimension; the dimensions surround it like the points of a star.
  3. 3. Why does SELECT SUM(amount) over a 50-column table run faster in a column store?

    Each column is stored separately, so a query touches only the columns it names. This also helps compression.
  4. 4. What is the difference between ETL and ELT?

    The order of the steps differs. ELT is popular because modern warehouses can run the transformations at scale and keep the raw data.
  5. 5. What does a roll-up do?

    Roll-up reduces the grouping detail along a hierarchy; drill-down is the reverse.
  6. 6. Which OLAP server type precomputes aggregates in a multidimensional store for very fast answers, at the cost of build time and storage?

    MOLAP stores precomputed cube cells, so a query is a lookup. ROLAP keeps data in relational tables and generates SQL, and HOLAP combines both.
  7. 7. How does a snowflake schema differ from a star schema?

    Snowflaking normalizes dimension hierarchies. That reduces redundancy but adds joins, which is why simple stars are more common.
  8. 8. What marks the Kimball approach to warehouse design?

    Kimball works bottom-up with dimensional marts tied together by conformed dimensions. Inmon works top-down from a normalized central warehouse.

Summary

  • OLTP serves many small, current, concurrent transactions on normalized data; OLAP scans huge amounts of historical data for aggregates.
  • A star schema has a central fact table of measures and surrounding denormalized dimension tables, so reports need one simple join per dimension.
  • Roll-up, drill-down, slice and dice are the standard OLAP operations.
  • A snowflake schema normalizes the dimensions of a star (less redundancy, more joins); ROLAP queries relational tables, MOLAP precomputes a multidimensional cube, HOLAP mixes both.
  • Inmon builds a normalized central warehouse first and derives data marts; Kimball builds star-schema marts bottom-up with conformed dimensions.
  • Column stores read only needed columns and compress well (run-length, dictionary, delta encoding), but are weaker for single-row writes.
  • ETL transforms before loading; ELT loads raw data and transforms in the warehouse. Surrogate keys and slowly changing dimensions are common warehouse techniques.
  • Data lakes hold raw files cheaply, lakehouses add table features, NewSQL scales SQL transactions across nodes, and HTAP tries to combine both workloads.

Next: Authentication, Authorization and Access Control.