I need to a build transformation to load a target table from 80+ source tables (all source tables are staged into BigQuery). From the 80+ tables, 30+ tables will be refreshed every 1 hr from their source systems and remaining will be once a day.
Now the target table should be refreshed every 1 hr as soon as data is staged and the target table will have 350+ columns and hourly source data volume will be 40M ( 90% data will be changes to existing data and 10% or less will be new data) and over all table volume will be around 3B records.
Any best practices or suggestion on designing transformation for this scenario ?
Best answer by jasondView original