Hmm, CDC is not built for full loads, so don't think that is the best option here.

I can clearly see the logic behind you're setup, but also see there's a possibility for optimization and cleanliness, as you're doing a lot of comparisons just to figure out what is deleted. But I'm not sure how much you can do differently when getting full loads.

Is the bronze full load always active? As I read your answer, If a data point exists in silver and not in bronze, it is deleted. If it exists in both, it is kept. If it is in bronze but not in silver, it is inserted. So as I see it, bronze always contains the most up to date data, correct? Have you considered using overwrite - perhaps with replaceWhere clause so you only select non-history data?