In our experience, no, the water parks were not worth the extra $! My kids are always perfectly fine with the pools at the hotel. Plus, if you have water parks in your area at home, you can always enjoy those when you want to.
The water parks at Disney with small kids involve packing everyone up, getting on a bus, getting there, getting changed, swimming, getting changed again and then getting back on a bus to your resort. That takes at least 1/2 a day! Whereas the pool at your resort you can swim for an hour (or less), your room is close by and then you can easily get back to whatever you want to do for the rest of the day/night.
When my kids are older (teenage years) I'm sure we'll plan a water park day as I think for that age group it's really worth it since they will enjoy riding all the water slides etc.