One Big Table & Denormalized Analytics

## What "one big table" means A one-big-table (OBT) model is an analytics table that carries, on every row, both the measures of a business event and the descriptive attributes that would otherwise sit in separate dimension tables. Instead of `fact_orders` joined to `dim_customer`, `dim_product`, `dim_store` and `dim_date`, the analyst is handed a single `orders_wide` whose columns include `order_amount`, `quantity`, `customer_segment`, `customer_country`, `product_category`, `product_brand`, `store_region` and `order_month` side by side. The joins were performed once, in the transform, and their results were written down. Two things remain true and are worth saying out loud in an interview. First, an OBT still has a **grain** — one row per order, or per order line, or per session — and that grain is still the first thing you declare and the first thing you test. "Wide" is not permission to mix grains in one table. Second, the numbers do not change: an OBT is arithmetically the same star, pre-joined. Anything a star could answer, the OBT can answer, provided the flattening did not silently change the grain. ## How it is built The usual construction is a single transform that reads the fact, joins each dimension on its key, projects the attributes analysts actually use, and materialises the result: ```sql CREATE TABLE orders_wide AS SELECT f.order_id, f.order_ts, f.order_amount, f.quantity, c.customer_segment, c.customer_country, p.product_category, p.product_brand, s.store_region FROM fact_orders f JOIN dim_customer c ON c.customer_key = f.customer_key JOIN dim_product p ON p.product_key = f.product_key JOIN dim_store s ON s.store_key = f.store_key; ``` Every join here must be many-to-one on the dimension key. The moment one of them fans out — a dimension with more than one row per key, or a many-to-many relationship — the row count grows and the measures on the fact side start repeating, which quietly changes the grain. ## Why teams reach for it Four reasons, roughly in order of how often they are the real reason: - **Consumer simplicity.** Analysts, BI tools and SQL-light users get one object. No join to write, no join to get wrong — no fan-out from a many-to-many dimension, no forgetting the filter that selects the current row of a versioned dimension. - **Column economics.** On a columnar engine a query is charged roughly in proportion to the columns it reads, not the columns the table has. A 300-column mart is not 300 columns of cost for a query touching four, which is why the wide-table style became viable on analytical engines and never caught on for row-oriented operational databases. - **Predictability.** One materialised table has one refresh, one grant, one set of documentation, one cache. Latency does not depend on whether the planner picked a good join strategy today. - **Tool constraints.** Some downstream tools model one table well and several tables badly; an OBT hands them the shape they want. ## What it costs - **Duplication.** `customer_segment` is stored on every one of a customer's orders. Compression on a columnar engine makes the raw byte cost far smaller than the row count suggests, so storage is rarely the honest objection. - **Restatement.** Correcting an attribute is no longer a one-row dimension update; it is a rewrite of every fact row that carries the copy. Cost scales with history, not with the change. - **Lost conformance and reuse.** The attribute's logic now lives in each OBT that copied it. Two teams' wide marts, built at different times or with slightly different definitions, will disagree — and there is no shared dimension to reconcile them against. - **Frozen history semantics.** Whatever you chose at build time — the value as of the event, or today's value — is baked in. A star lets a consumer pick by changing the join; an OBT does not. - **Schema churn and sprawl.** Adding an attribute means altering and backfilling a huge table rather than adding a column to a small dimension that every fact already points at. Wide tables also become dumping grounds: nobody deletes a column, nobody documents the 180th one. ## Where it fits The mainstream position is that OBT is a **serving artifact**, not a modelling philosophy. You still model facts and shared dimensions with declared grains and versioned history; you then generate one or a few wide, disposable marts from that core for the consumers who benefit. That keeps the definition of `customer_segment` in one upstream place while still giving the dashboard a join-free table, and it means a wide mart can be dropped and rebuilt without losing anything that is not reproducible. OBT is the wrong default when dimensions are shared across many business processes, when relationships are genuinely many-to-many, when attributes change frequently, when consumers need both as-of and current views of the same attribute, or when a full rebuild of years of history no longer fits the batch window.