In this post we are going to cover the one big table (OBT), and answer an interview question that comes up more and more on cloud warehouses: one big table or star schema, which is better?
A one big table is a fully denormalized table. Instead of keeping a narrow fact table and separate dimensions, we do the joins once, in advance, and store every customer and product attribute right on each sale row. Queries then need no joins at all.
We will build an OBT from a small star schema with SQL, query it, and then measure what it costs when a business attribute changes.
Question 43 of the Real DWH Question and Answers series on TechBrothersIT.
The interview answer
A one big table (OBT) is a fully denormalized table: every dimension attribute is copied onto every fact row, so queries need no joins. It is fast and simple on columnar engines and great for BI extracts and ML, but it duplicates data, makes attribute changes expensive and history hard. Best practice: keep the star as the source of truth and generate OBTs from it for specific uses.
What a one big table is
A star schema keeps the fact table narrow and puts the descriptions in dimensions. Queries join them at run time. A one big table flattens the star:
- Star schema -
fact_salesholds keys and amounts;dim_customeranddim_producthold the descriptions. - One big table -
obt_salesis one wide table with the customer and product attributes on every sale, so there are no joins.
Columnar warehouses like BigQuery, Snowflake and Databricks compress repeated values very well, so the extra width costs less than you might expect.
Let's create some sample data
We create a small star schema (dim_customer, dim_product, fact_sales) and an empty obt_sales table that will hold the flattened version.
DROP TABLE IF EXISTS obt_sales;
DROP TABLE IF EXISTS fact_sales;
DROP TABLE IF EXISTS dim_customer;
DROP TABLE IF EXISTS dim_product;
-- the star schema
CREATE TABLE dim_customer (customer_key INT PRIMARY KEY, name VARCHAR(30), segment VARCHAR(20));
CREATE TABLE dim_product (product_key INT PRIMARY KEY, name VARCHAR(30), category VARCHAR(20));
CREATE TABLE fact_sales (sale_id INT PRIMARY KEY, sale_date DATE, customer_key INT, product_key INT, amount DECIMAL(10,2));
INSERT INTO dim_customer VALUES (1, 'Sara', 'Retail'), (2, 'Omar', 'Business');
INSERT INTO dim_product VALUES (1, 'Pen', 'Office'), (2, 'Notebook', 'Office'), (3, 'Headphones', 'Electronics');
INSERT INTO fact_sales VALUES
(1, '2026-06-01', 1, 1, 5.00),
(2, '2026-06-01', 2, 2, 12.00),
(3, '2026-06-02', 1, 3, 60.00),
(4, '2026-06-02', 2, 1, 5.00),
(5, '2026-06-03', 1, 2, 12.00);
-- the one big table: every attribute copied onto every sale
CREATE TABLE obt_sales (
sale_id INT,
sale_date DATE,
customer_name VARCHAR(30),
segment VARCHAR(20),
product_name VARCHAR(30),
category VARCHAR(20),
amount DECIMAL(10,2)
);Here are the five sales in the star. Each one only has keys and an amount.
SELECT * FROM fact_sales ORDER BY 1;| sale_id | sale_date | customer_key | product_key | amount |
|---|---|---|---|---|
| 1 | 2026-06-01 | 1 | 1 | 5.00 |
| 2 | 2026-06-01 | 2 | 2 | 12.00 |
| 3 | 2026-06-02 | 1 | 3 | 60.00 |
| 4 | 2026-06-02 | 2 | 1 | 5.00 |
| 5 | 2026-06-03 | 1 | 2 | 12.00 |
The products: two of the three are in the Office category.
SELECT * FROM dim_product ORDER BY 1;| product_key | name | category |
|---|---|---|
| 1 | Pen | Office |
| 2 | Notebook | Office |
| 3 | Headphones | Electronics |
Step 1: Build the one big table from the star
Now let's build the OBT. An INSERT ... SELECT joins the fact to both dimensions, once, and writes every attribute into the wide table.
INSERT INTO obt_sales
SELECT f.sale_id, f.sale_date,
c.name, c.segment,
p.name, p.category,
f.amount
FROM fact_sales f
JOIN dim_customer c ON c.customer_key = f.customer_key
JOIN dim_product p ON p.product_key = f.product_key;Now let's check the result.
SELECT * FROM obt_sales ORDER BY sale_id;| sale_id | sale_date | customer_name | segment | product_name | category | amount |
|---|---|---|---|---|---|---|
| 1 | 2026-06-01 | Sara | Retail | Pen | Office | 5.00 |
| 2 | 2026-06-01 | Omar | Business | Notebook | Office | 12.00 |
| 3 | 2026-06-02 | Sara | Retail | Headphones | Electronics | 60.00 |
| 4 | 2026-06-02 | Omar | Business | Pen | Office | 5.00 |
| 5 | 2026-06-03 | Sara | Retail | Notebook | Office | 12.00 |
All five sales now carry the customer name, segment, product name and category. Notice the repetition: Office appears on four rows, and Sara on three. In the star, Office is stored once per product. In a real table, these repeats would be millions of copies.
Step 2: Query the OBT with no joins
Now the payoff. Let's get sales by category from the one big table.
SELECT category, SUM(amount) AS sales
FROM obt_sales
GROUP BY category
ORDER BY category;| category | sales |
|---|---|
| Electronics | 60.00 |
| Office | 34.00 |
Electronics sold 60.00 and Office 34.00. This is a short query on one table, with no joins and no keys, which is easy for analysts, BI tools and data science notebooks. On a columnar engine it only reads the two columns it needs, so it is fast.
The star gives the same answer with one join to the product dimension. Modern engines handle those joins well too, so speed alone is rarely the deciding factor.
Step 3: The cost of changing an attribute
Now the business renames the Office category to Stationery. Let's count how many rows each design has to rewrite.
SELECT (SELECT COUNT(*) FROM dim_product
WHERE category = 'Office') AS star_rows,
(SELECT COUNT(*) FROM obt_sales
WHERE category = 'Office') AS obt_rows;| star_rows | obt_rows |
|---|---|
| 2 | 4 |
The star updates 2 rows, one per Office product in dim_product, and every report sees the new name immediately. The OBT has to rewrite 4 rows, one for every sale ever made in that category. Here it is only 4, but in production that can be hundreds of millions of fact rows against a handful of dimension rows.
History is harder too. Type 2 changes, late corrections and keeping several wide tables consistent all become rebuild jobs.
When to use which
Each design wins somewhere.
| One big table | Star schema | |
|---|---|---|
| Joins | none | fact + dimensions |
| Attribute change | rewrite many rows | update a few dim rows |
| History (SCD) | hard | built in |
| Best for | one dashboard, ML, extracts | the shared core model |
- Use a one big table for a specific dashboard, a BI extract or machine learning features on a columnar engine, where speed and simplicity matter most.
- Use a star schema as the core model: shared, conformed dimensions, history with SCD, and many teams asking different questions.
Keep the star as the source of truth, and generate one big tables from it for specific uses, rebuilt on every load.
Follow-up questions interviewers ask
Why do OBTs work well on columnar engines?
Queries read only the columns they need, and repeated values compress very well.
What is the main downside of an OBT?
Attribute changes and history require rewriting many fact rows, and data is duplicated across wide tables.
Can you have both?
Yes: the common pattern is a star schema as the source of truth, with OBTs generated from it.
The full script
Here is everything from this post in one script. Copy it and run it top to bottom.
-- ========================================================================
-- Data Warehouse Interview Q&A #43 - One big table vs star schema
-- TechBrothersIT. Sample data is invented for teaching.
-- Standard SQL, tested on PostgreSQL 16. Runs top to bottom in one go.
-- Run it in an empty database or schema: other videos reuse table names
-- like dim_customer, and their leftover tables can block the DROPs below.
-- PostgreSQL example: CREATE SCHEMA dwh_q43; SET search_path TO dwh_q43;
-- ========================================================================
-- Step 0: create and load the sample data
DROP TABLE IF EXISTS obt_sales;
DROP TABLE IF EXISTS fact_sales;
DROP TABLE IF EXISTS dim_customer;
DROP TABLE IF EXISTS dim_product;
-- the star schema
CREATE TABLE dim_customer (customer_key INT PRIMARY KEY, name VARCHAR(30), segment VARCHAR(20));
CREATE TABLE dim_product (product_key INT PRIMARY KEY, name VARCHAR(30), category VARCHAR(20));
CREATE TABLE fact_sales (sale_id INT PRIMARY KEY, sale_date DATE, customer_key INT, product_key INT, amount DECIMAL(10,2));
INSERT INTO dim_customer VALUES (1, 'Sara', 'Retail'), (2, 'Omar', 'Business');
INSERT INTO dim_product VALUES (1, 'Pen', 'Office'), (2, 'Notebook', 'Office'), (3, 'Headphones', 'Electronics');
INSERT INTO fact_sales VALUES
(1, '2026-06-01', 1, 1, 5.00),
(2, '2026-06-01', 2, 2, 12.00),
(3, '2026-06-02', 1, 3, 60.00),
(4, '2026-06-02', 2, 1, 5.00),
(5, '2026-06-03', 1, 2, 12.00);
-- the one big table: every attribute copied onto every sale
CREATE TABLE obt_sales (
sale_id INT,
sale_date DATE,
customer_name VARCHAR(30),
segment VARCHAR(20),
product_name VARCHAR(30),
category VARCHAR(20),
amount DECIMAL(10,2)
);
-- Step 1: Build the one big table from the star
INSERT INTO obt_sales
SELECT f.sale_id, f.sale_date,
c.name, c.segment,
p.name, p.category,
f.amount
FROM fact_sales f
JOIN dim_customer c ON c.customer_key = f.customer_key
JOIN dim_product p ON p.product_key = f.product_key;
-- check the result
SELECT * FROM obt_sales ORDER BY sale_id;
-- Step 2: Querying the OBT: no joins at all
SELECT category, SUM(amount) AS sales
FROM obt_sales
GROUP BY category
ORDER BY category;
-- Step 3: Renaming the Office category: rows to rewrite in each design
SELECT (SELECT COUNT(*) FROM dim_product
WHERE category = 'Office') AS star_rows,
(SELECT COUNT(*) FROM obt_sales
WHERE category = 'Office') AS obt_rows;
Key points
- OBT: every attribute on every fact row
- No joins, fast on columnar engines
- Duplicated data; changes rewrite many rows
- Star: shared core, easy changes and history
- Generate OBTs from the star
Question 44 — Partitioning, clustering and materialized views How do you make a data warehouse fast?
Every query and every result on this page was run on PostgreSQL 16 using the script above. The SQL is standard and runs on most databases with small changes (noted in the script). All tables and data are invented demo data for teaching.
No comments:
Post a Comment
Note: Only a member of this blog may post a comment.