In short, at:// protocol lets you build apps that can deeply interop and query/embed each other's information, allow the user to have a shared user identity between apps, and allow the user to self-host their content (or move their hosting) without impacting the apps. It does this by giving bits of JSON permanent URIs that aren't tied to a specific user handle or hosting server, but instead are rooted in identity. The article describes how that works (and what an "identity" is) in technical detail.
Here's a concrete example: a blogging app (https://leaflet.pub) automatically "sees" mentions of a Leaflet post on a different microblogging app (https://bsky.app) and can display them: https://bsky.app/profile/o.simardcasanova.net/post/3luujudlr.... This is not done by hitting an API; it's just that both apps aggregate the same information from the open network into their respective databases.