I'm planning to store multiple dimension tables in BigQuery. In a normalized design I would have fact tables containing foreign keys that refer to one or many of the dimension tables.
I've read a bit about denormalization, and I'm wondering how far I should go. In general, do people mix fact and dimension data together into one table in BigQuery?
Regarding nested fields: In case a single fact refers to multiple dimension tables, would you have a nested column in each of the dimension tables, or just one fact table and then use joins?
In general, do people mix fact and dimension data together into one table in BigQuery?
Yes!
In case a single fact refers to multiple dimension tables, would you have a nested column in each of the dimension tables, or just one fact table and then use joins?
You should nest your dimention data inside fact data, not vice versa.