They're different tile types that are provided as part of the vanilla game (though the vanilla game doesn't generate all of them, not sure about deep). If you just have a few deep next to a few regular water it's hard to tell the difference, but if you have a big swath of them, you can tell the deep water is a bit of a deeper blue. They also look different on the map view.
Since these are vanilla tiles, various mods will do things with them already. For example a mod might cause biters to be able to swim across medium water, but not deep, or fish to spawn differently. A watercraft mod could make it more or less efficient to travel on. Alien Biomes will cause the game to spawn shallow and deep waters automatically.
Safe Waterfill is very configurable. If you don't see a need for deep water in your game, you can choose the "no deep" setting to get only shallow and regular.