Home / Learn / Data & AI / Data modelling / Lesson 6.3
All lessons

Module 6: Beyond Kimball · Lesson 6.3 · about 25 min

One big table and wide tables

A one big table (OBT) joins the star into one wide, flat table so nobody has to join. Build one from a Northbay star, measure what it costs in repeated text, see the double-counting trap, and learn when wide tables are the right call.

Key points, meanings and formulas on one page: choose "Save as PDF" in the print box.

Module 6 · about 25 minutes · read first: 2.4. You’ll be able to build a one big table from a star schema, explain what it gains and what it costs, avoid the double-counting trap, and decide when a wide table is the right design.

A one big table joins the fact and all its dimensions into one wide table, so queries need no joins, but facts that sit at a higher level get repeated.

In everyday terms: a spreadsheet where every row repeats the customer’s name, address and phone. Easy to read, but adding the “customer count” column up gives nonsense.

1. Why this matters

In cloud warehouses such as BigQuery, Snowflake and Databricks, many teams publish wide, flat tables for analysts and for tools that cannot join well (spreadsheets, some notebook users, machine learning features). You will be asked to build them, and to say when a star is better. Knowing both, and the traps in between, is what makes a modeller trusted.

One big table (OBT)
A single wide table made by pre-joining a star schema.
Wide table
A table with many columns.
Columnar storage
Data stored column by column, so unused columns cost nothing to skip.
Fan-out
A value repeated on every row of the many side of a join.
Physical meaning
Each fact row carries copies of its dimension values. Any measure that belongs to the parent (a shipping fee on an order) is repeated once per child row.

“An OBT always gives the same totals as the star.” Any measure stored at a higher grain is repeated on every line, so SUM over the OBT overstates it. Keep line-level measures in the OBT and keep header-level measures at their own grain.

2. The star, pre-joined

A one big table (OBT, also called a wide table or flat table) is a fact table with every dimension attribute already joined on. The grain does not change: one row per order line stays one row per order line. What changes is that the descriptions travel with each row.

First build a small Northbay star to start from: four dimensions and fact_sales at order-line grain, completed orders only, guests mapped to customer key -1.

-- noviz
DROP TABLE IF EXISTS fact_sales;
DROP TABLE IF EXISTS dim_date;
DROP TABLE IF EXISTS dim_product;
DROP TABLE IF EXISTS dim_store;
DROP TABLE IF EXISTS dim_customer;
CREATE TABLE dim_date AS
SELECT DISTINCT CAST(replace(order_date, '-', '') AS INTEGER) AS date_key, order_date AS full_date,
       CAST(substr(order_date, 1, 4) AS INTEGER) AS year, CAST(substr(order_date, 6, 2) AS INTEGER) AS month,
       CASE strftime('%w', order_date) WHEN '0' THEN 'Sun' WHEN '6' THEN 'Sat' ELSE 'Weekday' END AS day_type
FROM orders;
CREATE TABLE dim_product AS
SELECT p.product_id AS product_key, p.product_name, p.brand, c.category_name, c.department
FROM products p JOIN categories c ON c.category_id = p.category_id;
CREATE TABLE dim_store AS
SELECT s.store_id AS store_key, s.store_name, COALESCE(s.city, 'Online') AS city,
       COALESCE(s.province, 'Online') AS province, COALESCE(r.region_name, 'Online') AS region
FROM stores s LEFT JOIN regions r ON r.region_id = s.region_id;
CREATE TABLE dim_customer AS
SELECT -1 AS customer_key, 'Guest' AS customer_name, 'Unknown' AS city, 'Unknown' AS loyalty_tier
UNION ALL
SELECT customer_id, first_name || ' ' || last_name, trim(city), COALESCE(loyalty_tier, 'None') FROM customers;
CREATE TABLE fact_sales AS
SELECT o.order_id, CAST(replace(o.order_date, '-', '') AS INTEGER) AS date_key,
       oi.product_id AS product_key, o.store_id AS store_key, COALESCE(o.customer_id, -1) AS customer_key,
       oi.quantity, ROUND(oi.quantity * oi.unit_price * (1 - oi.discount), 2) AS sales_amount
FROM orders o JOIN order_items oi ON oi.order_id = o.order_id
WHERE o.status = 'completed';
SELECT COUNT(*) AS fact_rows, ROUND(SUM(sales_amount), 2) AS sales FROM fact_sales

7,112 fact rows worth $126,579.09. Now flatten it. Every join is done once, at build time:

-- noviz
DROP TABLE IF EXISTS obt_sales;
CREATE TABLE obt_sales AS
SELECT f.order_id, d.full_date, d.year, d.month, d.day_type,
       p.product_name, p.brand, p.category_name, p.department,
       s.store_name, s.city AS store_city, s.province, s.region,
       f.customer_key, c.customer_name, c.city AS customer_city, c.loyalty_tier,
       f.quantity, f.sales_amount
FROM fact_sales f
JOIN dim_date d     ON d.date_key = f.date_key
JOIN dim_product p  ON p.product_key = f.product_key
JOIN dim_store s    ON s.store_key = f.store_key
JOIN dim_customer c ON c.customer_key = f.customer_key;
SELECT COUNT(*) AS obt_rows FROM obt_sales

Still 7,112 rows: the joins are all many-to-one, so no row is lost or doubled. Always check that count; if it moves, a dimension had a duplicate key (fan-out).

3. What you gain: no joins

The same question, two ways. On the star:

-- try
SELECT p.department, ROUND(SUM(f.sales_amount), 2) AS sales
FROM fact_sales f
JOIN dim_product p ON p.product_key = f.product_key
JOIN dim_date d    ON d.date_key = f.date_key
WHERE d.year = 2025
GROUP BY p.department
ORDER BY sales DESC

On the OBT:

-- try
SELECT department, ROUND(SUM(sales_amount), 2) AS sales
FROM obt_sales
WHERE year = 2025
GROUP BY department
ORDER BY sales DESC

Same answer (Grocery $27,875.79 first), no joins. For people who don’t know SQL well, for spreadsheet exports and for machine learning features, that simplicity is real value. In columnar warehouses (which store each column separately and compress repeated values well), reading one wide table can also be faster than joining.

4. What it costs

Repeated text. Every product name, store name and customer name is copied onto every line. Count the characters of descriptive text each design stores:

-- try
SELECT
  (SELECT SUM(length(product_name) + length(brand) + length(category_name) + length(department)
            + length(store_name) + length(store_city) + length(province) + length(region)
            + length(customer_name) + length(customer_city) + length(loyalty_tier)
            + length(day_type) + length(full_date)) FROM obt_sales) AS obt_text_chars,
  (SELECT SUM(length(product_name) + length(brand) + length(category_name) + length(department)) FROM dim_product)
  + (SELECT SUM(length(store_name) + length(city) + length(province) + length(region)) FROM dim_store)
  + (SELECT SUM(length(customer_name) + length(city) + length(loyalty_tier)) FROM dim_customer)
  + (SELECT SUM(length(day_type) + length(full_date)) FROM dim_date) AS star_text_chars

824,084 characters in the OBT against 29,436 in the star’s dimensions, about 28 times as much. Columnar compression shrinks the gap a lot on disk, but in memory, in exports and in row-based databases you pay it.

Changes are expensive. Customer 135 moves from Silver to Gold. In the star that is one row. In the OBT:

-- noviz
UPDATE obt_sales SET loyalty_tier = 'Gold' WHERE customer_key = 135

The message says 20 rows changed, one per order line that customer ever bought. With millions of customers and a daily rebuild, that is why OBTs are usually rebuilt from the star rather than updated.

History is frozen at build time. If the OBT copies the tier when the row is built, it shows the tier “as it was”; if it is rebuilt nightly from the current dimension, it shows “as it is” (Lesson 5.4). Decide which, and write it in the table’s description.

5. The double-counting trap

Wide tables invite people to add columns from a different grain. Suppose someone adds each order’s total to every line:

-- try
WITH obt_plus AS (
  SELECT order_id, sales_amount,
         SUM(sales_amount) OVER (PARTITION BY order_id) AS order_total
  FROM obt_sales
)
SELECT ROUND(SUM(sales_amount), 2) AS right_total,
       ROUND(SUM(order_total), 2) AS wrong_total
FROM obt_plus

$126,579.09 is right. $301,183.62 is wrong: each order’s total was counted once per line, and orders average almost two lines. Rule: every measure in an OBT must be at the table’s grain. Order-level numbers belong in their own table, or are counted with COUNT(DISTINCT order_id).

Maths and principle

Principle: a star is a factorisation of the OBT. Each dimension column in the OBT is a function of a key: tier = g(customer_key). Storing g once (the dimension) and the key per row (the fact) is enough to rebuild every row, so the OBT = fact ⋈ dimensions. Nothing is lost either way; the trade is storage and update cost against join cost.

Equation: with n fact rows, m customers and an attribute of width w, the OBT stores n·w for it and the star m·w + n·k, where k is a small key. Here n = 7,112 and m = 604, so the OBT copy is about 12 times bigger for each customer column before compression.

Analogy: a printed phone book where every page repeats the whole street directory versus one directory at the back. Easier to read, much heavier to reprint when one person moves.

Try it: an OBT has 10 million rows and 50,000 products. Roughly how many times is each product name stored? (About 200, on average.)

Watch it happen, one step at a time (any live box below has a Show how it runs button that animates the real query):

The star.
orderfee
10016
orderlineitem
10011Pen
10012Ink
10013Pad
Join them once.
orderlineitemfee
10011Pen6
10012Ink6
10013Pad6
The fee is copied to every line.
Queries get simple. No joins: SELECT item, COUNT(*) FROM obt GROUP BY item.
The trap. SUM(fee) returns 18 but the order only paid 6.
The fix. Sum fee only on one row per order (for example where line = 1), or keep it in a separate table.

6. Worked example: should Northbay publish an OBT?

Request: the marketing team wants a file of every 2025 order line with customer tier and department, to load into a spreadsheet model each Monday.

  1. Size: SELECT COUNT(*) FROM obt_sales WHERE year = 2025 gives 4,032 rows. That fits a spreadsheet easily.
  2. Grain: one row per order line; only line-level measures (quantity, sales_amount). No order totals copied onto lines.
  3. History: marketing asks “what tier were they when they bought?”, so build it from an SCD2 customer dimension, joined on the surrogate key that was current at order time.
  4. Ownership: build it as a view or table on top of the star, rebuilt each night. The star stays the source of truth; the OBT is one more mart.

Verdict: yes, as a mart over the star. Not instead of it.

7. Practice

-- exercise: Using only obt_sales, show 2024 sales by region, highest first.
-- hint: WHERE year = 2024, GROUP BY region.
SELECT region, sales_amount
FROM obt_sales
-- answer:
SELECT region, ROUND(SUM(sales_amount), 2) AS sales
FROM obt_sales
WHERE year = 2024
GROUP BY region
ORDER BY sales DESC
-- exercise: Count orders (not lines) and sales by loyalty_tier from obt_sales, biggest sales first.
-- hint: An order spans several rows, so use COUNT(DISTINCT order_id).
SELECT loyalty_tier, COUNT(*) AS orders
FROM obt_sales
GROUP BY loyalty_tier
-- answer:
SELECT loyalty_tier, COUNT(DISTINCT order_id) AS orders, ROUND(SUM(sales_amount), 2) AS sales
FROM obt_sales
GROUP BY loyalty_tier
ORDER BY sales DESC
-- exercise: Check the OBT against the star: return one row with the fact table's total sales and the OBT's total sales, both rounded to 2 decimals.
-- hint: Two scalar subqueries in one SELECT.
SELECT ROUND(SUM(sales_amount), 2) AS obt_sales
FROM obt_sales
-- answer:
SELECT (SELECT ROUND(SUM(sales_amount), 2) FROM fact_sales) AS star_sales,
       (SELECT ROUND(SUM(sales_amount), 2) FROM obt_sales) AS obt_sales
  • An OBT trades storage and repetition for join-free queries.
  • Columnar warehouses make wide tables cheap to scan.
  • Header-level measures repeated on lines get double-counted.
  • Build the OBT from a tested star, not instead of one.

New words: one big table (OBT), wide table, columnar storage, factorisation.

Check yourself

1What is a one big table (OBT)?
2A customer's loyalty tier changes. What has to change in an OBT?
3Someone adds order_total (the whole order's value) to each order-line row of an OBT. What goes wrong?

Sources

Your ticks stay in this browser. Save them as a file on the course home.

Updated 2026-11-22. Northbay Retail is a made-up company; its data is generated in your browser. Queries run on your device and nothing you type is sent anywhere.