Hi My Data warehouse system upload 50 milion Dimensional records every week and load intoa STG table in database.Every week this data comes as a full dump with additional Dimension records.
2. The STG data is moved to a Dimensional Table by creating a Surrogate Key for each record.
Every week the STG Table is truncated and freshly loaded.
Every week the Dimensional Table hence gets some addtional new values .
Since the Surrogate Keys created into Dim Table previous week needs to be maintained how can we insert the new rows from STG to Dim with high Performance
For this i plan to create a Merge statement on DIM Table using STG table. If not matched then INSERT into Dimension Table.
Could you Suggest the best approach in terms of performance?