How do we choose the right Data Modeling approach for a modern Cloud Data Warehouse?

All answers to this question.

In the modern cloud era, the "Star Schema" is still the gold standard for BI performance because it minimizes the number of joins needed for common aggregations. However, for the "Inbound" or "Raw" layer, a Data Vault is superior because it handles schema changes without breaking downstream models. A popular strategy is to use Data Vault for your integration layer and then transform that into "One Big Table" (OBT) for specific high-traffic dashboards. OBT is incredibly fast in columnar databases like Snowflake because it avoids joins entirely, but it can be a nightmare to maintain if your dimensions change frequently.

   Answered 2025-03-16 by Linda Thompson

Do you think that the "One Big Table" approach leads to significant "Data Redundancy" and potentially higher storage costs that might offset the compute savings?

   Answered 2025-03-18 by Robert Miller

• That is a valid concern, Robert, but in modern cloud warehouses, storage is incredibly cheap—often pennies per gigabyte. The real cost "killer" is compute (the virtual warehouse running the SQL). By using OBT, you are trading a small amount of extra storage cost for a massive reduction in the CPU cycles required to join ten different tables for every single dashboard refresh. For most enterprises, the trade-off is heavily in favor of the OBT or "Flattened" view for the final consumption layer to keep the monthly cloud bill manageable and the user experience snappy.

     Commented 2025-03-20 by James Wilson

We use dbt (data build tool) to manage these transformations. It makes it very easy to pivot between Star Schema and OBT as our requirements evolve.

   Answered 2025-03-22 by Michael Brown

• I agree with Michael. Using a transformation tool allows you to be flexible. Sarah, definitely look at dbt for managing that OBT layer Linda suggested!

     Commented 2025-03-24 by Sarah Jenkins