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.
Overview
A wide table packs many columns — sometimes hundreds — into a single table, usually by flattening what would otherwise be several normalized or dimensional tables into one. Instead of joining a fact table to five dimension tables at query time, a wide table pre-joins everything once, so every attribute a report might need is already sitting in one row.
Wide tables trade storage and load-time complexity for query-time simplicity: no joins are needed, which is especially valuable for BI tools and less SQL-fluent users who just want to filter and aggregate a single table. The trade-off is redundancy (the same dimension value repeats across many fact rows) and less flexibility if the underlying grain or relationships change.
Example
Copy code
-- A wide, pre-joined table instead of fact + several dimensions
CREATE OR REPLACE TABLE sales_wide AS
SELECT
f.order_id, f.revenue, f.quantity,
c.customer_name, c.region, c.segment,
p.product_name, p.category, p.department,
d.full_date, d.quarter, d.fiscal_year
FROM fact_sales f
JOIN dim_customer c USING (customer_key)
JOIN dim_product p USING (product_key)
JOIN dim_date d USING (date_key);
SELECT region, category, SUM(revenue) AS revenue
FROM sales_wide
GROUP BY ALL;
Why columnar engines favor wide tables
Row-oriented databases pay a real cost for wide tables, since scanning any single column still means reading full rows off disk. Columnar engines don't have that problem — each column is stored (and compressed) separately, so a query touching 3 of a table's 200 columns only reads those 3. That's part of why wide, denormalized tables are common in modern analytical warehouses: the traditional storage-efficiency argument for keeping tables narrow and normalized mostly doesn't apply.
DuckDB angle
DuckDB's columnar storage is exactly what makes wide tables practical: adding more columns to a table doesn't slow down a query that ignores them, and repeated values in denormalized columns (like a repeated region string) compress well. Building a wide table with CREATE OR REPLACE TABLE ... AS SELECT from a star schema, as above, is a common way to hand analysts or BI tools a single, join-free table in DuckDB or MotherDuck.
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.
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.
table →A table is a fundamental structure in a relational database that organizes data into rows and columns, similar to a spreadsheet.
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.
Fact table →A fact table is the central table in a dimensional model that stores numeric measures (like revenue or quantity) alongside foreign keys linking to descriptive dimension tables.
Dimension table →A dimension table stores the descriptive, mostly-textual attributes — like customer name, product category, or region — that you use to filter, group, and label the measures in a fact table.
FAQS
They overlap heavily — a wide table is usually the result of denormalizing (flattening) several related tables together — but 'wide' specifically describes having many columns, while denormalization describes the process of merging data to reduce joins.
