Denormalisation, OLTP and OLAP

When to break the rules

A normalised design is the right default: every fact lives once, so it cannot disagree with itself. But some screens read the same expensive answer thousands of times a minute. If the order list must show each order's total, the database has to add up order_items for every order on every visit. Denormalisation means storing a copy or a pre-computed value on purpose so that common reads are cheap.

• Cached totals: orders.total, or a product's review_count and avg_rating.
• Copied labels: the customer's name on an invoice row, so a printed invoice never changes later.
• Summary tables: daily sales per category, rebuilt every night for a dashboard.

ALTER TABLE orders ADD COLUMN total INTEGER NOT NULL DEFAULT 0;
UPDATE orders
SET total = (SELECT SUM(qty * price) FROM order_items WHERE order_items.order_id = orders.id);
SELECT id, total FROM orders WHERE id IN (101, 106);
-- 101 | 1780
-- 106 | 4498

The cost: keeping copies in sync

Every copy can go stale. If someone adds a line to order 101 and forgets to update orders.total, the screen and the bill disagree. You must choose how to keep copies right: update both in the same transaction (module 8), use a trigger (module 11), or rebuild the summary on a schedule and accept that it is a few minutes old. Denormalise only when a measured slow query needs it, and write down how each copy is maintained.

OLTP and OLAP: two kinds of workload

Companies usually keep both. The app writes to the OLTP database; an ETL (extract, transform, load) or ELT job copies the data into a warehouse, where analysts run heavy reports without slowing the app down.

The star schema

A star schema has one fact table in the middle and several dimension tables around it, like the points of a star. A fact is a measurable event: one sale, one train ticket, one UPI payment. The fact table holds numbers (qty, amount) and keys. A dimension describes the "who, what, where, when" of the fact: a date dimension with month and quarter, a store dimension with city and region, a product dimension with category.

             dim_date (date_key, full_date, month, quarter)
                    |
dim_store ---- fact_sales (date_key, store_key, product_key, qty, amount) ---- dim_product
(store_key, city, region)                                          (product_key, name, category)

SELECT d.quarter, s.region, SUM(f.amount) AS revenue
FROM fact_sales f
JOIN dim_date d  ON d.date_key = f.date_key
JOIN dim_store s ON s.store_key = f.store_key
GROUP BY d.quarter, s.region
ORDER BY d.quarter, s.region;
-- Q1 | North | 3200
-- Q1 | South | 2900
-- Q1 | West  | 12497   ... 6 rows

Dimensions are deliberately denormalised: dim_store keeps city and region in one table, even though region depends on city. Analysts get simple, fast joins, and since the warehouse is loaded in batches, the update anomalies of topic 2 matter much less. A snowflake schema is the variant where dimensions are normalised into further tables.

Cloud data warehouses, one line each

• Google BigQuery: serverless warehouse on Google Cloud; you load data and run SQL without managing servers.
• Snowflake: a cloud data platform that separates storage from compute and runs on AWS, Azure and Google Cloud.
• Amazon Redshift: the AWS columnar warehouse, with provisioned clusters or a serverless option.
• Azure Synapse Analytics and Microsoft Fabric: Microsoft's analytics services; Fabric is the newer all-in-one platform with its own warehouse.

Slowly changing dimensions (SCD) handle a dimension value that changes, such as a store moving region: Type 1 overwrites the old value, while Type 2 adds a new row with valid_from and valid_to dates so old sales still report under the old region.

Common mistakes

• Denormalising before measuring. Fix: find the slow query first; often an index (module 10) is enough.
• Adding a cached column without a plan to maintain it. Fix: update it in the same transaction or with a trigger, and add a nightly check that compares it with the source.
• Running heavy analytics on the production OLTP database at peak hours. Fix: a read replica or a warehouse.
• A fact table without a clear grain. Fix: write down what one row means ("one product line of one bill") before designing it.