Copper fails due to freezing, corrosion, or movement fatigue. To some extent you can control all these by using the right solder, cleaning off flux, insulating, strapping pipes properly at stub-outs, accommodating thermal expansion, damping water hammer, etc.

Most houses in the US are expected to be replaced after 70-100 years of use. And all in-wall utilities have a finite lifespan. So the critical question is how often they need to be replaced, and how much damage occurs during a failure. In my experience/opinion soldered copper is proven good for 50-100 years typical between repipes, and PEX is probably good for 30-50 years between repipes. Accelerated-aging studies using high temps show it should hold up better than older stuff like orangeburg or CPVC that have clear reliability problems. Unfortunately, modern connections like propress and sharkbite don’t truthfully have enough field service history to know if they’re 20 year fittings or 50 year fittings or 100 year fittings.

Too much flux can cause a soldered joint to eventually fail too.

Sweated drain plumbing maybe. They're harder to get right, and harder to test.

I work in flood restoration and I see it pretty often, but it's usually on older pipes from the 1960s or older. The most interesting ones are when the water or minerals in the water have worn through the corner of the pipe.

The ones that I've seen pop off or separate are due to poor installation.

But also you have to think about how often the above occurs compared with how many joints exist. Something can be both incredibly rare and happen consistently. As in a failure rate of .05% is great , but .05% of 1,000,000 is still 500.

The big thing with sweat fittings are the pinhole leaks. They can occur from excessive flux, or from the fact that sweat fittings are just copper pipe stretched into a 90 or 45 or whatever. That means the material is not only weaker but exposed to more wear and tear from the water, as it has to more actively direct it. In cases with high water velocity, that means that the backside of 90s can be eaten away much quicker than the rest of the pipe. It's most common on the fitting immediately after pump.

[deleted]

Usually corrosion from dissimilar metals. I’ve seen a few failed joints in my tine. One, a few weeks ago.