The biggest difference is higher dpi's have a floaty, lighter feel, since it's smoother at very, very ,very slow speeds. Some players like this, other's don't. None is "better" objectively, although it is not recommended to go lower than 400 or above 6400. It's also only really noticeable on high refresh monitors and high frame rates.
On older sensors high dpi's didn't always track as well, but that is really a thing of the past now. Anything like a 3360 variant, hero sensor or 3310 will track perfectly at, say 3200 dpi.
The only other situation where dpi is important is if using a custom mouse accel curve, with higher dpi's being objectively preferable.
A lot of the reason people still use a low dpi is because the way the windows OS handles dpi; i.e it is coupled with cursor speed. So in some games or programs, it's not possible to set the mouse sens low enough if you use higher dpi's.