Design a star schema for sales reporting

A star schema arranges reporting data as one central fact table (the things that happened, with numbers) surrounded by dimension tables (the things you describe and filter them by). Most dashboards and BI tools expect this shape because it keeps queries simple and fast.

Step 1: Decide the grain

The grain is the meaning of one row in the fact table. Choose it first and write it down in a sentence, because everything else follows from it.

One row per product on one sales order line.

A grain that is too coarse (one row per day) throws away detail you cannot get back. A grain that is too fine costs storage and time. Order-line level is the usual choice for sales.

Step 2: List the facts and the dimensions

Look at what you want to report and what you want to slice it by.

• Facts are numeric and additive: quantity and net amount.
• Dimensions answer who, what, when and where: date, product and customer.

Keep descriptive text out of the fact table. If a column is a label or a category, it belongs in a dimension.

Step 3: Create the tables

Each dimension gets a surrogate key (a meaningless integer) plus the business key from the source system. The fact table holds only keys and measures.

CREATE TABLE dim_date (
    date_key     INTEGER PRIMARY KEY,   -- 20250131
    full_date    TEXT NOT NULL,
    year         INTEGER NOT NULL,
    month        INTEGER NOT NULL,
    month_name   TEXT NOT NULL,
    is_weekend   INTEGER NOT NULL
);
CREATE TABLE dim_product (
    product_key  INTEGER PRIMARY KEY,
    sku          TEXT NOT NULL UNIQUE,
    product_name TEXT NOT NULL,
    category     TEXT NOT NULL
);
CREATE TABLE dim_customer (
    customer_key INTEGER PRIMARY KEY,
    customer_id  TEXT NOT NULL UNIQUE,
    country      TEXT NOT NULL,
    segment      TEXT NOT NULL
);
CREATE TABLE fact_sales (
    sale_id       INTEGER PRIMARY KEY,
    date_key      INTEGER NOT NULL REFERENCES dim_date (date_key),
    product_key   INTEGER NOT NULL REFERENCES dim_product (product_key),
    customer_key  INTEGER NOT NULL REFERENCES dim_customer (customer_key),
    quantity      INTEGER NOT NULL,
    net_amount    REAL    NOT NULL
);

A date dimension looks like overkill until you need "weekends", "fiscal quarter" or "month name" in a report. Putting those in a table once is far easier than computing them in every query.

Step 4: Load a little data

Load the dimensions first, then the fact table, so every key already exists.

INSERT INTO dim_date VALUES
 (20250105, '2025-01-05', 2025, 1, 'January', 1),
 (20250106, '2025-01-06', 2025, 1, 'January', 0),
 (20250207, '2025-02-07', 2025, 2, 'February', 0);
INSERT INTO dim_product VALUES
 (1, 'NB-01', 'Notebook', 'Stationery'), (2, 'PN-01', 'Pen', 'Stationery'), (3, 'LP-01', 'Desk lamp', 'Furniture');
INSERT INTO dim_customer VALUES
 (1, 'C-100', 'DE', 'Business'), (2, 'C-200', 'FR', 'Consumer'), (3, 'C-300', 'DE', 'Consumer');
INSERT INTO fact_sales (date_key, product_key, customer_key, quantity, net_amount) VALUES
 (20250105, 1, 1, 10, 45.0), (20250105, 3, 2, 1, 24.0), (20250106, 2, 1, 50, 60.0),
 (20250207, 3, 3, 2, 48.0),  (20250207, 1, 2, 3, 13.5);

Step 5: Query it

Every question has the same shape: join the fact to the dimensions you need, group by their attributes, aggregate the measures.

SELECT d.year, d.month_name, p.category, c.country,
       SUM(f.quantity)               AS units,
       ROUND(SUM(f.net_amount), 2)   AS revenue
FROM fact_sales AS f
JOIN dim_date     AS d ON d.date_key     = f.date_key
JOIN dim_product  AS p ON p.product_key  = f.product_key
JOIN dim_customer AS c ON c.customer_key = f.customer_key
GROUP BY d.year, d.month, d.month_name, p.category, c.country
ORDER BY d.year, d.month, p.category, c.country;

The result:

Adding "by segment" or "by weekday" means adding one column to the SELECT and GROUP BY. The joins never change.

Step 6: Check the model

Two cheap checks catch most mistakes.

PRAGMA foreign_key_check;                              -- no rows: every key matches a dimension row
SELECT COUNT(*), COUNT(DISTINCT sale_id) FROM fact_sales;   -- both numbers equal: the grain holds

In SQLite PRAGMA foreign_key_check lists violations, so an empty result is what you want. On a real warehouse, compare the fact table's total revenue with the source system's total: if they differ, a join is dropping or duplicating rows.

Design rules worth keeping

• Declare the grain and never mix grains in one fact table.
• Measures are numbers you can add up. Averages and ratios are calculated from measures at query time, not stored.
• Dimensions are wide and descriptive. Denormalising a product's category into dim_product is intended. It saves a join on every query.
• Use an explicit "Unknown" member so a late-arriving or missing value does not break a join.