Must a VPN always end in a dedicated device, and what is the most common use case?

No. Besides VPN routers there are pure software VPN clients installed on a computer. The most common use is secure mobile working via the internet; server-to-server VPNs are rarer.

* Site-to-site joins whole networks; a software client joins one device, the usual case for mobile work. *

A site-to-site VPN connects two networks, with a VPN router or gateway at each end. A client-to-site (remote-access) VPN connects a single device: the user's notebook runs a software client that builds a tunnel to the company's VPN gateway, and the notebook then behaves as if it were in the company network. This is how most home-office and travelling users reach internal systems.

Go deeper:

• NCSC — Virtual Private Networks (VPNs) — UK guidance on choosing and configuring VPNs for remote users.
• IPsec — Wikipedia — the protocol suite behind most Layer 3 VPNs.