---
title: "Denormalized table"
description: "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."
canonical: "https://motherduck.com/glossary/denormalized-table/"
related:
  - title: "DuckLake | MotherDuck Docs"
    url: "https://motherduck.com/docs/concepts/ducklake/"
  - title: "DuckLake Architecture Deep Dive"
    url: "https://motherduck.com/blog/ducklake-architecture-deep-dive/"
  - title: "Understanding DuckLake: A Table Format with a Modern Architecture | MotherDuck"
    url: "https://motherduck.com/videos/understanding-ducklake-a-table-format-with-a-modern-architecture/"
---

# 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.

<glossary-callout guide="duckdb-cheatsheet-full" />

### Example

```sql
-- 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.