I agree with Galahad. It comes down to the talent and imagination of the programmer.
If you asked an absolute muppet like Dr John Prince/Tiertex, he'd probably say the Amiga sprites are useless. (Assuming he even knew they existed since I don't think any of his games used them).
Yet ask a decent programmer like Ron Pieket
who wrote SWIV and said "We got a lot of mileage out of those 8 sprite channels". All player bullets, most enemy bullets and some explosions were all done by reusing those 8 sprites.
As for your Twin Cobra example, if you really wanted water animation you could easily reserve colours 29-31 as 3 shades of blue, multiplex sprites 6 and 7 over the entire screen and have a much better animation than basic colour cycling could ever achieve. It all comes down to creativity!