0Pricing
SQL Academy · Lesson

Fact and Dimension Tables

The building blocks of a warehouse.

Fact and Dimension Tables is a free SQL Academy lesson on CoddyKit — lesson 2 of 4. You can read the complete lesson below for free — then practise it hands-on in the browser with a built-in code editor and a 24/7 AI tutor. It is part of the SQL Academy learning path, one of 4 lessons in the course, and your progress syncs across the web and the CoddyKit app.

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
);

Dimension Tables Defined

A dimension table stores descriptive attributes that give context to each fact. Examples include a product dimension (name, category, brand) or a date dimension (day, month, quarter, year).

Dimension tables are usually short (fewer rows) but wide (many descriptive columns). They are joined to the fact table using surrogate integer keys.

CREATE TABLE dim_product (
  product_key  SERIAL PRIMARY KEY,
  product_name VARCHAR(200) NOT NULL,
  category     VARCHAR(100),
  brand        VARCHAR(100),
  unit_cost    NUMERIC(10, 2)
);

CREATE TABLE dim_customer (
  customer_key SERIAL PRIMARY KEY,
  full_name    VARCHAR(200) NOT NULL,
  email        VARCHAR(200),
  country      VARCHAR(100),
  segment      VARCHAR(50)
);

The Date Dimension

The date dimension is the most common dimension in any warehouse. Instead of storing a raw TIMESTAMP in the fact table, you store an integer key that references a pre-built calendar table.

This allows queries to filter or group by fiscal quarter, day of week, holiday flags, and other calendar attributes without any date arithmetic at query time.

CREATE TABLE dim_date (
  date_key       INT PRIMARY KEY,  -- e.g. 20240315
  full_date      DATE NOT NULL,
  day_of_week    VARCHAR(10),
  day_of_month   INT,
  month_num      INT,
  month_name     VARCHAR(20),
  quarter        INT,
  year           INT,
  is_holiday     BOOLEAN DEFAULT FALSE,
  fiscal_quarter INT
);

-- Sample row
INSERT INTO dim_date VALUES
  (20240315, '2024-03-15', 'Friday', 15, 3, 'March', 1, 2024, FALSE, 2);

The Star Schema Pattern

When you draw a diagram with one fact table in the centre and dimension tables radiating outward, it looks like a star — hence the name star schema.

Foreign keys in the fact table point to the primary keys of each dimension. Queries typically join the fact table to one or more dimensions to add descriptive context to the raw numbers.

-- Join fact to two dimensions to enrich a sales report
SELECT
  dp.product_name,
  dp.category,
  SUM(fs.quantity)     AS total_units_sold,
  SUM(fs.total_amount) AS total_revenue
FROM fact_sales fs
JOIN dim_product  dp ON dp.product_key  = fs.product_key
JOIN dim_date     dd ON dd.date_key     = fs.date_key
WHERE dd.year = 2024
GROUP BY dp.product_name, dp.category
ORDER BY total_revenue DESC;

Surrogate Keys vs. Natural Keys

Dimension tables use surrogate keys — synthetic integers generated by the database, independent of any business meaning. Natural keys (like a product SKU or customer email) can change over time, but surrogate keys never do.

Using surrogate keys insulates the fact table from upstream system changes and makes joins faster since integer comparisons are cheaper than string comparisons.

-- Surrogate key approach: integer join is fast
SELECT fs.sale_id, dc.full_name, fs.total_amount
FROM fact_sales fs
JOIN dim_customer dc ON dc.customer_key = fs.customer_key
WHERE dc.country = 'Germany'
LIMIT 10;

-- Natural key approach (avoid in warehouses): slower string join
-- JOIN dim_customer dc ON dc.email = fs.customer_email

Grain: The Level of Detail in a Fact Table

The grain of a fact table describes exactly what one row represents. Before building a warehouse, you must declare the grain — for example, one row per individual product line on a sales order.

A well-defined grain prevents ambiguous aggregations. If different rows represent different events, your SUM and COUNT results will be meaningless.

-- Grain: one row per product per order line
-- Each row = one line item sold in one transaction
SELECT
  sale_id,
  date_key,
  product_key,
  quantity,
  unit_price,
  total_amount
FROM fact_sales
WHERE date_key = 20240315
ORDER BY sale_id;

Additive, Semi-Additive, and Non-Additive Measures

Facts come in three flavours based on how you can aggregate them:

  • Additive — can be summed across all dimensions (e.g. revenue, quantity).
  • Semi-additive — can be summed across some dimensions but not all (e.g. account balance can be summed across customers but not across time).
  • Non-additive — cannot be meaningfully summed (e.g. unit_price, ratio). Use AVG or other aggregates instead.
SELECT
  dd.month_name,
  SUM(fs.total_amount)         AS total_revenue,   -- additive
  AVG(fs.unit_price)           AS avg_unit_price,   -- non-additive: use AVG
  SUM(fs.quantity)             AS total_units       -- additive
FROM fact_sales fs
JOIN dim_date dd ON dd.date_key = fs.date_key
WHERE dd.year = 2024
GROUP BY dd.month_name, dd.month_num
ORDER BY dd.month_num;

Slowly Changing Dimensions (SCD Type 1 and 2)

Dimension attributes change over time — a customer moves country, a product changes category. Slowly Changing Dimensions (SCD) handle these changes:

  • Type 1 — Overwrite the old value. Simple, but history is lost.
  • Type 2 — Add a new row with a new surrogate key and validity dates. Preserves full history so historical facts still point to the correct version of the dimension.
-- SCD Type 2: add a new version of the row
ALTER TABLE dim_customer ADD COLUMN valid_from DATE;
ALTER TABLE dim_customer ADD COLUMN valid_to   DATE;
ALTER TABLE dim_customer ADD COLUMN is_current BOOLEAN DEFAULT TRUE;

-- Expire the old row
UPDATE dim_customer
SET is_current = FALSE,
    valid_to   = CURRENT_DATE - INTERVAL '1 day'
WHERE email = 'anna@example.com' AND is_current = TRUE;

-- Insert the updated version
INSERT INTO dim_customer (full_name, email, country, segment, valid_from, valid_to, is_current)
VALUES ('Anna Muller', 'anna@example.com', 'Austria', 'Premium', CURRENT_DATE, '9999-12-31', TRUE);

Degenerate Dimensions

Sometimes a dimension attribute does not need its own table. A degenerate dimension is a dimension key that lives directly in the fact table with no corresponding dimension table.

Classic examples are order numbers, invoice numbers, or ticket IDs. They provide context for drilling down but have no other descriptive columns worth storing in a separate table.

-- order_number is a degenerate dimension:
-- it lives in the fact table, no dim_order table needed
CREATE TABLE fact_order_lines (
  line_id      SERIAL PRIMARY KEY,
  order_number VARCHAR(20) NOT NULL,  -- degenerate dimension
  date_key     INT NOT NULL,
  product_key  INT NOT NULL,
  customer_key INT NOT NULL,
  quantity     INT NOT NULL,
  line_total   NUMERIC(12, 2) NOT NULL
);

SELECT order_number, SUM(line_total) AS order_total
FROM fact_order_lines
GROUP BY order_number
ORDER BY order_total DESC
LIMIT 5;

Querying the Full Star Schema

Putting it all together: a typical warehouse query joins the fact table to several dimensions, applies filters on dimension attributes, and aggregates measures from the fact table.

The optimizer can handle these multi-way joins efficiently because the fact table foreign keys are indexed and the dimension tables are relatively small.

SELECT
  dd.year,
  dd.quarter,
  dp.category,
  dc.country,
  SUM(fs.quantity)     AS units_sold,
  SUM(fs.total_amount) AS revenue
FROM fact_sales fs
JOIN dim_date     dd ON dd.date_key     = fs.date_key
JOIN dim_product  dp ON dp.product_key  = fs.product_key
JOIN dim_customer dc ON dc.customer_key = fs.customer_key
WHERE dd.year IN (2023, 2024)
  AND dp.category = 'Electronics'
GROUP BY dd.year, dd.quarter, dp.category, dc.country
ORDER BY dd.year, dd.quarter, revenue DESC;

Quick Check: Fact vs. Dimension

Test your understanding of how fact and dimension tables differ in a star schema.

Lesson Recap

In this lesson you learned the core building blocks of a data warehouse star schema:

  • Fact tables hold measurable events (sales, clicks, transactions) with numeric measures and foreign keys.
  • Dimension tables provide descriptive context (who, what, where, when) using surrogate keys.
  • The grain defines exactly what one fact row represents — declare it before building.
  • Measures are additive, semi-additive, or non-additive, which determines how you aggregate them.
  • SCD Type 2 preserves historical dimension values by adding new rows with validity dates.
  • Degenerate dimensions live in the fact table when they have no extra attributes to describe.

Understanding fact and dimension tables is the foundation for building fast, scalable, and analytically powerful warehouses.

Frequently asked questions

Is the “Fact and Dimension Tables” lesson free?

Yes — the full text of “Fact and Dimension Tables” is free to read here on the web, and the SQL Academy course includes 4 lessons in total. To practise it interactively (a built-in code editor and a 24/7 AI tutor) and unlock the rest of the SQL Academy course, upgrade to CoddyKit PRO.

What will I learn in “Fact and Dimension Tables”?

The building blocks of a warehouse. You practise SQL Academy with hands-on code you run directly in the browser, and a 24/7 AI tutor answers your questions as you work through the lesson.

Do I need any experience to start SQL Academy?

No prior experience is required. SQL Academy on CoddyKit is structured for beginners through advanced learners; this is — lesson 2 of 4, so you can start here or from the beginning and move at your own pace.

How long does the “Fact and Dimension Tables” lesson take?

Most CoddyKit lessons take about 5–10 minutes. Each one is bite-sized and interactive, so you make steady progress and pick up exactly where you left off across the web and the app.

Can I write and run code in this SQL Academy lesson?

Yes. Every SQL Academy lesson includes a built-in code editor, so you write and run real code right in your browser and get instant AI feedback — no local setup required.

All lessons in this course

  1. OLTP vs OLAP
  2. Fact and Dimension Tables
  3. Star and Snowflake Schemas
  4. Writing Analytical Queries
← Back to SQL Academy