WireGuard tunnel is exactly the right tool here, and the pi zero + external drive at a family member’s house is a well-trodden pattern.

One thing to think about before anything else: the offsite end is the weakest link. A pi zero 2 W copying over an ext4 USB drive is fine, but make sure the drive is encrypted (LUKS) or the backups are encrypted client-side (borgbackup --encrypt with a repo key, or restic) — a household drive sitting in someone else’s house should not be readable if it walks away. Since you already use borg, borg over SSH with repokey encryption covers that and gives you dedup + compression, which matters a lot over a home upload link.

Practical setup that works well:

• Seed the repo at home first: plug the pi/drive into your LAN (or mount the drive on your NAS temporarily), run the initial borg init + create there so the hundreds of GB never touch the internet.
• Then move the pi to the family site and run borg as a push over the WireGuard tunnel. Restrict the pi’s side to a forced-command borg serve key (command=“borg serve --restrict-to-path /srv/backup”) so a compromised key cannot get a shell.
• If the pi sits behind their router, have the pi initiate the WireGuard connection out to your side (persistent keepalive) rather than exposing anything on their network — easier than getting port forwarding from a relative.

One caveat with pi zero: it is slow at hashing/dedup. For incremental photo backups (a few GB at a time) it is fine, but expect the initial seed and any large catch-up run to take a while. If that hurts, a pi 4/5 with USB3 makes borg much happier.

Do not bother with a VPS for the bulk data — it just becomes an expensive relay. A VPS is only worth adding later as a small third copy for the most recent snapshots if you want 3-2-1 coverage without depending on one relative’s internet.