Sneak works very well with Ninja and other perks that increase the sneak attack bonus multiplier.
Rank 2 applies to all non-trigger-tension traps, such as physical tripwires, bathroom scales wired to grenades, and even laser tripwires on walls.
The trap avoiding bonuses from ranks 2 and 3 also work when not sneaking.
Rank 4 seems to completely eliminate the effect of footsteps on detection odds. If it is active then muffle armor mods are unnecessary and sneaking in power armor becomes much easier.
Rank 5 bestows an effect similar to that of a Stealth Boy or Chameleon armor effect when entering sneak mode, rendering the player, any equipped weaponry, and the Pip-Boy invisible for approximately one second. Note that this can interfere with aiming using non-scoped weaponry, or seeing the Pip-Boy display properly if brought up during that period of time. Unlike Chameleon armor, the player need not remain still for this effect to work.
Dogmeat had a unique rank 5, but this was removed in patch 1.6.
All companions have the effects of 4 ranks of Sneak except Curie who is missing all ranks of Sneak as both robot and synth.
The workings of sneaking in general and the associated perk/skill underwent a steady evolution with each new Bethesda RPG. One difference is how the AI reacts to an undetected opponent. In Fallout 3, everyone knew instantly where the player character was as soon as the fight started. In Skyrim, opponents would search for the player character, but in a non-smart way and simply run to the location that they saw or heard something. In Fallout 4, opponents take evasive maneuvers, run in unpredictable ways, jump from cover to cover and try to take a detour in order to flank the player character.
Once obtained, it can be discarded to sneak for the rest of the turn. Enemies can not stop the player character from performing actions in the same space as them. Enemies with the aggressive trait will also not attack the player character.