In my opinion it'd be best as a flowing river, but just with a very weak push so if someone jumps in, they'll slowly drift downstream, so it's neat but doesn't influence gameplay too much.
I woul have loved to put a river in my map that would sweep people to their doom - muhahahahahahahahah:sneaky2:
but as it is, I just had to settle for a death pit
I think it all depends on the map - you have to ask - does this fit?
If yes, then, go for it.
If maybe, side on the caution and don't do it.
If no, it sticks out like a sore thumb then that's your answer.
However, think of all the maps currently out there, especially the valve maps.
None that I've seen truly have moving water in them and perhaps there's a reason valve has for that.
In my map I couldn't get the waterfall to work, even with a big amount of thanks to everyone who helped me out trying to do it. Then in trying to fix my issue, I looked at, sorry can't remember you name off the top of my head, one of our members maps that has a waterfall in and and although I saw the waterfall there I didn't see the water, I saw a blue texture with some signs of movement but this might be a video card issue.
If it's a video card issue, perhaps this is why valve didn't go this way - maybe people's video cards could or couldn't handle it. Can you imagine Valve releasing TF2 with moving water only to have it show up as a blue moving texture - they'd be shooting themslves in the foot and the game would have it's reputation killed right at the start. So maybe this why they stayed away from it.
On a further note, and perhaps this will help us all, the only place I can remember seeing moving water in a valve map was HL2-Episode 1 (?) Remember the sequence where you're driving around in the boat? Well there's a part where you get in the boat and drive it over the edge of the dam and a new map loads. This new map ( I wish I knew the segment name) when loaded gives you the user the opportunity to look back at the dam to see the water coming down and if we ripped the code apart we could maybe see how valve did it to do the same in out maps. If we did it this way, tehn we could say based on valve did it this way here rule then we could do the same on our maps and therefore it would qualify as a gimmick.
Just random thoughts from a bug.