Denormalization deliberately re-introduces redundancy to make reads faster or simpler — trading write complexity and consistency risk for fewer joins.

Common techniques

Technique	Example	Kept in sync by
Stored aggregate	`customers.lifetime_value`, `posts.comment_count`	Trigger, application code, batch job
Duplicated column	`orders.customer_city` copied from customers	Trigger / app
Snapshot of a value at a time	`order_items.unit_price` (price when bought)	Written once — not really redundancy
Pre-joined reporting table	One wide `sales_fact` row per order line	ETL / materialised view
Materialised view	`monthly_revenue`	`REFRESH`

SQL

SELECT oi.order_id, p.name, p.price AS list_price, oi.unit_price AS paid
FROM order_items oi JOIN products p ON p.product_id = oi.product_id
WHERE p.name = 'Laptop'
ORDER BY oi.order_id;

Output

order_id | name   | list_price | paid
---------+--------+------------+------
101      | Laptop | 55000      | 55000
106      | Laptop | 55000      | 52000

OLTP vs OLAP

	OLTP (transactions)	OLAP (analytics)
Workload	Many small reads/writes by key	Few huge scans and aggregations
Schema	Normalised (3NF)	Denormalised star / snowflake schema
Examples	PostgreSQL, MySQL, SQL Server for the app	Snowflake, BigQuery, Redshift, ClickHouse, DuckDB
Storage	Row-oriented	Column-oriented, compressed
Freshness	Real time	Loaded by ETL/ELT (minutes–hours)

Star schema

A central fact table of measurable events (sales_fact: quantity, amount) with foreign keys to dimension tables describing them (dim_date, dim_customer, dim_product). Dimensions are wide and denormalised, so queries need one join per dimension.

SQL

CREATE TABLE sales_fact AS
SELECT o.order_date AS date_key, o.customer_id, oi.product_id,
       oi.quantity, oi.quantity * oi.unit_price AS amount
FROM orders o JOIN order_items oi ON oi.order_id = o.order_id
WHERE o.status <> 'cancelled';

SELECT p.category, c.city, SUM(f.amount) AS revenue
FROM sales_fact f
JOIN products  p ON p.product_id  = f.product_id
JOIN customers c ON c.customer_id = f.customer_id
GROUP BY p.category, c.city
ORDER BY revenue DESC;

Output

category    | city      | revenue
------------+-----------+--------
Electronics | Mumbai    | 60000
Electronics | Delhi     | 52000
Furniture   | Bengaluru | 12500
Furniture   | Delhi     | 9000
Furniture   | Mumbai    | 8000
Stationery  | Bengaluru | 1200
Stationery  | Mumbai    | 400
Stationery  | Delhi     | 200

When would you denormalize?

What is a star schema?