Where should I prefer and avoid cartridge bearings in a bicycle?

First, let's take a look at different types of cartridge bearings. There are angular contact bearings that require an axial preload and can handle both radial and axial loads, and deep groove radial bearings that are best at handling radial loads but can handle usually axial loads as well. Headsets use angular contact bearings, whereas hubs and bottom brackets have to use deep groove radial bearings.

The angular contact bearings are nearly always full complement (are fully filled with balls) because it is easy to snap together their races. However, deep groove radial bearings are hard to make full complement. The reason is that the parts have an overlap and thus easy filling is made by half-filling them with balls (half complement) in one side, putting the center race in, redistributing the balls evenly, and adding a retainer to prevent the balls from ever going to the same side in use. This method is known as offset filling and was invented by Robert Conrad. There are slot filled full complement bearings too but they are poor at handling axial loads and thus won't last long in a bicycle hub for example.

Bottom brackets: cartridge bearings are acceptable

Historically, bottom brackets had a square taper spindle with cup and cone bearings. The too-small axle was prone to failures but the eleven 1/4" balls on both sides were big and numerous enough. However, a drawback of cup and cone bearings as used in bottom brackets is that the bearings are "backward" unlike what they are in a hub for example, so the design increases bearing loads (details).

Then to simplify changing the entire assembly, square taper cartridge bottom brackets proliferated. They share the too-small axle of the cup and cone square taper bottom brackets, but despite the "cartridge" name at least the Shimano offerings still use cup and cone bearings (source: twelve 7/32" bearings on one side and eighteen 5/32" bearings on another side).

To solve spindle failures, Octalink was offered with bigger spindle. Unfortunately, the faulty design lacking press fit leads to left crank arm loosening and the big spindle leaves little space for bearings. Apparently Shimano uses cup and cone bearings with eighteen 1/8" balls on both sides (source).

Finally a solution was found to bottom bracket problems with Hollowtech II that moves bearings outboard so that both the bearings and the spindle can be big. It is an implementation detail that Hollowtech II uses cartridge bearings but the bearings are large (37x25x7 mm, source) so even being half complement is not a disaster, and the most of the cost of a Hollowtech II bottom bracket is the bearings so changing the entire threaded parts is not a problem.

Thus, because the only satisfactory bottom bracket is Hollowtech II, because all cup and cone bottom brackets have the bearings "backward" increasing their loads, and because Hollowtech II can use big bearings, it is not a problem to use industrial cartridge bearings instead of cup-and-cone bearings in bottom brackets, except perhaps in press fit frames where bottom bracket swap requires expensive tools (such frames are best ignored as they are a disservice to cyclists).

Headset: cartridge preferable

Early bicycle headsets used cup and cone bearings and they failed with a failure mode called "indexed steering" where the steering develops a notch in the straight position so that it takes a bit of force to turn the steering away from this position. Later, the cause was found to be vibrations the bearings can't absorb, and by adding a conical or spherical interface at both contact points of the bearing solved the issue. This naturally leads to using angular contact full complement cartridge bearings. Because the bearings are angular contact, they can be very easily made full complement so the bearings don't suffer from the typical problem of cartridge bearings that only half complement is possible.

When compared to for example cartridge bearing hubs where changing bearings requires an expensive tool, these angular contact bearings are very simple to change. In fact simpler than changing cup and cone bearings where the cups required a press fit tool to change.

Because cartridge bearings solved a genuine problem in early cup and cone headsets, and because cartridge bearings are simpler to change than cup and cone bearings, in the headset application, cartridge bearings are preferable.

Hubs: cartridge should be avoided

Traditionally, hubs used 3/16" cup and cone bearings in the front and 1/4" cup and cone bearings in the rear. However, recently, a problem in the quick release system was found by the adoption of disc brakes: the front quick release skewer self-loosens gradually on disc brake bicycles, eventually causing the front fork to jump out of the front wheel during hard braking. As this is dangerous, manufacturers switched from quick release system to thru axles on disc brake bicycles. Unfortunately, the desire to use 11-tooth sprockets in the rear hub mandates a maximum diameter for the bearings, and the thru axle made the hub axle larger, so not much space is left for the bearings and thus 5/32" balls are used in today's thru axle cup and cone hubs. Only time will tell whether they are durable.

A cartridge bearing hub has the same restrictions that a cup and cone hub has in that the bearings must fit into a confined space, but has many additional drawbacks: no bearing service is possible without an expensive bearing press, and the bearing has reduced lifetime due to needing to use the same size balls that a cup and cone bearing would use (due to needing to fit to the same confined space), but with no possibility of having a full complement of balls and needing to have half complement due to the offset filling used to manufacture the cartridge bearings. Typical cartridge bearings used in hubs are 28x15x7 mm which is arguably too small especially as a half complement bearing when compared to e.g. the 37x25x7 mm used in bottom brackets. The 28mm outside diameter cannot be made larger due to fixed freehub body dimensions and the 15mm inside diameter cannot be made smaller due to fixed axle dimensions.

(In theory, it is possible to make a hub that uses different bearing size between the hub shell and the axle than it uses between the freehub body and the axle, but that's not much help with today's ridiculously small chainrings typical of MTBs -- for example a 20 tooth chainring has 40 millimeter radius as opposed to the 170 millimeter length typical of cranks, multiplying the weight of the cyclist by a factor of 4.25x so in fact the freehub body bearings are the most important bearings in a hub due to huge chain loads.)

It is very important in a hub that bearing service is simple, because unlike in a bottom bracket, changing the entire hub is not feasible because it takes many hours to re-spoke the wheel for a new hub. In fact, changing a hub is probably the second most difficult component in a bicycle to change (with the most difficult being the frame) so it is of utmost importance that most hub bearings service can happen without special tools. Cup and cone bearings allow regreasing after water intrusion without expensive tools, and the balls and cones can be changed without expensive tools. The rarest failure, cup failure, requires a press fit tool but that's the only case where a cup and cone hub requires non-standard tools.

Thus, hubs should ideally use cup-and-cone bearings. The only reason one cannot find expensive "boutique" hubs with cup-and-cone bearings is that nearly everyone who can fool unsuspecting cyclists to buy a custom "boutique" hub at a great price can set up a CNC machining shop to manufacture hub shells and then put in industrial cartridge bearings bought from bearing manufacturers, but having capacity to manufacture cup-and-cone bearings is only possible for major players like Shimano.

I'll keep this answer short and simple.

Cartridge bearings from reputable manufacturers such as NTN, NSK and FAG will outlast their loose ball bearing counterparts assuming no maintenance is done.

90% of the issue arises when people do not use cartridge bearings from reputable manufacturers. As such, the argument that cartridge bearings are "unreliable" compared to their loose ball counterparts arises.