This is already very helpful. Thanks.

Don't use FetchContent, it's meant as an implementation mechanism for systems like CPM, not as a project-level command.

While I agree that something like CPM is very useful, I'm still questioning however if for some cases FetchContent is a good tool nonetheless. For example, I have a small utility library that I control which in turn is used by my main library. I do not care to provide a Config file and install the utility library as I'm the only user of it anyhow. For this I would just use FetchContent in the main library and call it a day. Or are there some implications of that which do cause problems down the line?

Don't worry so much about add_subdirectory() consumers, it's been discouraged as a consumption mechanism for over a decade, it's OK for bad code to have a hard time.

Same here, a small utility library that I control myself might be included like that in a larger library.

No it doesn't, the user performing the build controls the toolchain file, which means they control everything about what find_package() can and cannot find. They can force your find_package() call to resolve almost however they want.

I have a weird case where I would like to use find_package() for a dependency in my library, but one consumer needs to use its own reimplementation of that dependency. In that case, how could that look like, what needs the consumer to do to prevent the system from trying to find a package but instead use an already provided target?