Simply put? If you manage to master the damn thing, bam! Infinite water! They can also make an awesome sponge if you need to drain the water off something, or can even be manipulated into deadly freezing traps in the right biome.
Rivers and such also provide infinite water without the annoyance.
Hardly "infinite". I remember managing to drain a river during my carp farming experiments...
And they can't absorb water any faster than they produce it. It was fun to watch my big reservoir pressure-dump into the river... one instant almost-dry, then ***THUD***, full.
It also takes a great deal of effort to get water from a river where you want it. I've spent years on aqueducts and pump stacks.
Caverns also are almost certain to have some water too.
Almost. Mine didn't.
As for draining the water... pumping it into an aquifer is usually more difficult then other ways of getting rid of it.
pumping into an aquifer is stupidly easy. Put water on top of an aquifer. Bam, gone.
Aquifers also have one key advantage: They're indoors. Water wherever you want without having to dig a long channel for giant bats to follow and get in your hair. If your location's terrifying enough, you might find your dwarves all injured and dying of thirst before you can make a proper well.
I remember making my first waterwheel stack on a failed aquifer breach. It was a lovely site with a magma pipe (40D) and sand and sedimentary rocks... I almost abandoned when the breach failed until I realized there was a constant flow, around which I built a beast pushing thousands of urist running a massive central pump stack. I could flood or drain any level on command, filled the lakes which otherwise dried every summer, mashed garbage or transported (small) objects with the water conveyor, utilized some surplus power to create a magma whirlpool... I forgot all about the magma for several game years. I've never managed to make that good a waterfall again even on purpose, let alone by accident...
No river would've had enough water to do all that
and turn your waterwheels. You get a quick burst until it drains a little locally, and then, even if your wheels aren't stopped, you're only sucking mud... Even underground cavern map-edges, 3 tiles away vertically and pressured by 5 z-levels above, don't seem to have the same 'oomph' as an aquifer when you really want lots of water.
And when you do, it pays to remember that aquifer tiles only produce water at the edges, not in open space. You'll get a lot more out by making a weird feathery edge than you'll get by channelling out vast areas.