We've been to each Disney water park once. My kids asked to go back to one this year, so we're planning another visit (it helps that we still have days on some non-expiring tickets from many years ago). For us at home, the "good" water parks are about 2.5 hours away (and there's a lower-end one about 1.5 hours away; we do have some water-park-like city pools, though). So, our kids see a water park as about as much of an "event" as the main parks.
We've treated the water park day as a "break" day in a week long (8-9 days with weekends) trip. Water parks tend to open later so we can sleep in a little longer and still be there at opening, and we just leave when we're ready and then don't plan anything for the evening, besides eating at or very near our hotel. It's worked well in the past to help us not get too worn down during a long trip. If this is your first trip to Disney, I'd really recommend some sort of break day (if not two) during your trip, or at least planning on some late mornings/early evenings. It is easy to get worn down if you go-go-go for days on end. For us, a water park day is a good break day option (others have been: DisneyQuest (what is now) Disney Springs, one of the many many tourist-oriented things on either 192 or International Drive) - mixing 2 break days in with about 6 park days has usually worked well for us.
In terms of uniqueness, I think the Typhoon Lagoon park has some pretty unique features. The wave pool is not like any I've seen before (but maybe I am unaware of another?) - they sometimes generate one large (~6 foot I think) wave rather than regular smaller ones. Some people like it, some don't, but it does seem unique. Typhoon Lagoon also has the snorkeling with sharks lagoon, which is kind of cool (and again, not something you usually see at a water park). It has a water coaster, also (which are at lots of places, now, but still not at most water parks). So, if you are wanting to justify a water park visit as something unique, Typhoon Lagoon will probably fit the bill. Our family is actually planning on going to Blizzard Beach this year, though. It has some unique theming and some interesting things like the chairlift, but the actual water slides are more in line with a traditional water park (and those are what our kids are interested in).