One Big Table vs star schema: when to use each approach

One Big Table (OBT) denormalizes everything into a single wide table. Popularized by tools like dbt and by the lakehouse ecosystem, it contrasts with Kimball's star schema. The right choice depends on the use case and the consumption tool.

One Big Table — characteristics

-- OBT denormalizes everything (example columns in a single table):
sale_id, sale_date, revenue, quantity,
customer_id, customer_name, customer_segment, customer_country,
product_id, product_name, product_category, product_cost,
store_id, store_name, store_region, store_country

Advantages:
  ✓ Simple queries — no JOINs
  ✓ Excellent on columnar engines (BigQuery, Databricks, Fabric Serverless)
  ✓ Easy to materialize with dbt (model + refs)
  ✓ Good DirectQuery performance (no joins at runtime)

Disadvantages:
  ✗ Data redundancy (product name repeated on every sale row)
  ✗ Poor on VertiPaq — high-cardinality dimension columns
  ✗ Updating dimensions requires re-materializing the whole OBT
  ✗ No natural history (SCD)

Star schema — characteristics

Advantages:
  ✓ VertiPaq compresses dimensions perfectly
  ✓ Native SCD Type 2 (surrogate keys)
  ✓ Incremental dimension updates without touching the facts
  ✓ Pattern universally understood by analysts

Disadvantages:
  ✗ JOINs required in direct SQL queries
  ✗ Learning curve for business users

Practical decision rule

Use OBT when:
  - Engine: BigQuery, Synapse Serverless, Databricks SQL
  - Consumption: notebooks, ad-hoc SQL, BI tools with pushdown
  - Final table already aggregated (not transactional)
  - No need for SCD history

Use star when:
  - Engine: Power BI Import / Direct Lake
  - Need for SCD Type 2
  - Semantic model reused by many reports
  - Performance critical on VertiPaq

Recommended hybrid pattern

Bronze  → Raw (Delta files)
Silver  → Normalized (Data Vault or clean staging)
Gold    → OBT for consumption by SQL tools/notebooks
          + Star for Power BI Import/Direct Lake models

Conclusion

OBT and star are not rivals — they are tools for different contexts. The Gold layer of a good Lakehouse often has both: OBTs for SQL consumption and stars for Power BI models. Choose based on the consumption tool, not on philosophical preference.