Star schema vs one big table: when denormalizing actually pays

A star schema optimizes for reuse. Dimensions are written once and shared by every fact table that needs them, so a change to the customer segmentation propagates to every process at the same moment. It also optimizes for correctness under change: because attributes live in one place, versioning them is a local decision rather than a rewrite of every wide table that copied them.

One big table optimizes for a single read path. Every attribute a given consumer needs sits on the row, no joins are planned, no fan-out risk exists, and the query planner has one table to reason about. On columnar engines with column pruning and good clustering, that removes the shuffle that distributed joins pay. How much it removes depends entirely on your engine, your clustering, and your query shape, so the only number worth trusting is the one you measure on your own workload. Run both models over the same data and read the query profile before letting speed decide the design.

The two are not on the same axis. A star is a source model, an OBT is a projection. Teams get into trouble when the projection becomes the only thing that exists.

A single dashboard with a fixed set of filters, owned by one team, that would be the only consumer of the star you were about to build. Building four dimension tables to feed one report is ceremony, and the report will be rewritten before the dimensions get a second user.

Machine learning feature tables, where the consumer wants one row per entity per timestamp with every feature attached, and where the training run must see exactly the feature values available at prediction time. Joining dimensions at training time is how leakage gets introduced.

BI semantic layers that flatten anyway. Some tools materialize their own wide extract from whatever you give them, so serving a wide table skips a step. Check what your tool actually does before optimizing for a join it never performs.

Event analytics at very high volume where the dimension attributes are effectively immutable, such as device type or acquisition channel captured at event time. If the attribute is a property of the event rather than a property of an entity that changes, denormalizing it costs nothing in correctness.

Dimension changes become table rebuilds. Renaming a product category in a star means updating one dimension row. In an OBT it means reprocessing every historical row that carries the copied attribute, which on a large fact history is a scheduled job with a maintenance window.

History becomes hard. Type 2 attributes are the point where OBT stops being simpler: keeping a versioned attribute on a wide fact means either freezing the value at event time, which is correct but no longer answerable as of today, or rewriting history, which loses the as it was answer. A star keeps both because the fact points at the dimension version that was valid.

Conformance disappears. The second OBT copies the same attributes with slightly different logic, and now two dashboards disagree. This is the failure that costs the most political capital, because it surfaces as the data team being wrong rather than as a modeling choice.

Permissions get coarse. Column-level access on a wide table applies to every consumer of that table. In a star, sensitive attributes can be isolated in a dimension with its own grants.

Keep the star as the source of truth and generate OBTs from it as published projections, each with a named owner and a stated consumer. The projection is disposable: if the dashboard changes, rebuild it from the star. Nothing downstream depends on the wide table except the consumer it was built for.

The rule that keeps this honest is one direction only. Nothing reads an OBT to build another model. The moment a second model sources from a wide table, the business logic has been copied and the star has stopped being the source of truth.

-- models/marts/sales/obt_sales_daily.sql
-- projection of fct_sales, owner: revenue-analytics
-- consumer: the sales overview dashboard, nothing else
select
    f.order_line_key,
    d.date_day,
    d.fiscal_quarter,
    c.customer_name,
    c.segment,          -- frozen at event time on purpose
    p.product_name,
    p.category,
    f.net_amount
from {{ ref('fct_sales') }} f
join {{ ref('dim_date') }}     d on d.date_key = f.date_key
join {{ ref('dim_customer') }} c on c.customer_key = f.customer_key
join {{ ref('dim_product') }}  p on p.product_key = f.product_key

Design arguments about joins are much shorter when both options are on screen. Describe the two models in plain language and Datadef draws them on one editable canvas, the star as a fact node surrounded by dimension nodes with labelled join keys, the OBT as a single node with the attribute list, so the trade is visible rather than asserted.

The question that settles these arguments in practice is which dimension attributes were copied into the wide table, and that is a lineage question rather than a modelling one. Recording it link by link with column-level lineage gives you the rebuild list directly: one call takes up to a hundred links, and each one names the source column, the target column, and the operation between them, so the answer to what has to be reprocessed when segment changes is a filter rather than a code read.

• Kimball dimensional modeling
• Choosing fact table grain
• Slowly changing dimensions in practice
• Column-level lineage
• dbt best practices