Best way to load the data

1) make the source as single instance

a) Use T-SQL UNION ALL if you source from more than one table

...

I would like to give some ideas based on my experience ( though this approach may change depends on your business requirement)

1. Rename the source table ( history data table)...

Bridge table is nothing but linkage table to link to multiple DIMs( Diagnosis) into main FACT Table. I would suggest keep the COST in the FACT table itself , add...

Actually it depends on the usage.

Table Variable: It can be used as long as you capture/store less records ie in 100s or <1000 records. It also usefull if you don't...

Yes , you have to wait to complete the rollback and no other alternative in this case.

First I would like to highlight the downside of the UPDATE on table having...

Partition scheme always have one more File Group (say FG5 in this case for 4 partition values). Otherwise Partition Scheme creation would fail if extra file group is not mentioned....