What is the difference between "at" and "positioned" in "/execute"?

The /execute command is a versatile command used to execute commands on behalf of other entities, or to test for conditionals before executing a command. It offers multiple different parameters to set who, what, when, and where to execute the command.

There are two parameters that look very similar: at and positioned as.
The Minecraft Wiki has a clear description of what each one does: at is used to position at an entity, while positioned can be used to specify coordinates.

But however, there is another way to specify an entity: using positioned as ??. This also offers us to specify an entity instead of coordinates to execute the command at.

What is the difference between these two parameters, and in which situations should I prefer each of them?

This is an interesting question. at changes the position of execution using the position, rotation, and dimension of the target. positioned as only uses the position of the target.

You can test this yourself by running this command from a command block in the overworld (make sure the chunk is loaded):

/execute if entity @e[tag=test] positioned as @p run setblock ~ ~2 ~ glass

The entity with the tag is just a switch so you can activate the command from the end/nether. The command will place a glass block at your position but in the overworld (which proves that positioned as only uses the position). Now try using at and you will see that the glass block is placed not only at your position, but also in the same dimension you are in.