...
More about facts and dimensions: https://www.geeksforgeeks.org/difference-between-fact-table-and-dimension-table/
Google Big Query has a column-based table model. When querying data, only the columns specified in the "Select" are physically read. We have built our DWH models as "wide" as possible and have already joined a lot of master data where possible. A JOIN combines rows in two tables that have matching IDs into a single row. For this reason, you should never use "Select *", because a row can have a lot of columns, which can result in enormous query volumes. Think about what data you really need for an analysis.
The jointed master data is stored next to the original Id in a struct. We always try to use the same terminology. Here is a short summary of used acronyms.
Table name | Id name | Acronym |
|---|---|---|
platform_network | networkId | net |
partnerships_advertiser | advertiserId | adv |
partnerships_partner | partnerId | prt |
partnerships_adspace | adspaceId | asp |
trail_channel | channelId or trackingChannelId | ch |
trail_conversiontarget | conversionTargetId | tc |
When creating analyses, you should always provide all relevant columns in a model for graphical reporting tools. This saves you complicated joins in the reporting tools.
We may not have added master data to some models. This can be due to various reasons and you are welcome to bring this to our attention and we will look into the matter. You can always join the master data yourself and here we explain how this works.
...
2. Join a dimension to the fact table
This example shows how dimension tables are joined to fact tables. In our data warehouse we have already joined most of the existing dimensions. However, we believe that you should stay with this documentation as it is a good template for learning about data structure and methods of working with nested records.
The table "platform_network" under "net" and the table "platform_advertiser" under "adv" have already been joined to the table "finance_conversion". We assume in the following that this has not yet happened.
...