In this lesson, you will learn how to selectively combine related tables to minimize complex joins, allowing your analytical queries to run faster on massive datasets.
Why We Break the Rules of Normalization
In your previous studies of Entity Relationship Modeling, you learned that normalization is great for reducing data redundancy and keeping data consistent. However, when we perform large-scale analytical queries (OLAP), joining dozens of normalized tables together can significantly slow down performance.
When a query requires scanning millions of rows, the database spends excessive time and compute resources just connecting those rows across different tables. Denormalization is the strategic process of intentionally introducing redundancy by "flattening" these tables into a single, wider structure.
Practical Flattening
Imagine you have an Orders table and a Customers table. Normally, to see every order with the customer's name, you must perform a JOIN:
SELECT orders.id, customers.name
FROM orders
JOIN customers ON orders.customer_id = customers.id;
If you have billions of rows, this join operation becomes expensive. By flattening these tables, you create a new table that includes the customer_name directly inside the orders table:
| order_id | order_date | customer_id | customer_name |
|---|---|---|---|
| 101 | 2023-10-01 | 5 | Alice Smith |
Now, your analytical query no longer needs a JOIN at all:
SELECT id, customer_name
FROM flat_orders;
The Strategy
We don’t denormalize everything. Instead, we use it selectively. We flatten tables when we know specific data points (like customer_name) are frequently accessed together in analytical reports. By doing this "prep work" during our ELT transformation phase, we move the compute burden from the end-user's query time to the data pipeline stage. This results in much faster data retrieval for business intelligence dashboards and reporting tools.
Key Takeaway: Denormalization selectively merges related data into wider tables to eliminate expensive joins, trading off storage redundancy for significantly faster read performance in analytical workloads.