Data Warehousing Fundamentals with SQL: Star Schema, Facts, and Dimensions

1. What data warehousing actually solves

Data warehousing structures data for analysis over long time spans and large data volumes, unlike a transactional database, which is optimized for fast individual accesses. A normalized, transactional schema structure with many small tables connected via foreign keys is unwieldy for analysts and reporting tools: every query needs numerous joins just to compute a simple metric like revenue per month and region.

Data warehousing solves this problem with a dedicated, analysis optimized schema structure: the star schema. Central metrics live in a fact table, contextual information in dimension tables, and both are connected so that typical analytical queries get by with minimal join complexity. The following sections show how star schema, fact tables and dimension tables are actually built, how to handle changing dimension data and how aggregation queries run performantly on top of it.

2. The star schema as a central structure

The star schema arranges a central fact table in a star shape around several dimension tables. The fact table contains measurable events (orders, clicks, transactions) as rows, each with foreign keys to the relevant dimensions (customer, product, time, region) and the actual metrics (revenue, quantity, discount). The name of the data warehousing star schema comes from its visual appearance: the fact table at the center, dimension tables like the points of a star around it.

The major advantage of this structure: a typical analytical query needs only a single join level, from the fact table directly to each needed dimension, instead of several nested joins as in a normalized transactional schema. That makes queries readable for analysts and efficiently planned by the database optimizer, because join order and join strategy are much more predictable with a star schema.

-- Star schema: one fact table, three dimension tables
CREATE TABLE dim_customer (
  customer_key SERIAL PRIMARY KEY,
  customer_id INT NOT NULL,
  customer_name VARCHAR(200),
  segment VARCHAR(50)
);

CREATE TABLE dim_product (
  product_key SERIAL PRIMARY KEY,
  product_id INT NOT NULL,
  product_name VARCHAR(200),
  category VARCHAR(100)
);

CREATE TABLE dim_date (
  date_key INT PRIMARY KEY, -- e.g. 20260730
  full_date DATE NOT NULL,
  year INT,
  quarter INT,
  month INT
);

CREATE TABLE fact_sales (
  sale_id BIGSERIAL PRIMARY KEY,
  customer_key INT REFERENCES dim_customer(customer_key),
  product_key INT REFERENCES dim_product(product_key),
  date_key INT REFERENCES dim_date(date_key),
  quantity INT NOT NULL,
  revenue_eur NUMERIC(12,2) NOT NULL
);

3. Fact tables: metrics and granularity

The fact table at the center of every data warehousing model contains the actual measurable quantities, usually numeric metrics such as revenue, quantity or duration. The decisive factor is a fact table's granularity, meaning the question of what exactly a single row represents. A row per individual sale is finer grained than a row per day and product, but allows more flexible aggregation afterward.

Once granularity is set, it should not be changed anymore without rebuilding the entire fact table. Additive metrics (revenue, quantity) can be summed across any dimension without issue. Semi additive metrics (inventory level) can be summed across some dimensions but not over time, because an inventory level at month end must not be added to the previous month's inventory level. This distinction is central to correct aggregation queries in data warehousing.

4. Dimension tables: context for facts

Dimension tables provide the descriptive context to the metrics in the fact table: who, what, when, where. Unlike the fact table, which grows quickly (every transaction creates a new row), dimension tables change rarely and are usually much smaller. A good dimension table in data warehousing deliberately denormalizes: instead of a customer dimension with a foreign key to a separate country table, it contains the country name directly as a column, to avoid joins.

This deliberate denormalization is a central difference between transactional schemas and data warehousing schemas. In a transactional database, redundancy would be a sign of poor design; in a data warehouse it is a deliberate optimization for read speed, because write load in a warehouse plays a secondary role compared to read speed for analysis.

5. Managing slowly changing dimensions

A central problem in data warehousing: dimension data occasionally changes, for instance when a customer moves to a different sales territory. A slowly changing dimension of type 1 simply overwrites the old value, but loses history in the process, which is problematic for some analyses if historical revenue gets retroactively attributed to the new sales territory even though it occurred in the old one.

A slowly changing dimension of type 2 solves this problem by inserting a new row with a validity period on every change, instead of overwriting the old one. The fact table then always references the version of the dimension that was valid at the time of the event. This pattern is standard in data warehousing because it guarantees historical correctness without having to adjust the fact table itself.

-- Slowly Changing Dimension Type 2: keep full history
CREATE TABLE dim_customer_scd2 (
  customer_key SERIAL PRIMARY KEY,
  customer_id INT NOT NULL,
  region VARCHAR(100),
  valid_from DATE NOT NULL,
  valid_to DATE,           -- NULL = currently active
  is_current BOOLEAN NOT NULL DEFAULT TRUE
);

-- When a customer's region changes: close the old row, insert a new one
UPDATE dim_customer_scd2
SET valid_to = '2026-07-29', is_current = FALSE
WHERE customer_id = 4821 AND is_current = TRUE;

INSERT INTO dim_customer_scd2 (customer_id, region, valid_from, valid_to, is_current)
VALUES (4821, 'DACH-Nord', '2026-07-30', NULL, TRUE);

-- Fact rows always reference the customer_key valid at event time
SELECT f.revenue_eur, d.region
FROM fact_sales f
JOIN dim_customer_scd2 d ON f.customer_key = d.customer_key;

6. Star schema vs. snowflake schema

The snowflake schema is a variant of the star schema where dimension tables themselves are further normalized instead of deliberately staying denormalized. A product dimension, for instance, gets split into product, category and subcategory, each as a separate table with its own foreign keys. This pattern reduces redundancy but increases the number of joins needed for a typical analytical query in data warehousing.

In practice, most modern data warehouse systems prefer the star schema over the snowflake schema, because storage space in cloud warehouses is cheap while query performance and readability for analysts matter far more. Snowflake schemas pay off mainly for very large, frequently changing dimensions, where the star schema's redundancy leads to noticeable storage or maintenance overhead.

-- Snowflake schema: product dimension normalized into separate tables
CREATE TABLE dim_category (
  category_key SERIAL PRIMARY KEY,
  category_name VARCHAR(100)
);

CREATE TABLE dim_product_snowflake (
  product_key SERIAL PRIMARY KEY,
  product_name VARCHAR(200),
  category_key INT REFERENCES dim_category(category_key)
);

-- Same query now needs one extra join compared to the star schema version
SELECT cat.category_name, SUM(f.revenue_eur)
FROM fact_sales f
JOIN dim_product_snowflake p ON f.product_key = p.product_key
JOIN dim_category cat ON p.category_key = cat.category_key
GROUP BY cat.category_name;

7. Aggregation queries over large fact tables

The actual purpose of a data warehousing system shows up in aggregation queries: sums, averages and counts over millions or billions of rows in the fact table, grouped by one or more dimensions. These queries benefit enormously from column oriented storage (column store), as used by modern data warehouses (Snowflake, BigQuery, ClickHouse), because only the actually needed columns have to be read instead of complete rows.

Partitioning the fact table by the time dimension (for instance by month) further accelerates aggregation queries, because the optimizer can completely skip partitions outside the queried time range (partition pruning). This combination of star schema, column orientation and time partitioning is why data warehousing systems deliver aggregations over huge data volumes in seconds instead of minutes.

-- Typical warehouse aggregation: revenue by region and month
SELECT
  d.year,
  d.month,
  c.segment,
  SUM(f.revenue_eur) AS total_revenue,
  COUNT(*) AS number_of_sales
FROM fact_sales f
JOIN dim_date d ON f.date_key = d.date_key
JOIN dim_customer c ON f.customer_key = c.customer_key
WHERE d.year = 2026
GROUP BY d.year, d.month, c.segment
ORDER BY d.month, total_revenue DESC;

8. OLAP cubes and materialized views

An OLAP cube is a precomputed, multidimensional aggregation of a star schema that makes typical analytical queries even faster, because sums are not computed only at query time. Classic OLAP servers such as SQL Server Analysis Services manage these cubes explicitly, while modern cloud warehouses usually achieve similar performance more simply via materialized views, which cache the results of frequent aggregation queries and refresh them periodically.

The trade off for both approaches in data warehousing is the same: precomputed aggregations significantly speed up read queries but need to be updated on every change to the underlying fact table, either incrementally or through a full recomputation. For frequently queried but rarely changing aggregations (such as monthly revenue reports), this trade off is almost always worthwhile.

-- Materialized view: precompute a frequent aggregation
CREATE MATERIALIZED VIEW mv_monthly_revenue AS
SELECT d.year, d.month, c.segment, SUM(f.revenue_eur) AS total_revenue
FROM fact_sales f
JOIN dim_date d ON f.date_key = d.date_key
JOIN dim_customer c ON f.customer_key = c.customer_key
GROUP BY d.year, d.month, c.segment;

-- Refresh periodically, e.g. via a nightly scheduled job
REFRESH MATERIALIZED VIEW CONCURRENTLY mv_monthly_revenue;

-- Reporting queries hit the precomputed view instead of the raw fact table
SELECT * FROM mv_monthly_revenue WHERE year = 2026 ORDER BY month;

9. Data warehouse models compared

The table below compares the central schema approaches in data warehousing.

For most new data warehousing projects, a star schema with materialized views for the most common aggregations is the most pragmatic starting point. Snowflake schemas and dedicated OLAP cubes only pay off for very specific storage efficiency requirements or fixed, high frequency report structures.

10. Summary

Data warehousing structures data for analysis instead of transactional write speed, usually via a star schema with a central fact table and several dimension tables. Fact tables contain measurable metrics with a firmly defined granularity, dimension tables provide deliberately denormalized context. Slowly changing dimensions of type 2 preserve historical correctness when dimension data changes.

Column oriented storage, time partitioning and materialized views make aggregation queries over huge fact tables performant without relying on dedicated OLAP cubes. Anyone building data warehousing from the start with clear granularity and well thought out SCD handling avoids the expensive rework that a schema rebuilt afterward requires.

11. FAQ: Data Warehousing Fundamentals with SQL