When migrating your existing DW Start schema from traditional RDBMS to Google BigQuery what best practices regarding data modeling one should follow.
So I was able to understand below is good
- Deformalize is the key to get fast query response
- If Dimension table is conformed and you have multiple fact tables better to keep the that dimension table as a separate table
- If you have static dimensions tables which are not conformed, merge them with fact table to de-normalize.
- SCD Type 1 dimensions table can be merged with fact table to de-normalize
However, I am not able to understand
- What to do when you have SCD type 2 dimensions
- How to handle it if we want to merge them with fact table
- Should we keep them separate table in BigQuery
- Does BigQuery have concept of PK/FK to join tables, Is it recommended if not whats the alternative