Denormalization
Denormalization is the deliberate process of combining or duplicating normalized data — for example, flattening related tables together — to reduce joins and speed up read-heavy analytical queries.
Overview
Denormalization reverses some of what normalization enforces: instead of splitting data into many single-purpose tables joined by keys, you merge related data back together, accepting redundancy in exchange for fewer joins and simpler, faster reads. It's the standard trade-off analytical warehouses make, because BI dashboards and reports are read-heavy and tolerant of some data duplication, unlike transactional systems that need to avoid update anomalies.
A typical example: a normalized OLTP schema might store customers, addresses, and orders as three separate tables. A denormalized reporting table joins them once, at load time, into a single wide orders_report table that repeats the customer's name and address on every order row. Every downstream query then runs against one table instead of three.
Example
Copy code
-- Denormalize at load time instead of joining at query time
CREATE OR REPLACE TABLE orders_denormalized AS
SELECT
o.order_id,
o.order_date,
o.amount,
c.customer_name,
c.region,
a.city,
a.country
FROM orders o
JOIN customers c ON c.customer_id = o.customer_id
JOIN addresses a ON a.customer_id = c.customer_id;
SELECT region, SUM(amount) AS revenue
FROM orders_denormalized
GROUP BY ALL;
Trade-offs
Denormalization trades storage and write complexity for read speed and simplicity: updating a customer's name now means updating it everywhere it's duplicated, and the denormalized table takes more disk space. For a data warehouse where writes happen in controlled batch loads and reads happen constantly and ad hoc, that trade is almost always worth it. Star schemas are essentially a structured, half-way form of denormalization — dimensions are flattened, but fact and dimension tables stay separate.
DuckDB angle
DuckDB's columnar compression means a denormalized wide table costs less storage than it would in a row-oriented database, since repeated string values (like a region name) compress extremely well column by column. Materializing a denormalized table with CREATE OR REPLACE TABLE ... AS SELECT is a common pattern for pre-computing a fast, join-free table for dashboards.
Related terms
A denormalized table is a table that intentionally stores redundant, pre-joined data from multiple normalized sources, trading storage and update simplicity for faster, simpler reads.
Wide table →A wide table is a table with a large number of columns, typically produced by denormalizing and flattening related data together, common in analytics and columnar data warehouses.
Data normalization →Data normalization is the process of structuring relational tables to reduce data redundancy and prevent update anomalies, typically by applying a sequence of rules called normal forms.
OLAP vs OLTP →OLAP (Online Analytical Processing) and OLTP (Online Transaction Processing) are two workload categories: OLTP handles many small, concurrent transactional writes on normalized schemas, while OLAP handles large read-heavy analytical queries on denormalized, columnar data.
Star schema →A star schema is a dimensional data warehouse design where a central fact table of numeric measures connects directly to denormalized dimension tables, forming a shape like a star.
Snowflake schema →A snowflake schema is a dimensional model where dimension tables are normalized into multiple related sub-tables instead of one flat table, reducing redundancy at the cost of extra joins.
FAQS
Denormalize when a query pattern is read-heavy and repeats the same joins constantly — pre-computing the join once at load time is cheaper than re-joining on every query. It's less appropriate for tables with frequent, concurrent writes, where redundancy creates update-consistency risk.
