I am designing a new data warehouse in Google BigQuery for a retail client. Traditionally, I’ve used a Snowflake schema to save space through normalization. However, I keep reading that modern columnar databases like BigQuery perform better with denormalized Star schemas or even wide tables. Given the way BigQuery charges for slots and processing, which modeling approach is more cost-effective and performant for complex JOIN operations?
3 answers
For BigQuery, the general rule is to lean toward denormalization. Unlike traditional RDBMS where storage was expensive, BigQuery is optimized for scanning large blocks of data in a columnar format. A Star Schema is usually the sweet spot because it balances readability with performance. While a Snowflake schema reduces redundancy, the multiple JOINs required can lead to "shuffling" data across nodes, which increases query latency and cost. If you have very nested data, consider using BigQuery’s "REPEATED" and "RECORD" fields (JSON-like structures). This allows you to keep related data in a single row, effectively eliminating the need for many JOINs while maintaining a logical relationship between entities.
Are your end-users planning to use "Self-Service BI" tools like Tableau, or will most of the queries be pre-written by data engineers?
We found that denormalizing our "Product" and "Store" dimensions into the main "Sales" fact table reduced our monthly BigQuery bill by 15% due to fewer shuffle bytes.
Jennifer is right. In modern warehouses, compute is almost always more expensive than storage. Saving pennies on storage while spending dollars on JOINs is a bad trade-off.
Christopher, it's a mix. Our analysts want to build their own dashboards in Looker. This is exactly why I'm leaning toward the Star Schema. If the tables are too normalized, the analysts get confused by the complex JOIN logic, leading to "Frankenstein" queries that eat up our budget. On the other hand, if I go fully denormalized with "One Big Table," will the storage costs for redundant strings become an issue over time? BigQuery storage is cheap, but with 50 billion rows, even small redundancies start to add up in the monthly bill.