You can't always have URL redirect for your host records (see for instance DigitalOcean's DNS service). Also, as you unintentionally pointed out, you need a server to do the redirection, which can be a waste of resources for some use cases (see below).
You'd use this service if you were using things like Heroku, which are relying on CNAME records to avoid distributing all the traffic on one IP address that could have the machine it points to shut down at any moment - that's the principle of the cloud.
Comments
You can't always have URL redirect for your host records (see for instance DigitalOcean's DNS service). Also, as you unintentionally pointed out, you need a server to do the redirection, which can be a waste of resources for some use cases (see below).
You'd use this service if you were using things like Heroku, which are relying on CNAME records to avoid distributing all the traffic on one IP address that could have the machine it points to shut down at any moment - that's the principle of the cloud.