What is a Star Schema?

How it works

A star schema has two kinds of table. The fact table records one business event per row at a declared grain, with numeric measures and a foreign key to each dimension; it is long and narrow. Dimension tables hold the descriptive context people filter and group by; they are wide, mostly text, and small compared with the facts. Dimensions join only to the fact table, never to each other, so the diagram looks like a star.

The design makes analytical SQL predictable. Analysts and BI tools learn one query pattern, and warehouses optimise for it: filters on the small dimensions are applied first, and the large fact table is scanned once.

A few rules keep a star healthy:

• Declare the grain first (“one row per order line”), then add only dimensions and measures that are true at that grain.
• Use surrogate keys on dimensions, so a customer can have several historical versions.
• Denormalise dimensions: product, category and department sit in one row, even though department repeats.
• No NULL foreign keys: point unknown values at an “Unknown” member so inner joins do not drop facts.
• Keep text out of the fact table; it belongs in dimensions.

Several stars that share conformed dimensions (one dim_customer, one dim_date) form a galaxy or constellation schema. Normalising the dimensions into further tables gives a snowflake schema.

Example

A grain test that belongs in every pipeline: the declared grain columns must be unique. An empty result means the grain holds; here a duplicated order line is caught.

CREATE TABLE fact_order_line (
  order_id text, line_number int, product_key int, customer_key int, net_amount numeric(10,2)
);
INSERT INTO fact_order_line VALUES
  ('O-1', 1, 10, 1, 49.99), ('O-1', 2, 11, 1, 7.98),
  ('O-2', 1, 10, 2, 49.99), ('O-2', 1, 10, 2, 49.99);

SELECT order_id, line_number, count(*) AS copies
FROM fact_order_line
GROUP BY order_id, line_number
HAVING count(*) > 1;

 order_id | line_number | copies
----------+-------------+--------
 O-2      |           1 |      2

Common mistakes

• Joining two fact tables directly, which multiplies rows; aggregate each to a shared grain first.
• Mixing grains, such as putting an order-level shipping fee on every order line, so sums double count.
• NULL foreign keys that make rows vanish from inner joins.
• Giving every low-cardinality flag its own tiny dimension instead of combining them into a junk dimension.