How do symlinks work from the point of view of software?

Imagine I have a file in my downloads folder called movie.mp4, and I have a symlink to it in my home folder.

Whenever I open the symlink, does the software (player) understand «oh this file seems like a symlink, I should go and open the original file», or it’s a filesystem level stuff and software (player) basically has no idea if a file I’m opening is a symlink or the original movie.mp4?

Can I use sync software (like Dropbox, Gdrive or whatever) to sync symlinks? Can I use sync software to sync actual files, but only have symlinks in my sync folder?

Is there a rule of thumb to predict how software behaves when dealing with symlinks?

I just don’t grok symbolic links.