Connection entities are created automatically when there’s a link between two entities via a Multi-Entity field.
An example enabled on your site by default includes the AssetShotConnection, which is based on the connection between Assets and Shots. Every time an Asset is linked to a Shot, or vice-versa, then a new AssetShotConnection is made.
You can use connection entities when you want to track something that only exists on the bridge between two entities, such as when you want to track the number of times an Asset appears in a Shot, or what costume or look a Character wears in a Shot—as this can’t be tracked on the Asset level or Shot level but on the Asset when it appears in a particular Shot.
These "join fields" live on the connection, so they can have a unique value for each time an Asset is linked to a different Shot.
Another example, which exists by default, is the Sort Order field on the connection between Playlists and Versions.
To create fields like this, head over to the detail page for one of the entities and add a tab displaying the other entity. For example, go to the 'Assets' Tab on a Shot Detail Page. Next click on Fields and scroll down to a section called "Shot-specific fields." Here you can add a new connection field via "Add New Asset-in-Shot Field…"