For some reason, I am only seeing this comment thread now, so sorry for the late response.
Thanks for those valuable details. But I am still a bit confused. I understand why you are saying that pi hole should be the only DNS server handling requests sent by LAN devices (including the machine hosting the DNS). That's because it is the only one which can resolve local names (well, that's actually its upstream dnsmasq running as a sibling container that does that but that's a minor detail).
But then you say there should be another DNS server to solve my problem. If I put two server entries in /etc/resolv.conf, one being pi hole and the other my ISP's DNS, the two of them will be randomly picked by DNS clients. When the ISP's is used, it will fail to translate local names. I guess there is a way to let the client try the other server after a failure but it will add some undesirable latency.
Sorry if I misunderstood your point but after reading the first comments I was quite convinced by the idea of adding a second nameserver entry in /etc/resolv.conf. Your explanations convinced me otherwise and now I have the impression that I can't really solve my initial problem in a reliable way.