Objective: Purpose to reduce the size of 1 massive fact table that contains over 400 million rows of data. Currently this fact tables consists of data from 6 smaller fact tables, all concatenated together - there by blowing up the size exponentially.
Idea is to reduce the size by removing redundant keys / columns
What is the best approach to create a bridge table to remove column redundancy in all these different fact tables ?
Will that have any impact on performance ?
Column details of all fact tables:
Below is a screenshot of all common fields between these tables. The left most table being the main fact table with the most no. of rows and columns. The other tables have common fields listed below.