Useful? Well, one rare possibility is using water flow to transport goods. For any single good, there are easier ways, but if you're trying to move hundreds of items few tiles away it makes for a quicker option.
This will also transport creatures, possibly through fortifications (beware though as completely submerged ones are swimmable) for a holding room to be processed later.
If you embarked on a mountain( or glacier )then you can irrigate subterranean tile, have cavern plants grow on it (have to breach caverns), and then clear off the plant (with, for instance, dirt road) and see what soil type correspondents to your current mountain - there's a good chance it is clay or sand.
Very simple trapavoid/buildingdestroyer detection is a door with 2-7 water and pressure plate set to 0-1 water above it with the plate build on constructed floor and the floor then deconstructed. Anything that opens or destroys the door is going to make the water fall down.
If you're in a more dirty area - such as evil embark - you can use an infinite water source to clean your dwarves. Here is one possible schematic:
z0: > >^ Down stairs, pressure plate that triggers the bridge to remove dirty water as well as the placement of 2-3 tiles of fresh water onto path per path (left as exercise to the reader)
z1: X>X Up/down stairs and down stairs where water will be when bridge is raised
z2: BBB Raising bridge
The system is technically useful with no mechanisms, too - however, all the dirty will collect into water, so if anything unclothed goes through it while it has nastiness inside they will have a bad time.
Less useful per water used, you can bring the brook to your fort so your fisherdwarves don't have to walk to it to fish - just make sure any nasties can't use the path to enter it too (basically make it half of an underground moat).
For pure fun, though: You can freeze water in temperate or colder biomes, then dig it out for ice boulders to build constructions with. An entire ice castle with double-thick walls that have magma in-between is entirely possible. If you instead go for ice sculpture without digging, make sure to smooth the walls.
1-tile 1-deep water moats with underground tunnels for water approach are pretty useless as barriers, but aesthetically it can be very pleasing to draw things that represent your fortress or civilization into the ground with the blue on green - or gray, as the case may be.
Using the bit with underground crops mentioned earlier and then removing ceilings will let apple trees grow in some very unlikely places for a longer term project - additionally, if you wait for underground trees first, you can get stuff like tower-cap and birch trees growing next to each other.