is there concept like fact and dimension in bigquery

user_8287313 0
Viewed 2340

As we are planning to migrate the data from Teradata to google cloud(Bigquery). In Teradata we have key concepts like primary and foreign with help of this keys we are able to define relation between dimension and fact.

Say for example I have 3 dimension tables and one fact table as shown below.

D1 D2 D3

F1

with the help of keys or indexes in Teradata we can able to fetch the data from fact table.

When coming to Bigquery we do not have any concept like keys or indexes then how we are going to define relation between the dimension and fact

Note: If there are no primary keys or index concept how we are going to eliminate the duplicates

fact google-bigquery
2 Answers

Think of how you deal with that flat table when it comes to SCD1 and SCD3.

For both these you need to run updates on target flat table or generate a new flat table from scratch vs just updating the dimension table.

The current generation of DWH don't keep the consistency and historical data like the old DWH. The only reason current generation still can do flat tables is because they either consider immutable data or they don't follow consistency rules. This will be over in a couple of years once data out grows the infrastructure and these ways of modeling again and we will be back to dimensional models again. It already is like that if you work with PBs of data, you don't reload that data over and over it costs too much so you try to split it into incremental batches, split immutable for none immutable aka facts and dimensions.

I would do like always if you have more than a couple of TB data or if you need to load the DWH hourly. If you are below one TB of data and only needs daily updates in the DWH probably the easiest is to reload all every time.

user_72428700
Related