My production datawarehouse on one server has a star schema structure, where all the dimension and fact tables get loaded on a monthly basis, from the staging tables on a different server.
The data gets dumped directly into the staging tables, as is, from a file on a shared drive, without any transformations.
The staging tables get truncated and loaded every month. But we cannot do the same with the tables on our data warehouse as the key values get changed in the fact table, if the fact table gets truncated every month as the foreign keys of the fact table are mapped to the primary keys in the dimension tables.
We need to either insert new records or do an upsert or the like, which would make the fact table eventually grow in size. Could you please suggest the best approach to follow, in order to load the above? what would be the best approach to truncate and reload the full table or insert or upsert or the like?
Thanks a million in advance! I greatly appreciate it! Any code/logic is greatly appreciated.
I need a quick help! It's really, really urgent!