Data modelling: normalisation, star schemas and SCDs

Data modelling means deciding which tables you have, what one row in each table means, and how the tables connect. It sounds dull but it decides whether your queries are simple or painful, and whether your numbers are right.

There are two main styles, and they solve opposite problems. Normalised models (used by application databases) avoid repeating data so that updates are safe. Dimensional models (used by warehouses) group data so that reports are easy and fast. A data engineer moves data from the first kind to the second.

Imagine Brew & Bean stored everything in one spreadsheet-like table, one row per item sold:

Problems show up quickly. Asha's phone number is stored twice; if she changes it you must update every row, and if you miss one the data disagrees with itself (an update anomaly). If you stop selling croissants and delete all their rows, you also lose the fact that croissants cost ₹150 (a delete anomaly). You cannot add a new menu item until someone orders it (an insert anomaly).

Normalisation fixes this by splitting the table so each fact lives in exactly one place.

A useful summary is: *every column depends on the key, the whole key, and nothing but the key*. After normalising, Brew & Bean has four small tables:

CREATE TABLE customers (
  customer_id INTEGER PRIMARY KEY,
  name        TEXT NOT NULL,
  phone       TEXT NOT NULL
);

CREATE TABLE items (
  item_id  TEXT PRIMARY KEY,   -- 'cappuccino'
  name     TEXT NOT NULL,
  category TEXT NOT NULL,
  price    INTEGER NOT NULL    -- rupees
);

CREATE TABLE orders (
  order_id    INTEGER PRIMARY KEY,
  customer_id INTEGER NOT NULL REFERENCES customers(customer_id),
  created_at  TEXT NOT NULL
);

CREATE TABLE order_items (
  order_id INTEGER NOT NULL REFERENCES orders(order_id),
  item_id  TEXT    NOT NULL REFERENCES items(item_id),
  qty      INTEGER NOT NULL,
  PRIMARY KEY (order_id, item_id)
);

Each fact is stored once. Phone number lives only in customers; price only in items.

This design is excellent for an app that takes orders: writes are small and safe. But to answer "revenue by category by month" you must join four tables. On a few thousand rows that is fine. On billions of rows, many joins get slow and confusing for analysts. That is where dimensional modelling comes in.

A star schema has one central fact table surrounded by dimension tables, so a diagram looks like a star.

- A fact table records events or measurements: one row per order line, per payment, per click. It holds numbers you add up (quantity, amount) and keys that point to dimensions. - A dimension table describes the who, what, when and where: customers, items, dates, stores. It holds the descriptive text you filter and group by.

The most important idea is grain: the exact meaning of one row in the fact table. Decide and write it down first: "one row per item on an order". If grain is unclear, you will double count or miss rows. Everything else follows from it.

CREATE TABLE dim_date (
  date_key  INTEGER PRIMARY KEY,   -- 20261015
  full_date TEXT,
  day_name  TEXT,
  month     INTEGER,
  year      INTEGER
);

CREATE TABLE dim_item (
  item_key   INTEGER PRIMARY KEY,  -- surrogate key, no business meaning
  item_id    TEXT,                 -- business key: 'cappuccino'
  item_name  TEXT,
  category   TEXT,
  price      INTEGER,
  valid_from TEXT NOT NULL,
  valid_to   TEXT,
  is_current INTEGER NOT NULL DEFAULT 1
);

CREATE TABLE fact_order_items (
  order_id   INTEGER,
  item_key   INTEGER REFERENCES dim_item(item_key),
  date_key   INTEGER REFERENCES dim_date(date_key),
  qty        INTEGER,
  line_total INTEGER
);

dim_item has extra columns (valid_from, valid_to, is_current) that we explain in the SCD section below.

Now the earlier question needs just two joins to dimensions and one GROUP BY:

SELECT i.category, d.month, SUM(f.line_total) AS revenue
FROM fact_order_items AS f
JOIN dim_item AS i ON i.item_key = f.item_key
JOIN dim_date AS d ON d.date_key = f.date_key
GROUP BY i.category, d.month
ORDER BY i.category, d.month;

Every report looks like this: join the fact to the dimensions you need, then group and sum.

Dimensions change, slowly. A customer moves city. A menu item's price rises. A product moves to a different category. What should last year's sales report show? The price at that time, or today's? Different answers need different handling.

Type 2 is the one interviewers ask about. Suppose the cappuccino price rises from ₹180 to ₹200 on 1 October 2026. We close the old row and insert a new one. Old facts still point to the old item_key (price 180) and new facts point to the new one (price 200).

BEGIN;

-- 1. close the current version
UPDATE dim_item
SET valid_to = '2026-10-01', is_current = 0
WHERE item_id = 'cappuccino' AND is_current = 1;

-- 2. insert the new version
INSERT INTO dim_item (item_id, item_name, category, price, valid_from, valid_to, is_current)
VALUES ('cappuccino', 'Cappuccino', 'coffee', 200, '2026-10-01', NULL, 1);

COMMIT;

Both statements are inside one transaction, so a failure never leaves the table half-updated.

When loading a new fact row, look up the dimension row whose validity covers the order date (or just the current row for today's data) and store its surrogate key. Tools such as dbt call this feature snapshots; you will meet it in *Transformations with dbt*.

You are a senior data modeller. I'm designing a star schema for [business, e.g. a food-delivery app].

Grain of the fact table: [one row per ...]
Fact table columns: [list]
Dimensions: [list with columns]

Check: (1) is the grain clear and consistent with the columns, (2) are any columns in the wrong table, (3) which dimensions need SCD Type 2 and why, (4) what questions would be hard to answer with this model. Be specific.

What to expect: Good answers challenge the grain and point out mixed-grain columns (such as an order total stored on each line), then suggest which attributes need history.