Skip to content
Tutorials Center
My learning

Topics

Lesson 83 of 125 ยท Designing Databases

Data Warehousing: Star Schema, OLTP vs OLAP

Learn how data warehouses differ from transactional databases. Build a star schema with fact and dimension tables, run analytic queries and add aggregate tables.

So far you have designed databases the way an application wants them: normalized tables, one fact stored in one place, foreign keys everywhere. That design is perfect for taking orders and updating accounts. It is not always perfect for answering questions like "What was our revenue by product category and quarter, for every store, over the last three years?" Those questions touch millions of rows and join many tables, and people ask them again and again.

Analysts solve this by building a second database shaped for reading, called a data warehouse. The most common shape is the star schema: one big table of events surrounded by small descriptive tables. In this lesson you will learn how the two worlds differ, build a star schema, query it, and speed it up with pre-computed summary tables.

OLTP and OLAP

Databases serve two very different kinds of work:

OLTP (online transaction processing) OLAP (online analytical processing)
Typical task Place an order, update a profile Revenue by region and month
Rows touched per query A handful Millions
Operations Many small reads and writes Few big reads, bulk loads
Design Normalized (3NF) Denormalized (star schema)
Data Current state History, often years
Users Applications, thousands at once Analysts, dashboards

A normalized design splits data into many narrow tables so that each fact lives once (see Normalization). That makes writes safe, but a report may need seven joins. A warehouse trades some redundancy for simpler, faster reads. Data usually flows one way, from the OLTP system into the warehouse, in a nightly or hourly job often called ETL (extract, transform, load).

flowchart LR
    A["App database<br/>(OLTP, normalized)"] -->|"extract"| B["Staging area"]
    B -->|"transform and clean"| C["Warehouse<br/>(OLAP, star schema)"]
    C --> D["Reports and dashboards"]

Data is copied from the application database into a warehouse that is shaped for analysis.

Facts and dimensions

A star schema has two kinds of tables.

  • A fact table records events or measurements: one row per sale, per click, per shipment. It holds numbers you can add up (the measures, such as quantity and revenue) and foreign keys pointing at the dimensions. Fact tables are tall and narrow, and they grow forever.
  • A dimension table describes the context of the facts: who, what, where, when. Dimensions are short and wide, with many descriptive text columns (product name, category, brand, city, country) that you filter and group by.

Drawn out, the fact table sits in the middle and the dimensions radiate from it like points of a star:

erDiagram
    FACT_SALES }o--|| DIM_DATE : "sold on"
    FACT_SALES }o--|| DIM_PRODUCT : "which product"
    FACT_SALES }o--|| DIM_STORE : "sold at"
    FACT_SALES {
        int date_key
        int product_key
        int store_key
        int quantity
        real revenue
    }
    DIM_DATE {
        int date_key
        text month_name
        int quarter
        int year
    }
    DIM_PRODUCT {
        int product_key
        text name
        text category
    }
    DIM_STORE {
        int store_key
        text city
        text country
    }

One fact table, three dimensions: the star.

Notice that dim_product holds the category right inside it, instead of pointing to a separate categories table. Storing it directly is the denormalization that makes star schemas easy to query. (A design that splits dimensions further is called a snowflake schema. It saves a little space but costs extra joins, so most teams avoid it.)

Building a star schema

Dimensions get a surrogate key: a meaningless integer made by the warehouse, so it does not depend on the source system's ids. The date dimension is special: it contains one row for every calendar day you care about, with the month, quarter and year already worked out, so queries never need to compute them.

SQL A small sales star schema

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

Every analytic query in a star schema has the same shape: start from the fact table, JOIN the dimensions you need, GROUP BY dimension columns, and SUM the measures. Once you know the pattern, you can answer almost any question by swapping which dimension columns you group by. Try replacing p.category with p.name, or adding a join to dim_store and grouping by country.

Choose the grain first. The grain is what one fact row means: "one product on one receipt line", or "one store's sales of one product per day". Decide it before you build anything, write it down, and never mix grains in one fact table. Most star-schema bugs are really grain mistakes, such as joining a daily fact to a monthly one and counting twice.

Slicing, dicing and filtering

Because each dimension is a separate small table, filtering is cheap and readable. The terms analysts use have simple SQL equivalents:

  • Slice: fix one dimension to a single value with WHERE (only 2026, only Germany).
  • Dice: filter several dimensions at once.
  • Roll up: group at a coarser level (months to quarters, cities to countries).
  • Drill down: group at a finer level (countries to cities).
SQL Dice, roll up and drill down

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

Aggregate tables

Even a well-built star schema gets slow when the fact table has billions of rows and a dashboard sums them on every page load. The classic cure is an aggregate table: a smaller fact table pre-computed at a coarser grain, such as one row per month, category and country. Dashboards read the small table; only unusual questions go to the detailed one.

SQL Building a monthly aggregate table

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

Aggregates only work for measures that can be added back up. Sums and counts are additive. A ratio such as average price is not: to roll it up you must store the sum and the count and divide at the end. The fact_rows column above is there for exactly that reason. Many databases can maintain aggregates for you with materialized views (see Views and PostgreSQL); otherwise a scheduled job rebuilds the table.

Slowly changing dimensions

What happens when a product changes category, or a customer moves to another city? Overwriting the dimension row rewrites history: last year's sales would suddenly appear under the new category. The usual fix, "type 2", keeps one row per version, with valid_from, valid_to and an is_current flag, and each fact points at the version that was true when it happened. This is why dimensions use surrogate keys instead of the source system's ids.

Check your understanding

Quick check ยท 5 questions

of 5 answered You scored / 5 ยท perfect!

  1. 1. What does one row in a fact table typically represent?

    Fact tables record events (sales, clicks, shipments) with numeric measures and foreign keys to the dimensions that describe them.
  2. 2. Why does a star schema store the product category directly in the product dimension?

    Denormalizing dimensions removes joins and keeps analytic queries simple and fast. The repeated text is a cheap price in a read-mostly warehouse.
  3. 3. Which workload is OLAP?

    OLAP queries scan and aggregate large amounts of history. The other three are small transactional writes.
  4. 4. A dashboard needs monthly revenue by category on every page view, and the fact table has billions of rows. What helps most?

    A pre-aggregated table has far fewer rows, so the dashboard reads a tiny table instead of scanning the detailed facts.
  5. 5. Why is an average price column risky to store in an aggregate table?

    Averaging averages gives wrong answers when groups have different sizes. Store the sum and the count and divide at the end.

Summary

  • OLTP databases are normalized and tuned for many small writes; OLAP warehouses are denormalized and tuned for large reads over history.
  • A star schema has a central fact table (events and additive measures) joined to small dimension tables (context to filter and group by).
  • Decide the grain of the fact table first and keep it consistent.
  • Analytic queries all look alike: fact table, JOIN dimensions, GROUP BY dimension columns, SUM measures.
  • Aggregate tables pre-compute coarser summaries so dashboards stay fast; store sums and counts rather than averages.
  • Use surrogate keys and versioned rows (type 2) so dimension changes do not rewrite history.

Next: Multi-Tenant Schema Design, where one database has to serve many customers without mixing their data.