DHCP and DNS are two separate things. DHCP is used to give out IP leases to clients, which includes information about what to use as a gateway (“which device to i have to send traffic to to access the internet”) and which DNS the network “recommends”, along with optional other things which are probably not important here
DNS is used to translate domain names to IP addresses, ie google.com to 172.253.63.100. DNS also does more, but that’s the most common use
The benefit of setting up pihole/adguard/whatever locally and telling your DCHP to recommend that as a DNS will allow you to intercept requests and block requests to advertising sites or questionable content before consulting an upstream DNS server. It can also do caching to speed up requests and use DoH or DoT (which I don’t think pihole supports as of now, adguard home does though) to encrypt the request sent upstream. So your ISP or another MitM can’t read or intercept your requests (they can still see the IP of the server you send a request to afterwards. But not the specific domain you access, multiple websites can run on a single host, which is quite common in the webhosting world) DHCP server can choose the DNS. Some routers offer offloading the DHCP to another device, but don't offer custom DNS. You can also choose to spin up your own router with OpenWRT or similar. Can give you even more options for firewall settings/logging and traffic shaping
Though for some connections, you still need the ISPs device as a modem, but just in bridge mode then.
If you enjoy strategy, building and war games then you might enjoy this amazing game which is called Terracore. https://www.terracoregame.com/