Can someone explain how and why and when this would be used?
I guess it could be seen as a relic considering most registrars/cdns/hosts are a "full package" now. In the days of old that wasn't exactly the case; where you bought your domain name, where you controlled your dns, and where your systems lived could all be different entities. Nowadays there are still reasons to do so...more control and less centralization are some. Not everyone wants to be under the thumb of the Amazons/Googles/Cloudflares.
When you want to host your own nameserver but to lazy to configure it on your colocated server. Yet you have requirements that require a nameserver.
I've been paying customer for years. Branded nameservers are great, the web ui is janky and dated but seriously robust.
Support is lacking as for a while afraid.org has had an SSL cert error, very annoying.
similarly, someone please explain why a free citizen would voluntarily use a Google DNS service that builds profiles and extensive activity records on its users
I use one of their "dynamic domain" as a stable address to point to my home computer : example.mooo.com instead of using the IP. They provide an endpoint for a cron to update this IP each X time.