This would require knowledge of EVERY mod that exists for the game

Or a search bar.

You likely will find mods that appear to be the same but they'll be made from scratch as opposed to being repurposed from existing mods. So new assets, new code, etc, it would technically​ be new and original despite being functionally the same from a user standpoint. Generally with stuff like this there's strange loopholes on what can be considered new and original despite the literall meanings of the word so you are right to have some doubt here, it just depends how Bethesda choose to go with it (It's in their interest to loophole it).

Pretty sure FO4 had many QA cycles and that game was still released with its faults. In what reality does bug testing iron out ALL bugs?

FO4 wasn't a single piece of crab armor, for every bug there is think about something that wasn't bugged, that was likely fixed in QA. It's significantly easier to bug test a single piece of content then it is to bug test an open world with absolutely shit loads of stuff going on. It should be fine apart from something very specific you likely wouldn't have picked up in any feasible amount of QA cycles. To answer your question about reality, there is pretty much no reality where all bugs are fixed, there's always something hidden away somewhere, it just depends how often it crops up. The real question is how quickly those would be fixed once they are found.

how are they going to ensure compatibility when I buy and enable every single mod on the Creation Club?

This would go two ways, either it gets sorted as and when an issue is reported as testing against everything all the time means every time a new mod is added the testing for the next mod is now longer, which could quickly become ridiculous so it's better to just sort out conflicts as they get reported. Or they say fuck it and let you figure it out/ put a disclaimer up saying there might be conflicts and to use them at your own risk.

Basically there's a couple of things Bethesda can't feasibly guarantee and everything else is going to be at their discretion so it comes down to how much you personally trust them.