One Big Table (OBT) vs Star — The Modern Debate

The classic dimensional design says: facts in the centre, dimensions around. The modern provocation says: just flatten everything into One Big Table (OBT). Each row has the fact measurements plus all the dimension attributes pre-joined.

Like all modern provocations, it's right in some cases and wrong in others.

What OBT actually looks like

CREATE TABLE obt_orders AS
SELECT
    o.order_id, o.order_date, o.revenue, o.quantity,
    c.customer_id, c.customer_name, c.customer_segment, c.customer_country, c.signup_date,
    p.product_id, p.product_name, p.category, p.brand, p.cost,
    s.store_id, s.store_name, s.region, s.store_type
FROM fct_orders o
JOIN dim_customer c ON c.customer_key = o.customer_key
JOIN dim_product p ON p.product_key = o.product_key
JOIN dim_store s   ON s.store_key   = o.store_key;

One table. ~30 columns. Every dashboard query becomes:

SELECT customer_segment, SUM(revenue) FROM obt_orders WHERE order_date > ... GROUP BY 1;

No joins.

Why OBT works on modern warehouses

1. Columnar compression makes the duplicated dimension columns cheap.
2. Columnar reads only touch the columns you query — extra unused columns are free.
3. Join elimination at query time — every dashboard query is now a simple SCAN + GROUP BY.
4. BI tools love flat tables — no model setup; just point at the table.

For a Looker explore where analysts will mix-and-match 50 different filter and group-by combinations, an OBT gives consistent fast performance. No "this dashboard is fast but that one needs a different model".

Where OBT breaks down

1) Storage cost grows quickly

Yes, columnar compression is good, but if you have:

• 1B fact rows
• Each fact joins to 5 dimensions
• Each dimension has 20 attributes

…you've created a 100-column OBT with 1B rows. Compression helps but doesn't eliminate the bloat. Star schema stores the dimensions once.

For a startup with 10M fact rows, OBT is essentially free. For a scaled company with 100B+ fact rows, the difference matters.

2) Update cost on dimension changes

When a customer's segment changes:

• Star schema: UPDATE one row in dim_customer. Done.
• OBT: UPDATE every fact row where this customer appears. Could be thousands.

If your dimension attributes change frequently, OBT is painful to maintain.

3) SCD semantics are awkward

OBT doesn't have a clean place for SCD Type 2 logic. You'd need a separate OBT row for each (fact event × dimension version) combination — which is what you get if you JOIN to a Type 2 dim and materialize. But updates and reasoning about versioning get tangled.

4) Cross-fact comparison disappears

In a star, dim_customer is conformed across fct_orders, fct_returns, fct_subscriptions. Cross-fact queries (e.g., "compare order revenue and subscription revenue by customer segment") trivially work.

In OBT-per-fact, the customer attributes live separately in obt_orders, obt_returns, obt_subscriptions. Cross-fact comparison requires either (a) joining back to a separate dim_customer (defeating OBT) or (b) trusting that all three OBTs were built from the same dimensional source.

5) Model evolution is painful

Adding a new dimension attribute (e.g., customer_tier):

• Star: ALTER dim_customer ADD tier. Existing queries unchanged.
• OBT: backfill tier into every historical row of obt_orders. Or accept some rows have NULL.

When OBT is the right answer

• BI / dashboarding for non-technical users — flat tables in Looker / Tableau / Metabase. No joins for the user to manage.
• One specific dashboard with a known query pattern — pre-flatten exactly what that dashboard needs.
• Small-to-medium scale — under a few hundred million rows, the storage overhead is negligible.
• Stable schema — dimension attributes that don't change much, dimensional logic that doesn't evolve often.

When star is the right answer

• Multi-fact warehouse with shared dimensions — conformed dimensions are powerful.
• Heavy SCD requirements — versioning is much cleaner in normalized dims.
• Large scale — billions of facts, where storage and update costs matter.
• Dimensional logic that evolves — adding/removing attributes affects one table, not all.

The pragmatic middle ground

Most modern warehouses (with dbt / Looker / Metabase) end up with:

• Star schema in the warehouse — fct_* and dim_* tables, properly normalized for the analytics use case.
• OBT views or materializations on top — obt_orders is a view (or materialized table) that joins the star into a wide table for BI consumption.

This gives both: clean star for the data team, flat OBT for the BI users. The OBT is generated from the star, so it stays consistent. Best of both.

[ fct_orders + dim_customer + dim_product + dim_store ]   ← star (canonical)
                          ↓
              [ obt_orders view / mat ]                    ← OBT (consumer-facing)
                          ↓
                    [ Looker / BI ]

Takeaway

OBT vs star isn't a religious war. Star is the canonical model; OBT is a consumption layer on top. Use star to model the truth; use OBT to expose it to BI tools. Don't replace one with the other.