How can I have a hostname always be resolved using my local network's DNS server when I'm connected to a VPN?

You could configure dnsmasq to

• use your local DNS as an upstream server for the domain in question
• use the VPN's DNS as the upstream for all other requests

This can be achieved by configuring a domain dependent upstream server in /etc/dnsmasq.conf, for example:

server=/hostname.domain/dns.server.local

Then change the VPN configuration to use your dnsmasq setup as its DNS server.

dnsmasq can be easily installed with Homebrew as follows:

brew install dnsmasq

Note that, in such a setup, the configuration file is located in $(brew --prefix)/etc/dnsmasq.conf. This post about using dnsmasq on macOS for local development may be provide you with some additional hints.