SQL Academy · Lesson

Fact and Dimension Tables

The building blocks of a warehouse.

What Is a Data Warehouse?

A data warehouse is a central repository designed for reporting and analytical queries. Unlike a transactional database that optimises for fast writes, a warehouse is tuned for fast reads across large volumes of historical data.

The most common way to organise a warehouse is with a star schema, which splits data into two table types: fact tables and dimension tables.

Fact Tables Defined

A fact table stores measurable, quantitative events — the things you want to analyse. Each row represents one occurrence of a business event, such as a sale, a web page view, or a support ticket.

Fact tables are typically wide (many rows) and narrow (few columns), with most columns being either foreign keys to dimension tables or numeric measures like quantity or revenue.

CREATE TABLE fact_sales (
  sale_id      SERIAL PRIMARY KEY,
  date_key     INT NOT NULL,
  product_key  INT NOT NULL,
  customer_key INT NOT NULL,
  store_key    INT NOT NULL,
  quantity     INT NOT NULL,
  unit_price   NUMERIC(10, 2) NOT NULL,
  total_amount NUMERIC(12, 2) NOT NULL
);

All lessons in this course

← Back to SQL Academy