Denormalized table
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.
Overview
A denormalized table is the end result of denormalization: a single table holding data that, in a fully normalized schema, would be spread across several related tables. Where a normalized schema stores a customer's region in one customers table and references it by key from an orders table, a denormalized table copies the region value onto every relevant order row directly, so reading it back requires no join.
Denormalized tables are the default shape for analytics-ready data — reporting tables, BI extracts, dashboard sources — because they're read far more often than they're written, and read performance and simplicity matter more than storage efficiency or update elegance in that context.
Example
Copy code
-- Source: normalized customers + orders
-- Denormalized table for reporting
CREATE OR REPLACE TABLE orders_report AS
SELECT
o.order_id,
o.order_date,
o.amount,
c.customer_name, -- duplicated from customers on every row
c.region
FROM orders o
JOIN customers c ON c.customer_id = o.customer_id;
SELECT region, SUM(amount) AS revenue
FROM orders_report
GROUP BY ALL;
If a customer's region changes after this table is built, every historical row still shows the old region unless the table is fully rebuilt or explicitly updated — that staleness risk is the main cost of denormalized tables, alongside the extra storage from repeated values.
Refresh strategy
Because a denormalized table is derived from other tables, it needs to be kept in sync — typically by fully rebuilding it on a schedule (CREATE OR REPLACE TABLE ... AS SELECT) or incrementally updating just the changed rows. This is essentially the same operational problem materialized views solve, and in engines without native materialized views, a denormalized table maintained by CTAS is often the direct substitute.
DuckDB angle
DuckDB's columnar compression keeps the storage cost of denormalized tables lower than it would be in a row-oriented database, since repeated string values in a column (like region) compress well. Rebuilding a denormalized table with CREATE OR REPLACE TABLE ... AS SELECT from source tables — in DuckDB locally or scheduled against MotherDuck — is a common, simple refresh pattern.
Related terms
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.
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.
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.
CREATE TABLE AS SELECT (CTAS) →CREATE TABLE ... AS SELECT (CTAS) creates a new table and populates it in one statement, using the result of a query to define both its schema and its data.
table →A table is a fundamental structure in a relational database that organizes data into rows and columns, similar to a spreadsheet.
UPDATE statement →The UPDATE statement modifies existing rows in a table, setting one or more column values for rows that match an optional WHERE condition.
FAQS
The main risk is staleness: because data is duplicated rather than referenced, an update to the source value doesn't automatically propagate. The table needs a defined refresh process — a full rebuild or targeted update — to stay accurate.
