In this post we are going to cover denormalization: what it is, when you should use it, and what it costs you.
Denormalization means deliberately adding redundancy to a design that was normalized. We merge tables or copy columns so that reads need fewer joins. The joins still happen, but once, when the data is loaded, instead of in every query.
We will start with a normalized TB Shop model, answer a simple question with joins, then denormalize the customer data and answer the same question with no joins at all. Then we will see the price you pay when a copied value goes stale.
Question 12 of the Real DWH Question and Answers series on TechBrothersIT.
- The interview answer
- Let's create some sample data
- Step 1: Normalized - every question pays for joins
- Step 2: Denormalize - build a flat customer dimension
- Step 3: The same question with zero joins
- Four common ways to denormalize
- Step 4: The price - a copied value goes stale
- When should you denormalize?
- Follow-up questions interviewers ask
- The full script
The interview answer
Denormalization is deliberately adding redundancy, by merging tables or copying columns, so that reads need fewer joins and run faster. We accept extra storage and harder writes. It is standard in data warehouses (star schema dimensions, wide reporting tables), and used carefully in OLTP.
Let's create some sample data
We need a normalized (3NF) model: customers point to cities, and cities point to countries. We also need orders and order items, where orders keeps a pre-computed order_total, which is already a small denormalized column.
DROP TABLE IF EXISTS order_items;
DROP TABLE IF EXISTS orders;
DROP TABLE IF EXISTS dim_customer;
DROP TABLE IF EXISTS customers;
DROP TABLE IF EXISTS cities;
DROP TABLE IF EXISTS countries;
-- normalized (3NF) OLTP tables
CREATE TABLE countries (country_id INT PRIMARY KEY, country VARCHAR(50));
CREATE TABLE cities (city_id INT PRIMARY KEY, city VARCHAR(50),
country_id INT REFERENCES countries(country_id));
CREATE TABLE customers (customer_id INT PRIMARY KEY, name VARCHAR(50),
city_id INT REFERENCES cities(city_id));
INSERT INTO countries VALUES (1, 'Canada'), (2, 'USA');
INSERT INTO cities VALUES (1, 'Toronto', 1), (2, 'Dallas', 2), (3, 'Seattle', 2), (4, 'Vancouver', 1);
INSERT INTO customers VALUES (1, 'Sara Khan', 1), (2, 'Omar Ali', 2), (3, 'Lina Park', 3), (4, 'Mei Chen', 4);
-- orders keep a pre-computed order_total (a denormalized column)
CREATE TABLE orders (order_id INT PRIMARY KEY, customer_id INT, order_total DECIMAL(10,2));
CREATE TABLE order_items (order_id INT, product_id INT, qty INT, price DECIMAL(10,2),
PRIMARY KEY (order_id, product_id));
INSERT INTO orders VALUES (1001, 1, 53.00), (1002, 2, 25.00), (1003, 3, 30.00);
INSERT INTO order_items VALUES (1001, 101, 1, 35.00), (1001, 102, 2, 9.00),
(1002, 103, 1, 25.00), (1003, 104, 1, 30.00);Let's look at the three tables of the customer hierarchy.
SELECT * FROM countries ORDER BY 1;| country_id | country |
|---|---|
| 1 | Canada |
| 2 | USA |
Two countries, each stored once.
SELECT * FROM cities ORDER BY 1;| city_id | city | country_id |
|---|---|---|
| 1 | Toronto | 1 |
| 2 | Dallas | 2 |
| 3 | Seattle | 2 |
| 4 | Vancouver | 1 |
Four cities, each pointing to its country through country_id.
SELECT * FROM customers ORDER BY 1;| customer_id | name | city_id |
|---|---|---|
| 1 | Sara Khan | 1 |
| 2 | Omar Ali | 2 |
| 3 | Lina Park | 3 |
| 4 | Mei Chen | 4 |
Four customers, each pointing to a city. There is no redundancy at all: to find a customer's country we have to go through the city.
SELECT * FROM orders ORDER BY 1;| order_id | customer_id | order_total |
|---|---|---|
| 1001 | 1 | 53.00 |
| 1002 | 2 | 25.00 |
| 1003 | 3 | 30.00 |
Each order stores its total. Order 1001 says 53.00.
SELECT * FROM order_items ORDER BY 1;| order_id | product_id | qty | price |
|---|---|---|---|
| 1001 | 101 | 1 | 35.00 |
| 1001 | 102 | 2 | 9.00 |
| 1002 | 103 | 1 | 25.00 |
| 1003 | 104 | 1 | 30.00 |
Order 1001 has two items: 1 x 35.00 and 2 x 9.00, which add up to the stored 53.00.
Step 1: Normalized - every question pays for joins
Let's ask a simple question: how many customers do we have per country? The country is two hops away from the customer, so the query needs two joins.
SELECT co.country, COUNT(*) AS customers
FROM customers c
JOIN cities ci ON ci.city_id = c.city_id
JOIN countries co ON co.country_id = ci.country_id
GROUP BY co.country
ORDER BY co.country;| country | customers |
|---|---|
| Canada | 2 |
| USA | 2 |
Canada has 2 customers and USA has 2. On four customers the joins cost nothing. On millions of customers, in every dashboard that refreshes all day, those joins add up.
Step 2: Denormalize - build a flat customer dimension
Now let's denormalize. We build a flat dim_customer table and copy city and country onto every customer row. In a warehouse the pipeline does this once, at load time.
CREATE TABLE dim_customer (
customer_id INT, name VARCHAR(50),
city VARCHAR(50), country VARCHAR(50));
INSERT INTO dim_customer
SELECT c.customer_id, c.name, ci.city, co.country
FROM customers c
JOIN cities ci ON ci.city_id = c.city_id
JOIN countries co ON co.country_id = ci.country_id;Now let's check the result.
SELECT * FROM dim_customer ORDER BY customer_id;| customer_id | name | city | country |
|---|---|---|---|
| 1 | Sara Khan | Toronto | Canada |
| 2 | Omar Ali | Dallas | USA |
| 3 | Lina Park | Seattle | USA |
| 4 | Mei Chen | Vancouver | Canada |
Look at the country column: Canada and USA now each appear on two rows. That is redundancy, and this time it is on purpose.
Step 3: The same question with zero joins
Let's ask the same question again, this time from the flat table.
SELECT country, COUNT(*) AS customers
FROM dim_customer
GROUP BY country
ORDER BY country;| country | customers |
|---|---|
| Canada | 2 |
| USA | 2 |
Same answer as Step 1, Canada 2 and USA 2, but now it is a simple GROUP BY with no joins. Every dashboard that reads this table gets the speed-up for free.
Four common ways to denormalize
In practice, denormalization usually shows up in one of these forms:
- Flatten a hierarchy - city, state and country on the customer dimension. This is exactly what a star schema does.
- Copy a column - for example the customer name stored on the order, so the order screen needs no join.
- Pre-compute a value - like
order_totalon the order, so nobody has to sum the items again. - Wide reporting table - one big table with everything a dashboard needs. It gets its own post later in the series (question 43).
Step 4: The price - a copied value goes stale
Every copy has to be kept in sync. Let's change the quantity of one item on order 1001 and not touch the stored order_total. Then we compare the stored total with the real sum of the items.
UPDATE order_items
SET qty = 3
WHERE order_id = 1001 AND product_id = 102;Now let's check the result.
SELECT o.order_id, o.order_total,
SUM(oi.qty * oi.price) AS real_total
FROM orders o
JOIN order_items oi ON oi.order_id = o.order_id
GROUP BY o.order_id, o.order_total
HAVING o.order_total <> SUM(oi.qty * oi.price);| order_id | order_total | real_total |
|---|---|---|
| 1001 | 53.00 | 62.00 |
Order 1001 still says 53.00, but its items now add up to 62.00. The data contradicts itself. This is the update anomaly that normalization was meant to prevent.
That is why denormalization works best where a pipeline rebuilds the copies, like a data warehouse. In an OLTP system it needs triggers or careful application code to keep every copy in sync.
When should you denormalize?
- Yes - read-heavy analytics and dashboards, when joins are the measured bottleneck, and when the data is loaded by a pipeline or changes rarely.
- Be careful - when many users and applications write the same data, because you must keep every copy in sync.
A good line for the interview: "normalize until it hurts, denormalize until it works." And add that denormalization is a deliberate step from a normalized design, not skipping design altogether.
Follow-up questions interviewers ask
Denormalization vs no normalization?
Denormalization is a deliberate, measured step from a normalized design, not skipping design.
How do you keep denormalized copies in sync in OLTP?
Triggers, application code in the same transaction, or materialized views refreshed on a schedule.
Is a star schema denormalized?
Yes, the dimensions are: hierarchies are flattened into one table per dimension.
The full script
Here is everything from this post in one script. Copy it and run it top to bottom.
-- ========================================================================
-- Data Warehouse Interview Q&A #12 - What is denormalization?
-- TechBrothersIT. Sample data is invented for teaching.
-- Standard SQL, tested on PostgreSQL 16. Runs top to bottom in one go.
-- Run it in an empty database or schema: other videos reuse table names
-- like dim_customer, and their leftover tables can block the DROPs below.
-- PostgreSQL example: CREATE SCHEMA dwh_q12; SET search_path TO dwh_q12;
-- ========================================================================
-- Step 0: create and load the sample data
DROP TABLE IF EXISTS order_items;
DROP TABLE IF EXISTS orders;
DROP TABLE IF EXISTS dim_customer;
DROP TABLE IF EXISTS customers;
DROP TABLE IF EXISTS cities;
DROP TABLE IF EXISTS countries;
-- normalized (3NF) OLTP tables
CREATE TABLE countries (country_id INT PRIMARY KEY, country VARCHAR(50));
CREATE TABLE cities (city_id INT PRIMARY KEY, city VARCHAR(50),
country_id INT REFERENCES countries(country_id));
CREATE TABLE customers (customer_id INT PRIMARY KEY, name VARCHAR(50),
city_id INT REFERENCES cities(city_id));
INSERT INTO countries VALUES (1, 'Canada'), (2, 'USA');
INSERT INTO cities VALUES (1, 'Toronto', 1), (2, 'Dallas', 2), (3, 'Seattle', 2), (4, 'Vancouver', 1);
INSERT INTO customers VALUES (1, 'Sara Khan', 1), (2, 'Omar Ali', 2), (3, 'Lina Park', 3), (4, 'Mei Chen', 4);
-- orders keep a pre-computed order_total (a denormalized column)
CREATE TABLE orders (order_id INT PRIMARY KEY, customer_id INT, order_total DECIMAL(10,2));
CREATE TABLE order_items (order_id INT, product_id INT, qty INT, price DECIMAL(10,2),
PRIMARY KEY (order_id, product_id));
INSERT INTO orders VALUES (1001, 1, 53.00), (1002, 2, 25.00), (1003, 3, 30.00);
INSERT INTO order_items VALUES (1001, 101, 1, 35.00), (1001, 102, 2, 9.00),
(1002, 103, 1, 25.00), (1003, 104, 1, 30.00);
-- Step 1: Normalized: customers per country needs two joins
SELECT co.country, COUNT(*) AS customers
FROM customers c
JOIN cities ci ON ci.city_id = c.city_id
JOIN countries co ON co.country_id = ci.country_id
GROUP BY co.country
ORDER BY co.country;
-- Step 2: Denormalize: build a flat customer dimension once (the pipeline does this)
CREATE TABLE dim_customer (
customer_id INT, name VARCHAR(50),
city VARCHAR(50), country VARCHAR(50));
INSERT INTO dim_customer
SELECT c.customer_id, c.name, ci.city, co.country
FROM customers c
JOIN cities ci ON ci.city_id = c.city_id
JOIN countries co ON co.country_id = ci.country_id;
-- check the result
SELECT * FROM dim_customer ORDER BY customer_id;
-- Step 3: Denormalized: the same question with zero joins
SELECT country, COUNT(*) AS customers
FROM dim_customer
GROUP BY country
ORDER BY country;
-- Step 4: The cost: a pre-computed total goes stale
UPDATE order_items
SET qty = 3
WHERE order_id = 1001 AND product_id = 102;
-- check the result
SELECT o.order_id, o.order_total,
SUM(oi.qty * oi.price) AS real_total
FROM orders o
JOIN order_items oi ON oi.order_id = o.order_id
GROUP BY o.order_id, o.order_total
HAVING o.order_total <> SUM(oi.qty * oi.price);
Key points
- Deliberately adds redundancy so reads need fewer joins
- Joins happen once at load time, not in every query
- Techniques: flatten hierarchies, copy columns, pre-compute values, wide tables
- Price: storage and copies that must stay in sync
- Best where pipelines write and people read: the warehouse
Question 13 — Cardinality and ER diagrams What is cardinality, and how do you read an ER diagram?
Every query and every result on this page was run on PostgreSQL 16 using the script above. The SQL is standard and runs on most databases with small changes (noted in the script). All tables and data are invented demo data for teaching.
No comments:
Post a Comment
Note: Only a member of this blog may post a comment.